Online Slave reports 1062 Cases

Online Slave reports 1062 Cases

Recently, the online Slave often reported a 1062 error. It broke the ground. Fortunately, Slave has not been used in the business for the time being. That is to say, there is no read/write splitting, so Slave has problems and has little impact, however, I reported the 1062 primary key conflict error every other time, which made me very entangled. If I didn't solve it, I wouldn't dare to go to Atlas. So I 've been investigating what caused it. Although we all know that when the primary key or unique key of the data inserted by the Master node already exists on the Slave, Last_Errno: 1062 will be reported, and the Master-Slave synchronization will be disconnected. However, it is strange that the data on the Slave is the same as the data to be inserted by the Master every time when 1062 is reported, which is enough to exclude the possibility of manual data insertion.

Troubleshooting process:

1. If 1062 errors occur frequently, pay attention to the time point at which the error is reported to the table in the database and whether the error is reported again next time.

2. When a 1062 error occurs, check the latest Slave backup to check whether the data has been stored on the Slave.

3. When the 1062 error occurs, check whether the Row Records of the Master and Slave are the same. If the row records are the same each time, you can check whether there is a timer to call the storage process for the Insert operation.

The error 1062 message on Slave is as follows:

Check the Master binlog records:

You can see that a record is inserted in the Master binlog. log on to the Master and check it:

Previously, the binlog format was originally used with the default mixed. Later, I thought it was possible that the binlog format caused data problems and changed it to ROW, but the problem still exists.

View the information on the Slave. We can see that the binlog format is also ROW and set it to read_only. The ROW data record is exactly the same as that of the Master, as shown below:

Is it a bit strange here? How did the data on Slave come from? Later, I checked the stored procedures and timers related to the table, as shown below: (I replaced the table name with numbers. Please forgive me !)

Create Procedure

Create definer = 'root' @ 'localhost' PROCEDURE '_ sp_1036 '()
Begin declare _ count int unsigned default 0; DECLARE _ current_time timestamp default CURRENT_TIMESTAMP (); select count (*) INTO _ count FROM _ 1030 WHERE F04 is not null and F05> _ current_time;
Insert into _ 1036 SET F01 = DATE (_ current_time), F02 = HOUR (_ current_time), F03 = _ count on duplicate key update F03 = VALUES (F03); END

Create Event

Create definer = 'root' @ 'localhost' event' _ daily_sp_1036 'on schedule every 1 hour starts '2017-01-01 00:00:00' on completion preserve enable do call _ sp_1036 ()

This timer runs once an hour, calls the stored procedure, and inserts data into the table. In fact, the stored procedure and the timer are not properly written here, the problem lies in "create definer = 'root' @ 'localhost'. The historical pitfalls are great. The read_only setting on Slave is only useful to common users and useless to management-level users, therefore, Slave also executes the storage process when the timer reaches the time. To prove that Slave has its own data, we did a test to stop the Slave SQL thread:

You can see that the master-Slave synchronization is disconnected, and Slave also generates a record every hour. It is already obvious how the data on Slave comes from.

From the above we can see that the Master data is the same as the Slave data. In this way, the Master-Slave synchronization is processed first, and a transaction is skipped through set global SQL _slave_skip_counter = 1. If the data is inconsistent, take the Master data record as the standard:

We can see that after skipping a transaction, an interesting Log is reported: the event's master log FIRST. At this time, the primary key conflict of the same record is reported and executed again.

We can see that the synchronization is normal, although it is normal. To ensure data integrity, we recommend that you use the pt-table-checksum I previously wrote to verify the integrity of a data.

Several questions are discussed:

I. Why is the error 1062 reported in the above case sometimes?

2. When the master node synchronizes data, the system reports the 1062 error, or when the slave executes the timer to tune the storage process and inserts the data into the slave, the system reports the 1062 error?

Hey, you are welcome to discuss it.

Summary:

1. Manage MySQL permissions, minimize permissions, and prohibit administrator-level users from running programs.

2. Perform regular master-slave data integrity verification to ensure that the master-slave data is consistent, especially in read/write splitting scenarios.

This article permanently updates the link address: