TiDB Technical Issues

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB技术问题

| username: 这里介绍不了我

Using Dumpling to export data from a MySQL replica and using Lightning to import it into a new replica, after executing change master xxx position, a primary key conflict occurred. Can anyone knowledgeable explain the reason?

| username: 啦啦啦啦啦 | Original post link

Is the position used by change master taken from the master connected by the slave?

| username: 这里介绍不了我 | Original post link

The point used is from the master database, the position information from show slave status in the metadata. There were a few primary key conflicts, but after manually deleting them, the master-slave replication is now normal.

| username: 啦啦啦啦啦 | Original post link

Alright, the backup from the replica will provide a position for the primary and one for the replica. I thought it might be wrong, which is a bit strange. In a production environment, you can use pt to compare and see if there is any other inconsistent data.

| username: 这里介绍不了我 | Original post link

Alright, let me compare the data and take a look.

| username: 这里介绍不了我 | Original post link

Is it possible that the synchronization delay on the replica is causing this?

| username: 小龙虾爱大龙虾 | Original post link

Could you please post the dumpling command?

| username: 这里介绍不了我 | Original post link

dumpling -u xxx -p xxx -P xxx -h xxx --filetype sql -t 4 -o xxx -F 512MiB -B xxx

| username: dba远航 | Original post link

The master position used by change master to connect to the primary database.

| username: 小龙虾爱大龙虾 | Original post link

Check the character set and collation rules of the upstream and downstream tables that are reporting errors.

| username: 这里介绍不了我 | Original post link

This has nothing to do with it. What does primary key conflict have to do with character set?

| username: 这里介绍不了我 | Original post link

Do you want to take a look at what you’re saying?

| username: 小龙虾爱大龙虾 | Original post link

It is possible. When the primary key is a string, different character sets and collation rules handle trailing spaces differently. Refer to: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html#charset-unicode-sets-pad-attributes

| username: wangccsy | Original post link

It’s a bit confusing. Isn’t it common to use meaningless keys like GUIDs as primary keys? It’s rare to use business-related fields as primary keys, right?

| username: 这里介绍不了我 | Original post link

The problem is solved. There are several tables where the primary key of the original table has id=0.

| username: dba远航 | Original post link

It’s good that it’s resolved.

| username: Kongdom | Original post link

:thinking: Does it mean that the primary keys of the source table and the target table are different, and there are cases where the data in the source table has duplicate primary keys in the target table?

| username: 这里介绍不了我 | Original post link

The original table has an auto-increment primary key with a value of 0. During the dump, a primary key value will be assigned to this record. When the actual data corresponding to this primary key value is inserted, a primary key conflict will occur.

| username: Kongdom | Original post link

:thinking: Indeed, this will cause a primary key duplication issue.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.