mysql 5.6.10部分严重bug

2年前 (2016-10-17) admin mysql 0评论 已收录 641℃
mysql  5.6.10部分严重bug  

1)blocked with FLUSH TABLES WITH READ LOCK + SHOW SLAVE STATUS(官方确认)
http://bugs.mysql.com/bug.php?id=68460
解释:在slave上做FLUSH TABLES WITH READ LOCK,然后在master上做一些update,然后在slave上之前的同一个session做SHOW SLAVE STATUS会hang住。
表现:
mysql> show processlist;
+----+-------------+-----------------+------+---------+------+---------------------------------------+-------------------+
| Id | User        | Host            | db   | Command | Time | State                                 | Info              |
+----+-------------+-----------------+------+---------+------+---------------------------------------+-------------------+
|  2 | root        | localhost:51048 | test | Sleep   |  322 |                                       | NULL              |
|  3 | root        | localhost:51049 | test | Sleep   |  323 |                                       | NULL              |
|  6 | root        | localhost:51055 | test | Query   |  320 | init                                  | show slave status |
|  7 | root        | localhost:51056 | test | Sleep   |  322 |                                       | NULL              |
|  8 | system user |                 | NULL | Connect |  321 | Waiting for commit lock               | NULL              |
|  9 | system user |                 | NULL | Connect |  321 | Reading event from the relay log      | NULL              |
| 10 | system user |                 | NULL | Connect |  321 | Waiting for an event from Coordinator | NULL              |
| 11 | system user |                 | NULL | Connect |  321 | Waiting for an event from Coordinator | NULL              |
| 12 | root        | localhost:51078 | NULL | Query   |    0 | init                                  | show processlist  |
+----+-------------+-----------------+------+---------+------+---------------------------------------+-------------------+

2)memory leak with innodb memcached plugin for stale connection(官方确认)
http://bugs.mysql.com/bug.php?id=68530
解释:使用memcached插件功能会导致内存泄露
表现:
# echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse
# ./mc-benchmark  -k 0 -l -c 400 -q
# top -b -d 5 | grep -w mysqld
22284 mysql     18   0 1204m 673m 7060 S  4.0  4.4   6:28.23 mysqld
22284 mysql     18   0 1204m 675m 7060 S 13.2  4.4   6:28.89 mysqld
22284 mysql     18   0 1205m 676m 7060 S 19.4  4.4   6:29.86 mysqld
22284 mysql     18   0 1205m 677m 7060 S  8.4  4.4   6:30.28 mysqld
22284 mysql     18   0 1206m 679m 7060 S 24.2  4.4   6:31.49 mysqld
22284 mysql     18   0 1206m 680m 7060 S  8.2  4.4   6:31.90 mysqld
22284 mysql     18   0 1207m 682m 7060 S 25.6  4.4   6:33.18 mysqld
22284 mysql     18   0 1207m 684m 7060 S  9.8  4.5   6:33.67 mysqld

3)Error "When GTID_NEXT is set to a GTID" ROW based replication(官方确认)
http://bugs.mysql.com/bug.php?id=68525
解释:RBR/MIXED复制下,使用GTID的注意下,GTID相关的其他bug也很多,包括5.6.11版本
表现:
Errono: 1837 "When GTID_NEXT is set to the GTID, you must Explicitly set it again after a COMMIT or ROLLBACK. 
If you see this error message in the slave SQL thread, it means That a table in the current transaction is transactional on the master 
and non-transactional on the slave. connection In the client, it means That You GTID_NEXT SET executed before the transaction and forgot to 
set GTID_NEXT to a different identifier or to 'AUTOMATIC' after COMMIT or ROLLBACK. 
GTID_NEXT Current is '76e884c2-7f8b- bd39-11e2-000c2904bdbb: 316 "

4)"select count(distinct N1), count(distinct N2) from test.AA" works incorrectly(官方确认)
http://bugs.mysql.com/bug.php?id=62504
解释:对优化器改进的副作用,用5.5版本的同学也要注意
表现:
create table test.AA (N1 int, N2 int); /*engine='MyISAM' or engine='InnoDB'*/
create unique index AA_uq on test.AA(N1,N2);-----注意这个是否存在会导致结果不一样
insert into test.AA (N1, N2) values (1, 1);
insert into test.AA (N1, N2) values (1, 2);

select count(distinct N1), count(distinct N2) from test.AA;
-- Result: 2 2
-- Expected: 1 2




5.6.11部分严重bug
1)NOT NULL columns prevent Memcached plugin from writing new records(怀疑)
http://bugs.mysql.com/bug.php?id=68974
解释:表上有not null的列会阻止memcached插件写入新数据

2)Memory leak slave_parallel_workers(怀疑)
http://bugs.mysql.com/bug.php?id=69075
解释:slave_parallel_workers设置非0在压力大的数据库上可能会有内存泄露
表现:
slave_parallel_workers = 0
PID  USER   PR  NI  VIRT   RES   SHR S %CPU %MEM    TIME+   COMMAND
8877 mysql  15  0   5432m  4.7g 7160 S 77.5 20.2  207:58.75  mysqld

changed slave_parallel_workers = 8
PID      USER      PR   NI   VIRT   RES    SHR  S   %CPU    %MEM      TIME+         COMMAND                                                                  
2548     mysql     15   0   11.7g   10g   7168  S   83.7    34.9      1104:27        mysqld 

3)replication fails with GTID enabled and master changes from SBR to RBR(官方确认)
http://bugs.mysql.com/bug.php?id=69095
解释:启用GTID,从SBR切换到RBR会导致复制中断

4)GTID_NEXT_LIST session variable is not visible(官方确认)
http://bugs.mysql.com/bug.php?id=69096
解释:GTID_NEXT_LIST会话变量没有
表现:
mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| enforce_gtid_consistency | ON        |
| gtid_executed            |           |
| gtid_mode                | ON        |
| gtid_next                | AUTOMATIC |
| gtid_owned               |           |
| gtid_purged              |           |
+--------------------------+-----------+
6 rows in set (0.00 sec)

5)mysqld scans all binary logs on crash recovery(官方确认)
http://bugs.mysql.com/bug.php?id=69097
解释:mysqld在异常退出,自动灾难恢复的时候扫描所有的binlog文件,如果binlog文件保留的很多会影响效率,理论上只要扫描异常退出时的binlog就行

6)Wrong FOUND_ROWS() on MySQL 5.6.11(官方确认)
http://bugs.mysql.com/bug.php?id=69119
解释:found_rows函数返回的结果集不正确
表现:
mysql> SELECT * FROM test_found_rows;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 4548870 | -10000 |
| 4600641 | -10000 |
| 4718998 | -10000 |
| 4737258 | -10000 |
| 4737259 | -10000 |
| 4769408 | -10000 |
| 4806434 | -10000 |
| 4891119 | -10000 |
| 4897306 | -10000 |
| 4982112 | -10000 |
| 5489466 | -10000 |
| 5531955 | -10000 |
| 5837345 | -10000 |
| 6069196 | -10000 |
| 6756422 | -10000 |
| 6839510 | -10000 |
| 7082263 | -10000 |
| 7087671 | -10000 |
| 7352152 | -10000 |
+---------+--------+
19 rows in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows
    -> ORDER BY ivalue DESC, pkey DESC
    -> LIMIT 0,10
    -> ;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)


7)mysql.slave_master_info is not updated(官方确认)
http://bugs.mysql.com/bug.php?id=69135
解释:用表mysql.slave_master_info存放master.info的相关信息时信息不更新


8)MySQL 5.6 may lose the last transaction when sync_binlog = 0(怀疑)
http://bugs.mysql.com/bug.php?id=69162
解释:设置innodb_flush_log_at_trx_commit = 1和sync_binlog = 0可能会导致commit的最后一个事务丢失
相关设置:
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1
log_bin = mysql-bin
sync_binlog = 0


9)COUNT(*)...GROUP BY sometimes returns wrong results on partitioned InnoDB tables(官方确认,也影响MariaDB 5.5.30)
http://bugs.mysql.com/bug.php?id=69168
解释:小心InnoDB分区表上的COUNT(*)...GROUP BY获得不正确的结果集


10)Drop/Alter table takes much longer time in 5.6 than 5.5(官方确认)
http://bugs.mysql.com/bug.php?id=69316
解释:部分DDL比5.5更耗时

11)MySQL uses significantly more memory for ALTER TABLE than expected(官方确认)
http://bugs.mysql.com/bug.php?id=69325
解释:在分区表上DDL(add index)消耗过多的内存

12)mysqlbinlog prints incorrect value while using decimal(官方确认)
http://bugs.mysql.com/bug.php?id=69253
解释:mysqlbinlog解析出来的decimal类型可能会出错,利用RBR和脚本做flashback恢复类的要小心
表现:
CREATE TABLE `aaa`( `d_tax` decimal(4,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
insert into aaa values(0.18);
### INSERT INTO `test`.`aaa`
### SET
###   @1=180000000 /* DECIMAL(4,2) meta=1026 nullable=1 is_null=0 */
# at 397
#130516 15:22:47 server id 9821  end_log_pos 428 CRC32 0xd02a2732       Xid = 6937888
COMMIT/*!*/;

create table t1 (a decimal(12,2));
insert into t1(a) values (2004681056);
### INSERT INTO `test`.`t1`
### SET
###   @1=000000002.004681056.000000000 /* DECIMAL(12,2) meta=3074 nullable=1 is_null=0 */
# at 400
#130516 16:52:21 server id 9821  end_log_pos 431 CRC32 0x517ecc68       Xid = 6938004
COMMIT/*!*/;




博主

让学习成为习惯,坚持-共享-开源-自由! 成功者决不放弃,放弃者绝不成功!

相关推荐

嗨、骚年、快来消灭0回复。