A MySQL master-slave synchronization inconsistency problem caused by character set problem tracing

Recent business preparation on-line a new feature, after pouring data suddenly found master-slave synchronization stop, error is as follows:

Error ' Duplicate entry ' 66310984-2014-04-18 00:00:00--122815.sh ' for key ' PRIMARY "on query. Default database: ' Bill '. Query: ' INSERT intoBOND3311(OBJECTID,BONDID,BONDNAME,DECLAREDATE,F001D,F002D,F003N,F004N,F005D,F006D,F007D,F008D,MEMO,RECTIME,MODTIME,ISVALID,F009N,SEQID,SECNAME,F010N,F014V,F011D,F013N,SECCODE) VALUES (0x373236313333373339,0x3636333130393834, 0X32303131C4EAD0C2BDAEB9E3BBE3CAB5D2B5CDB6D7CA28BCAFCDC529D3D0CFDED4F0C8CEB9ABCBBEB9ABCBBED5AEC8AF, ' 2014-04-14 00:00:00 ', null,null,0x352e3833,0x35382e33, ' 2014-04-18 00:00:00 ', Null,null,null,0xbbd8cadbb2bfb7d6b6d2b8b6, ' 2014-04-14 13:48:56 ', ' 2014-04-14 13:48:56 ', 0x31,0x3230,0x31373430333831393837,0x3131b9e3bbe3d5ae,0x352e3833, 0xb6d2b8b6, ' 2014-04-21 00:00:00 ', 0x3130352e3833,0x3132323831352e7368) '

As you can see from the error message, the values of many ID (int) fields are converted to ASCII code execution, which results in a synchronous interrupt. The first reaction is the character set problem, because the MySQL character set has a number of parameters, and in the use of Chinese, the character set conversion will be affected by many character sets in cline,connection,server,table, so there are often some garbled situation.

So we first check whether the data in the main library is written properly, and the result is normal. Then according to the following structure:

cline--> master--> Slave

Since the main library data is written correctly, you can only suspect that characters escaped when master and slave do replication, causing the above to happen. We have checked the following parameter settings, all are consistent.

• Table Character Set settings
• The character set settings for the server
• Database version
• SQL mode

Such a strange problem, had to turn to the almighty search engine, and finally found the following blog, the explanation is very clear. As explained in the blog, there are 2 prerequisites for this problem:

• The program uses prepared statement
• The character set uses multibyte character sets, such as GBK

Based on the solution provided in the blog, we first abort the synchronization, modify Binlog format, and then copy the data from the main library to the data consistency from the library, and when the synchronization is turned on, the problem is resolved.

PS: Such a strange problem, really do not encounter do not know, this is the bare experience of the problem.

A MySQL master-slave synchronization inconsistency problem caused by character set problem tracing