MySQL多字节字符集造成主从数据不一致问题
问题产生
线上一直有个历史遗留问题,最近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,将在后续的版本中进行修复。