Shulou(Shulou.com)06/01 Report--

-record text- -

Conclusion first: in the end, we only solved the problem and did not find the root cause. This paper only focuses on the analysis and thinking of this problem.

Phenomenon:

When Master-5.0.X synchronizes with Slave-5.7.17, slave worker throws an error, duplicate primary

The error message is shown in the figure:

Analysis:

It seems to be a normal error report. The primary key is repeated. There are a lot of possibilities for this problem, but this time the problem is rather strange.

Because this slave is newly made with mydumper, it is a bit strange to report an error at the beginning of synchronizing several pieces of data.

After taking a look at the data in the table, the record of pk=13 does exist. Look at relaylog for so long and find the complete statement.

After finding this sentence, I found that something was wrong.

Because auto_increment is used as the primary key, binlog will indicate the specific value of the primary key before the statement of binlog when recording such statements

Judging from the content of binlog, this statement obviously should not be inserted into the record of pk=13, but should be 91391.

So if you export this data separately from Master and import it manually, you can also solve the problem by skipping this error.

I took a look at relaylog, and when I exported the data, I didn't modify it any more. Let's go~.

PS: because the Master writes very little, so can do this, heavy business, skip this method.

To be on the safe side, a new test library has been built to see if there will be a problem with such derived data.

Import the data and take a look.

There is no problem with import, and the data content is also ok, so try importing the data into the synchronized library.

It'seems that synchronous error reporting is not an accident.

Later, the following attempts have been made one after another:

Suspected that there was something wrong with the table. After all, it was imported from the 5.0.X library to 5.7.17, so I tried: alter table; mysql_upgrade; checked the value of auto_increament

Suspected use of fake relaylog synchronization (▔, ▔) synchronization: resynchronization

╯ '□' ╯ defective ┻━┻: insert data directly with insert...select from a test library that already has this data

It's all useless.

Finally, the doubt is put on the trigger of this table.

This trigger is used to convert the table character set, and it has been used in other databases before, and the information reported this time is not the content of the trigger, so it should be no problem.

However, apart from the trigger, there really seems to be nothing that could go wrong. After trying to delete the trigger, I found that everything was fine.

The final solution is to delete all triggers.

Think about:

When I first encountered this problem, I checked relaylog. The first guess was that it was the problem of binlog or the problem of the watch.

Because it can be seen from the error message that when sql_thread repeats this sentence, he thinks that pk=13, but what is recorded in binlog is obviously 91391.

Then it is possible that when sql_thread is in this piece of event, it does not recognize the information of this insert_id=91391 and directly ignores it (although the binlog is v4, but God knows what bug is not..)

Use the system's own auto_increament count (this guess is generated because the number of Master writes is very small, considering that the value of 13 is not large)

So I checked the auto_ increment value of the table, and tried to rebuild the table with mysql_upgrade and alter, but it didn't work.

Later, it was found that binlog was statement, which did not explicitly assign the values of all columns, and formed a transaction with another statement that followed.

Is it all right if you list all the complete information, or split the statement into a separate transaction?

So after that, export that row of data separately, and then try to insert it into the table, whether it's a source sql file or insert...select.

After these attempts, we can basically judge that it is not the problem of SQL.

Finally, we focus on the trigger, which is the only difference between the test table and the business table, but the information in the error has nothing to do with the trigger.

However, on the table that went wrong this time, there are still some clues that there may be something wrong with the trigger, that is, the auto_ increment recorded by the table itself.

The maximum value in the table is 91390, the data that failed to insert is 91391, and the auto_increament recorded in the table is 91392

But I found that I didn't know much about it, and there was no way to determine whether the value of this auto_increament was the entry point to find the root cause.

There is a long way to go. _ (: "∠") _

PS: when the source database exports the structure, it has been confirmed that the source library does not have triggers and stored procedures, and that the binlog of 5.0.x and 5.7is v4.

PPPPPPPPPS: after all, synchronization from 5.0.x to 5.7is not a common scenario, so let's just think of it as a cross-version synchronization problem. If you can switch to row mode, I'd like to see if there will be any problems.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.