(转)MySQL多字节字符集造成主从数据不一致问题 博客分类： mysql MySQL主从preparestatement乱码
原文地址:http://backend.blog.163.com/blog/static/20229412620133274030845/
问题产生
线上一直有个历史遗留问题，最近DBA提了出来，所以跟了下代码，作了下简单分析，问题描述如下：
在master-slave的环境下，对master上的某个表中的数据插入，会导致master-slave数据不一致的情况，通过反复试验，确定出现该情况的条件如下：
- master上设置了character_set_server=gbk
- 应用中采用了prepared statement并且设置了useCursorFetch=true
- 对于master上某个表中的int字段，采用了字符的形式进行插入
比如，master上有张表，结构如下：
create table t(id int auto_increment primay key, count int)engine=innodb;
进行的操作如下：
conn =DriverManager.getConnection("jdbc:mysql://192.168.0.1:3307/test?useCursorFetch=true&user=root");
pstmt = conn.prepareStatement("insert into test(count) values(?)");
pstmt.setString(1,"1");
pstmt.execute();
通过上面的操作，我们发现，在master上，插入后的结果如下：
mysql>select*from t;
+----+-------+
| id | count |
+----+-------+
|1|1|
+----+-------+
而在slave上，却变成了另外一个结果：
mysql>select*from t;
+----+-------+
| id | count |
+----+-------+
|1|49|
+----+-------+
从上面的信息可以看出，master-slave上的数据出现了不一致，查看master上的binlog，我们会发现如下信息：
insert into t(count) values(0x31)
这里的binlog中的值'1'被转化成了16进制0x31。
问题分析
binlog在某种情况下会被转化成16进制存储，这个可能很多人没有注意，转化成16进制有又造成了master-slave上数据不一致，这个就让人比较难以接受了，以下我们的分析就从这两方面入手：
- MySQL为什么要把Binlog转化成16进制，在那些条件下会转成16进制？
- 转化成16进制后，数据为什么会出现不一致？
首先，我们来分析第一个问题，通过上面的条件进行跟踪测试，我们很快发现，MySQL把Binlog中的字符串转化成16进制存储的条件有两个：
- 客户端使用了prepared statement
- 客户端传过来的编码是多字节符集编码
客户端在使用prepared statement的时候，在执行前，需要先解析出内部给定的参数，由于涉及到转义的问题，需要把参数内部的如\0，\n等字符进行相应的替换（\0会被替换成'\\0'），而对于多字节字符集编码的字符串(如：gbk，gb2312)，转义字符'\'可能出现在字符的第二个字节，例如字符串“?\0”，可能实际的编码是两个字符'?\'和‘0’,如果按照替换的原则替换，很有可能会破坏原有的字符串的内容，所以MySQL对于这种转义符'\'可能出现在第二个字节的字符集，都给了一个标识进行说明(escape_with_backslash_is_dangerous=1)，在处理时，遇到这种字符集，直接把字符串转成16进制进行处理，这样就可以避免转义出现的问题。
具体的代码可以查看log_event.cc文件中的append_query_string函数，关键代码如下：
if(csinfo->escape_with_backslash_is_dangerous)
ptr= str_to_hex(ptr,from->ptr(),from->length());
else
{
*ptr++='\'';
ptr+= escape_string_for_mysql(csinfo, ptr,0,from->ptr(),from->length());
*ptr++='\'';
}
整个字符串转成16进制的过程在Prepared statement处理时进行，所以在没有使用prepared statement或者客户端字符集不会出现转义符'\'出现在第二字节的情况下，不会产生该问题。 转义符'\'会出现在第二字节的字符集如下： my_charset_big5_chinese_ci my_charset_big5_bin my_charset_cp932_japanese_ci my_charset_cp932_bin my_charset_gbk_chinese_ci my_charset_gbk_bin my_charset_sjis_japanese_ci my_charset_sjis_bin
分析了第一个问题，再来看第二个问题，binlog被转成16进制后，为什么主从上的数据会出现不一致？ MySQL中，参数类型的确定在SQL语句的解析中进行，由于prepare statement采用先给出SQL后设置值得形式，所以在解析SQL时，MySQL还不知道具体的值，构造了Item_param对象，然后通过设置，把后面的参数'1'设置给Item_param的str_value成员，而对于salve，binlog传递没有prepared statement的信息，所以在slave上，还是按照一般的方式执行，slave发现value的值为0x31，所以构造一个Item_hex_string对象来保存，所以我们只需要查看下两个Item的save_in_field方法，就可以查明具体的原因，先看master上的处理： field->store(str_value.ptr(), str_value.length(),str_value.charset()); 最终的处理方式为 get_int(cs, from, len, &rnd, UINT_MAX32, INT_MIN32, INT_MAX32); 我们的字段类型为int，而实际给定的值是string，在处理时，需要进行转换，转换的关键代码如下：
for(ul=0; str < end9 &&(ch=(uchar)(*str -'0'))<10; str++)
{
ul= ul *10+ ch;
}
从上面的代码我们可以知道，master这种转化方式类似于C中的atoi函数，例如字符串‘1234'会被转换为1234，如果中间出现非数字字符，后面部分会被截断，所以，在master上的数据就是字符串'1'转换过来的值1 现在看下slave上的处理： slave上处理转换的方法主要在下面的代码： nr= (ulonglong) val_int(); 函数val_int的处理过程如下： 先检查字段类型，如果是string则按照string的方式处理，如果不是string，则按照下面的方式处理
char*end=(char*) str_value.ptr()+str_value.length(),
*ptr=end-min(str_value.length(),sizeof(longlong));
ulonglong value=0;
for(; ptr !=end; ptr++)
value=(value <<8)+(ulonglong)(uchar)*ptr;
slave上通过把value中的每个字节强制转化得到，所以如果value为‘1234’，通过强制转换过来的值将是:
(uchar)'1'<<24+(uchar)'2'<<16+(uchar)'3'<<8+(uchar)'4'
=31<<24+32<<16+33<<8+34
这样就造成了master-slave上的数据不一致
总结
该问题主要的原因在于MySQL的两种不同的Item在处理字符串转整型的方法不一致，Item_param通过类似于atoi的形式，直接把字符中的数字通过-'0'转换到整型，而Item_hex_string则通过强制内存转化所得，这两种方式都合理，但是两边没有统一，造成replication出错，MySQL从5.1版本后到目前MySQL5.5的版本中都存在该问题(MySQL5.6没有测试过，应该也存在该问题)。解决方法：
- 服务端使用utf8字符集编码(由于前面时gbk,改成utf8会出现乱码等很多问题)
- 更改应用不对int字段进行非int数据的插入
- 更改应用不使用prepare statement
- binlog的format设置成row格式
注：该问题已经上报给MariaDB，并且被确认为一个Bug，将在后续的版本中进行修复。