ITPub博客

首页 > 数据库 > MySQL > 转载:5.6.10部分严重bug

转载:5.6.10部分严重bug

MySQL 作者:G8bao7 时间:2014-03-07 10:16:16 0 删除 编辑

欢迎同学们继续补充

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=68525n
解释: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/*!*/;


13>

【报错信息】

mysql的slave启动时,error.log中出现Warning警告:

[Warning] Slave SQL: If a crash happensthis configuration does not guarantee that the relay log info will beconsistent, Error_code: 0

这条Warning信息对Mysql和MySQL复制功能没有任何影响。


MySQL5.6版本开始支持把master.info和relay-log.info的内容写入到mysql库的表中,

master.info--> mysql.slave_master_info

relay-log.info--> mysql. slave_relay_log_info

同时在MySQL5.6版本中,增加了 Slave crash-safe replication功能,为了 保证mysql的replication能够crash-safe,slave_master_info和slave_relay_log_info表必 须使用事务型的存储引擎(InnoDB),不要尝试去手动修改这两张表的内容。同时,Slave还要开启relay_log_recovery功能。


设置master_info_repository和relay_log_info_repository参数:

SQL> stop slave;

SQL> set global master_info_repository=table;

SQL> set global relay_log_info_repository=table;






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26250550/viewspace-1102858/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-04

  • 博文量
    486
  • 访问量
    1187265