2014年（1）
分类： Mysql/postgreSQL
2014-12-11 10:02:13
原文地址：ROW模式的SQL无法正常同步的问题总结 作者：飞鸿无痕
ROW模式的SQL无法正常同步的问题总结
最近处理数据库问题时遇到一起mysql从机ROW模式的SQL无法正常同步的问题，今天刚好有时间，将整个过程总结一下，方便后面的同学学习！
最近有一个业务的实例在比对数据的时候数据库、表、以及行数都是一样的，只是有多个表的checksum值不一致，主从状态也正常。初步判断可能是运维之前有做过skip的操作导致，对从机进行了重做后发现问题依旧，于是对binlog的内容进行了分析跟进，来找到不能同步的根本原因。分析binlog的过程如下：
? 选择一条checksum主从不一致的表的一条最近的记录
从上面两张图中可以很清楚的看出两条记录的内容不一致，那么为什么会不一致呢？我们来追踪binlog看看
? 通过binlog进行追踪master上这条记录的执行情况
在master上找到了对应的update的记录。下面到从机上找一下对应的relay日志和binlog看看是否有正常复制和执行。
? 在slave的relay日志和binlog中都查找对应master上的那条binlog的执行情况
在slave上发现一个奇怪的现象，在relay日志中能找到对应的更新这条语句的SQL，也就是说在master上的binlog已经通过从机的IO线程将对应的update语句同步到了relay日志中。但是在slave的binlog中没有找到运行这条SQL的记录，并且从机上的位置早就已经超过了那个update的位置，排除了slave端延迟的问题。
跟踪多条ROW模式的SQL均是此问题，而STATEMENT的SQL不会出现异常的问题，从机上的所有的binlog显示都是STATEMENT的SQL。主从binlog的比较也能看出确实有异常（备注：开启了log-slave-updates参数），如下截图所示：
Master上的binlog的数量和大小：
Slave上的binlog的数量和大小：
汇总问题如下：
Slave无法同步Master上的ROW模式的SQL
出现这个奇怪的灵异问题，首先想到的是mysql某个版本的bug，找各个版本的服务器进行重现该问题，发现相同的SQL在其他服务器都没有此问题。但是当采用其他的各个版本或者相同版本的mysql作为那台有问题的master的从机，就都出现了不能同步ROW模式的SQL的问题。因此基本可以排除版本的问题。
那么是什么原因在从机上ROW模式的SQL没有执行呢？什么场景会触发这种问题呢？
经过开发同学zhiyangli和edgeyang的源码定位终于找到了问题原因，问题原因是sql线程将relay log保存的64位table id转换成32位溢出，导致在hash结构中找不到对应的表而不进行任何操作，具体的逻辑为：binlog中table_id是一个ulong类型（无符号长整形），在slave进行重做binlog events之前，会先将这个ulong的table_id（为了避免混淆，用m_table_id表示）传给一个它内部维护的一个数据结构RPL_TABLE_LIST，这个里面有一个变量table_id用来存储binlog中的m_table_id，问题出现了：数据结构的变量table_id是一个uint（无符号整形），如果m_table_id超过uint的范围会发生截断。而MySQL内部在构造hash，从hash表中取值是这样的做法：set_table(table_id),get_table(m_table_id)，在两个阶段用到的key因为发生了数据截断所以必然也就不能取到预期的值。也就是说之前用uint型的table_id构建出来的key-value的hash对，用ulong型的m_table_id是无法查询到的。也就是如果binlog中显示的tableid超过2的32次方就是42亿的时候就会触发这个bug。通过重启能临时解决问题。如下截图就是有问题的master产生的binlog：
从这个图中我们可以看出table id已经远远超过了42亿了，达到了109亿。
既然是由于tableid导致，那么tableid是什么东西，为什么要有tableid这个东西呢，以及为什么STATEMENT模式的就不需要tableid呢？另外为什么tableid为上涨得那么厉害超过42亿？带着这些问题，下面就来慢慢分析和解答：
在引入table id之前我们先来说一下mysql的binlog格式
（一）Mysql的binlog格式
搞mysql的同学都知道，mysql的binlog分为三种格式，一种是STATEMENT格式，一种是ROW格式，最后一种是结合STATEMENT和ROW的MIXED格式。下面比对一下各个格式的优缺点：
1. STATEMENT
a) 优点
只记录执行的SQL语句本身，binlog量少，节省IO，性能比较好
b) 缺点
对一些却确定的函数比如uuid()、limit、user()等不能保证主从数据的一致性。
2. ROW
a) 优点
Row格式非常清楚地记录下每一行数据的修改细节，能保证主从数据的一致性。
b) 缺点
Binlog太多，IO性能受限制，另外对从机的主从延迟也是一个挑战。
3. MIXED
结合了STATEMENT和ROW模式的有点。
（二）Table id是个啥东西
先来看两个binlog中的SQL语句
STATEMENT格式的binlog：
从mysql的binlog中发现statement的SQL是没有table id的，从STATEMENT中记录的SQL，我们可以看出，通过SQL就知道更改表的对应位置，因此不需要通过table id去查找到对应的表的结构信息。
ROW格式的binlog：
从截图中可以看出ROW模式中含有table id的概念，ROW模式引入table id是为了在执行insert/update/delete解析的时候能够知道具体的表信息，因为我们通过binlog可以看到，语句并不能反应出列名信息。因此通过table id来关联表结构信息。从table_map_id代码中也能看table id就是专门用于ROW格式的：
ulong table_map_id; /* for row-based replication */
（三）Tablemap和table id
ROW模式的binlog中有如下两行信息：
Table id就是table map映射key ID，从binlog中可以看到mysql分2个events分别记录这些信息events 1，记录了操作哪些库哪些表。其中会将这些信息缓存到一个hash map内，key为tabke_id，value为table类（保存了库名，表名等信息），events 2记录了操作哪些行。每次执行events 2的时候，mysql通过table_id先去hash map查找相关的table信息。找到库表后再操作具体的行。一个table map events可以对应多个row events，以此减少binlog占用空间。
（四）Table id增长和cache的关系
从代码中可以看出table id的分配在函数assign_new_table_id()，每次分配都是对上一次的table id自增，代码如下：
一般是DDL语句会导致table id增加。
下来再看看table id和cache的关系，网上有代码分析了，table id是保存在cache中，当cache中有该表定义时，表对应的table id是不变的，而当cache中没有改表定义时，该值根据上一次操作的table id自增1获得的。Cache指的是table cache，由table_definition_cache组成，这里就会引出一个问题，当table cache过小而表的数量又很多的场景，会导致表定义将被频繁置换出cache，被置换出的表如果有操作时，重新加载时，table id的值就会发生改变。因此，table id与实际操作的数据表没有直接对应关系，而与操作的数据表是否在table cache中有关。
总结有如下两个方面会导致table id增加：
? DDL语句执行的时候。
? Table cache设置太小，表定义被频繁置换出cache，导致table id增加。
? 执行flush tables
（五）为什么table id超过42亿同步就有问题呢？
这里涉及到mysql的bug，在定义table id的时候采用的ulong型，为8byte。而在同步的SQL线程中设置的table id为uint型，为4byte，因此同步的SQL线程中如果超过2^32的话就溢出了，主机的update等就无法同步更新到从机。具体代码如下：
知道了问题原因就好解决了，主要有如下两种解决办法：
1. 修改代码修复uint的问题。
2. 重启实例，并将table cache调大。
对于一个平台来讲，虽然遇到的机会比较少，但是这种问题侧出现反应了我们平台还是有一些监控的盲点和漏洞，需要对table id进行监控，另外对table cache的默认配置400还是非常小的。因此接下来三个任务：
1．更改线上的版本，修复uint的问题。
2．对于存量的需要将table cache进行一次整体的调整。
3．推动添加table id的监控，防止类似的问题出现。
http://blog.chinaunix.net/uid-26896862-id-3329896.html
http://agapple.iteye.com/blog/1797061
