MySQL多位元組字元集造成主從資料不一致問題

標籤：style   blog   http   使用   io   strong   檔案   資料   

MySQL多位元組字元集造成主從資料不一致問題  

來自江羽   2013-04-27 16:03:56|  分類： 預設分類|舉報|字型大小 訂閱

轉載： 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，將在後續的版本中進行修複。