MySQL内核月报 2014.09-MySQL· 捉虫动态·GTID 和 DELAYED

<b>描述</b>

　　这是一个mysql 5.6才有的bug，影响包含最新版本。涉及到的概念有gtid、delayed。

<b>现象</b>

　　在5.6主备都开启gtid-mode的时候，备库同步线程停止，且last_sql_error显示“when @@session.gtid_next is set to a gtid, you must explicitly set it to a different value after a commit or rollback. please check gtid_next variable manual page for detailed explanation. current @@session.gtid_next is ... ” 　　

　　查到这个位置正在执行的日志是一个insert语句，并且主库上使用的语法是 insert delayed into。 　　

<b>gtid限制</b>

　　众所周知，在打开gtid-mode的时候，mysql不允许执行create table xx as select ... 这个语句。其原因是每个gtid编号(gno)需要唯一对应一个事务，而在row格式binlog模式下，上述语句会被写成一个create语句和一个insert事务。这样违背唯一对应约束。

<b>关于delayed</b>

　　往数据库里插入数据的标准命令是insert，而delayed的意思，则是异步插入。也就是说，mysql接受这个命令后，保存命令就直接返回给客户端，因此用户会发现在某些场景下insert delayed性能优于”insert，实际上只是更快的返回，而非更快的完成。

　　既然执行线程已经返回给用户，那么这个insert任务就是由一个后台线程执行的。这里有一个优化：执行线程每次循环获取现有的任务列表，多个一起执行。

　　这样就可能连续执行n个inser操作，生成多个insert事件。而在生成gtid时，就只对应一个gno。

　　这就违反了上一节提到的gtid限制。

　　这个binlog传到备库后，备库在执行完这个gno对应的第一个事件后，操作表是一个myisam表（delayed语法只myisam引擎支持），自动提交事务，在执行下一个事务时，发现“少了”新的gno，因此报错。

<b>分析修复</b>

　　上述bug的根本原因是delayed语法生成了违反gtid限制的binlog。实际上这个语法应该也设定为：在gtid模式下禁止。

　　若从减少应用的报错考虑，另一种修复策略是在gtid模式下，自动将insert delayed转为insert。

<b>delayed相关</b>

　　a) innodb不支持delayed语法，因为这破坏了事务的原子性和可见性。

　　b) 即使对于myisam，官方已经将delayed语法在5.6列为deprecated, 在5.7取消。

　　c) 目前能够使用delayed的语法有 insert delayed 和 replace delayed。

　　d) delayed 命令统一使用row格式binlog。