Question 1: synchronizing data from MySQL 5.1 to the 5.5 Library

Source: Internet
Author: User
Tags mysql commands

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.