Question 1: synchronizing data from MySQL 5.1 to the 5.5 Library
Today, when synchronizing data from MySQL 5.1 to MySQL 5.5, a problem occurs in the sub-database: show slave status \ G. The error is as follows:
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'
Why does the slave database fail to be executed on the master database?
Tracing reason
On the slave database, view the table structure in 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,
The grp_status_cnt column is an unsigned integer. Query the value of grp_status_cnt:
mysql> select grp_status_cnt from mg_group_var where mgid = '302412';+----------------+| grp_status_cnt |+----------------+| 27 |+----------------+
A negative number is obtained after 27-38.
In the official manual, for the unsigned number, mysql will automatically convert it to the largest positive number if a negative number is obtained by subtraction.
(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)
Verify
Run the following statement on 5.1:
mysql> SELECT CAST(0 AS UNSIGNED) - 1;+-------------------------+| CAST(0 AS UNSIGNED) - 1 |+-------------------------+| 18446744073709551615 |+-------------------------+
However, when executed on 5.5, an error is reported:
mysql> SELECT CAST(0 AS UNSIGNED) - 1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '()cast(0 as unsigned) - 1)'
According to the explanation in the official documentation, when a negative number is obtained by subtraction of 5.1, it is immediately converted to 18446744073709551615, and it is beyond the unsigned integer range, so it is converted to the maximum unsigned number of int type 4294967295 (but on 5.5, this conversion reports an error, resulting in synchronization interruption ).
(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. )
Return to the production database and run the following command on the master database:
mysql> select grp_status_cnt from mg_group_var where grp_status_cnt > 300000000 limit 3;+----------------+| grp_status_cnt |+----------------+| 4294967295 || 4294967295 || 4294967272 |+----------------+
We can find that there are many such overflow situations. They are actually converted from negative numbers. These data are all incorrect from the application perspective.
Solution
There are many solutions to the problem of the slave database and the following are commonly used:
1. Skip the specified error code
- Restart mysql and set slave_skip_errors to 1069. This requires restarting the database, which is too costly, so it is not recommended.
- Use watch-n in combination with mysql commands to skip one command point per second:
watch -n 1 'mysql -uroot -prootpassword -S /tmp/ mysql4389.sock -e "set global sql_slave_skip_counter=1;start slave;" '
However, watch can only have a minimum interval of 1 s. Therefore, it is only suitable for skipping command points of a few card libraries. This method is not advisable if there are many command points accumulated from the database card library.
- Use pt-slave-restart to skip the command point. This is also the recommended method for skipping command points:
- You can specify matching by error code, for example, limit 2, 1069;
- You can specify Regular Expression matching based on the error text;
- Intelligent adjustment of the interval between slave Database Synchronization status checks. Therefore, if you find a continuous card library problem, you can skip a lot of SQL statements within 1 s. These features are very important for quick service recovery.
2. The Skip command point can only solve the problem for a while. You should modify the code logic to ensure that there is no overflow of the unsigned subtraction. 3. For DBA, the row format replication can fundamentally ensure the consistency of master and slave data. After all, skipping the card library is simple, but the subsequent data restoration process is complicated. References
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
-------------------------------------- Split line --------------------------------------
Install MySQL in Ubuntu 14.04
MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF
Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL
Build a MySQL Master/Slave server in Ubuntu 14.04
Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS
Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04
MySQL-5.5.38 universal binary Installation
-------------------------------------- Split line --------------------------------------
This article permanently updates the link address: