问题:[Err] 1055 – Expression
#1 of ORDER BY clause is not in GROUP BY clause and contains
nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not
functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

1. innodb_lock_wait_timeout

本节主要:

 

  mysql
能够活动监测行锁导致的死锁并拓宽相应的拍卖,不过对于表锁导致的死锁无法自动监测,所以该参数主要用于,出现就疑似意况的时候等待内定的时间后回滚。系统默许值是50秒。顾客能够依照专业活动安装。生产条件不引入应用过大的
innodb_lock_wait_timeout 参数值。

  • 多表连接查询
  • 符合条件连接查询
  • 子查询

 

-- 查看事务超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

  

减轻方案:

  图片 1
  也得以对脚下对话实行过期设置如: set
innodb_lock_wait_timeout=1000。关于爆发死锁的缘由,怎样查看分析死锁难题,
怎样优化尽量幸免死锁,请查看”mysql 开荒晋级篇 锁难题系列”。

 

select version(),
@@sql_mode;SET sql_mode=(SELECT
REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”));

2. innodb_support_xa

安不忘危工作:计划两张表,部门表(department)、职员和工人表(employee)

 

        
通过该参数设置,是还是不是帮忙遍及式事务。暗中同意值是ON也许1,表示支持分布式事务。
   布满事事务分两类:

图片 2

 

  (1)是外表xa事务(协理多实例分布式事务)。

create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('nvshen','male',18,200),
('xiaomage','female',18,204)
;

# 查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.19 sec)

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.02 sec)

mysql> select * from employee;
+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | egon     | male   |   18 |    200 |
|  2 | alex     | female |   48 |    201 |
|  3 | wupeiqi  | male   |   38 |    201 |
|  4 | yuanhao  | female |   28 |    202 |
|  5 | nvshen   | male   |   18 |    200 |
|  6 | xiaomage | female |   18 |    204 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)

 

  (2)是支撑内部xa事务(帮助binlog和redo_log之间数据一致性)。

图片 3

图片 4

即使关闭这些参数,据前辈们说大概会潜濡默化到:1是着力复制binlog与redo_log不一致,2是binlog与redo_log事务逐项分裂性。

ps:旁观两张表,发掘department表中id=203机关在employee中从不相应的职工,开采employee中id=6的职员和工人在department表中从未对应涉及。

 

--  查看是否支持分布式事务
SHOW VARIABLES LIKE 'innodb_support_xa';

 

包罗万象的解决方案是:

  图片 5

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注