MySQL 5.1同步到5.5卡庫問題一則

MySQL 5.1同步到5.5卡庫問題一則

今天在MySQL 5.1同步到5.5時遇到一則卡庫問題，在從庫上show slave status \G，報錯如下：

Error 'BIGINT UNSIGNED value is out of range in   '(group.mg_group_var.grp_status_cnt + -(38))'' on query. Default   database: 'group'. Query: 'update mg_group_var set   grp_status_cnt=grp_status_cnt+(-38) where mgid = '302412' and   grp_status_cnt>0'

為何在主庫上能執行，在從庫上就會失敗呢？

追查原因

在從庫上，show create table 查看錶結構：

*************************** 1. row ***************************Table: mg_group_varCreate Table: CREATE TABLE mg_group_var (mgid bigint(20) NOT NULL,grp_status_cnt int(11) unsigned NOT NULL DEFAULT '0',grp_member_cnt int(11) unsigned NOT NULL DEFAULT '0',grp_apply_cnt int(6) DEFAULT NULL, 

發現grp_status_cnt 列為無符號的整數類型。 查詢一下grp_status_cnt的值：

mysql> select grp_status_cnt from mg_group_var  where  mgid = '302412';+----------------+| grp_status_cnt |+----------------+|             27 |+----------------+

27-38後得到的是負數。
官方手冊中有一段話：對於無符號數，如果相減得到負數，mysql會自動轉化為最大的正數。

(Subtraction between integer values, where one is of type   UNSIGNED, produces an unsigned result by default. If the   result would otherwise have been negative, it becomes the   maximum integer value）

驗證一下

在5.1上執行如下語句：

mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+| CAST(0 AS UNSIGNED) - 1 |+-------------------------+|    18446744073709551615 |+-------------------------+

但是在5.5上執行的時候就會報錯：

mysql> SELECT CAST(0 AS UNSIGNED) - 1;  ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '()cast(0 as unsigned) - 1)'

按照官方文檔的解釋，在5.1上，當相減得到負數，馬上就被轉化為18446744073709551615，而它超出了不帶正負號的整數的範圍，所以被轉化為最大的int類型的無符號數4294967295(但是在5.5上，這個轉化報錯，因此導致同步中斷)。

（When an out-of-range value is assigned to an integer   column, MySQL stores the value representing the corresponding   endpoint of the column data type range. If you store 256 into a   TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255,   respectively. ）

回到生產資料庫，在主庫上，執行：

mysql> select grp_status_cnt from mg_group_var where    grp_status_cnt > 300000000 limit 3;+----------------+| grp_status_cnt |+----------------+|     4294967295 ||     4294967295 ||     4294967272 |+----------------+

可以發現這種溢出的情況很多，它們其實都是從負數轉化來的，這些資料從應用的角度來說，都是錯誤的。

解決方案

解決從庫卡庫問題的辦法很多，常用的有如下幾種：

1.跳過指定的錯誤碼

• 重啟mysql，設定slave_skip_errors=1069。這需要重啟資料庫，代價太大，所以不推薦。
• 用watch -n與mysql命令相結合，每秒跳過一個命令點:

watch -n 1 'mysql -uroot -prootpassword -S /tmp/  mysql4389.sock -e "set global sql_slave_skip_counter=1;start slave;" '

但是watch的時間間隔最小隻能到1s，因此只適合跳過少數卡庫的命令點。如果從庫卡庫累積的命令點非常多，此法就不可取了。

• 用pt-slave-restart工具跳過命令點。這也是推薦的跳過命令點的方法：

     
  
      
  可以指定按照錯誤碼匹配，例如1062，1069等；
      
  可以指定按照錯誤文本進行正則匹配；
      
  智能的調整從庫同步狀態檢查的時間間隔，因此如果發現連續的卡庫問題，1s內可以跳過非常多的sql語句。這些特點對於快速恢複服務是非常重要的。
     

2.跳命令點始終只能解決一時的問題，根本上還是應修改代碼邏輯，確保不出現無符號減法的溢出。3.對DBA來說，採用行格式複製，從根本上可以保證主從資料的一致性。畢竟跳過卡庫問題是簡單的，但是後續的資料修複過程卻是複雜的。參考資料

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
http://dev.mysql.com/doc/refman/5.1/en/out-of-range-and-overflow.html

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：