欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

(转)MySQL多字节字符集造成主从数据不一致问题 博客分类: mysql MySQL主从preparestatement乱码 

程序员文章站 2024-03-26 11:11:35
...

原文地址: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没有测试过,应该也存在该问题)。解决方法:
  1. 服务端使用utf8字符集编码(由于前面时gbk,改成utf8会出现乱码等很多问题)
  2. 更改应用不对int字段进行非int数据的插入
  3. 更改应用不使用prepare statement
  4. binlog的format设置成row格式

注:该问题已经上报给MariaDB,并且被确认为一个Bug,将在后续的版本中进行修复。