Mysql auto-incrementing column leads to duplicate primary key issue analysis..., mysql primary key

Mysql auto-incrementing column leads to duplicate primary key issue analysis..., mysql primary key

A few days ago, the development of shoes reported a problem of using the load data infile command to import data primary key conflicts. After analysis, it was determined that this problem may be a bug in mysql. I would like to share it with you here. In the future, it is difficult to sleep because of similar problems encountered by children's shoes. Haha. Speak nonsense and start with the question.

After obtaining the problem, first check the field and find that the maximum value recorded in the problem table is greater than the value of the auto-increment column. Obviously, when a record is inserted, the value generated by the auto-increment column may conflict with the existing record primary key, resulting in an error. First, try to solve the problem. Manually increase the value of the auto-incrementing column to ensure that the value is greater than the existing primary key in the table. After adjustment, the data is imported normally. The problem is solved. Next we need to clarify the cause of the problem. What operations have led to this phenomenon?

There is a possibility that the business logic contains the code for updating the auto-incrementing primary key. because mysql's update action does not update the auto-incrementing column value at the same time, if the value of the updated primary key is larger than that of the auto-incrementing column, this also causes the above phenomenon: the maximum value of the record is greater than the value of the auto-incrementing primary key. However, the Development Feedback shows that this table only has the load data infile operation and does not update the primary key. Therefore, this explanation does not work. Continue the analysis. The table contains unique constraints. Will the table be related to the unique constraint? offline Experiment Simulation is not reproduced. Later, I thought about whether it would be related to the master-slave switchover, Because I performed a master-slave switchover two days ago. As a result, the test was performed in conjunction with the Master/Slave environment, which is related to the Master/Slave switchover. The source of all problems is clear.

Prerequisites for the problem:

1. mysql replication is based on the row Mode

2. innodb table

3. The table contains an auto-incrementing primary key and a unique constraint.

4. load data infile insert data using the replace into syntax [in case of duplicate and unique constraints, directly overwrite]

How the problem occurs:

1. When the master database encounters a duplicate unique constraint, perform the replace operation;

2. replace changes to delete + insert on the master database, but the binlog records update;

3. the standby database retries the update action to update the primary key. However, because the update action does not update the auto-incrementing column value, the record value after update is greater than the auto-incrementing column value.

Problem reproduction experiment:

 

Preparations			Create table test_autoinc (id int auto_increment, c1 int, c2 varchar (100), primary key (id), unique key (c1 )); Insert into test_autoinc (c1, c2) values (1, 'abc '); Insert into test_autoinc (c1, c2) values (2, 'abc '); Insert into test_autoinc (c1, c2) values (3, 'abcd '); Insert into test_autoinc (c1, c2) values (4, 'abcd '); Insert into test_autoinc (c1, c2) values (5, 'abcd ');
1	Operation	Remarks	Master	Slave
2	View auto-incrementing column values Show create table Test_autoinc \ G	After five records are inserted, the value of the auto-increment column is changed to 6.	Create table 'test _ autoinc '( 'Id' int (11) not null AUTO_INCREMENT, 'C1' int (11) default null, 'C2 'varchar (100) default null, Primary key ('id '), Unique key 'c1' ('c1 ') ) ENGINE = InnoDB AUTO_INCREMENT = 6 default charset = utf8	Create table 'test _ autoinc '( 'Id' int (11) not null AUTO_INCREMENT, 'C1' int (11) default null, 'C2 'varchar (100) default null, Primary key ('id '), Unique key 'c1' ('c1 ') ) ENGINE = InnoDB AUTO_INCREMENT = 6 default charset = utf8
3	View table data		Id | c1 | c2 --- + ------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd	Id | c1 | c2 --- + ------ 1 | 1 | abc 2 | 2 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd
4	View binlog location Show master status \ G	Record the current binlog point, You can view the binlog events generated by the replace action later.	Mysql-bin.000038 59242888
5	Replace operation Replace into test_autoinc (c1, c2) values (2, 'eeeee ');	Two records are affected. primary database replace = Delete + insert	Query OK, 2 rows affected (0.00 sec)
6	View table data		Id | c1 | c2 --- + ------ + ------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee	Id | c1 | c2 --- + ------ + ------- 1 | 1 | abc 3 | 3 | abcdd 4 | 4 | abcdd 5 | 5 | abcdd 6 | 2 | eeee
7	View binlog events Show binlog events in 'mysql-bin.000038' from 59242888;	You can also use the mysqlbinlog tool to analyze logs and query the update statements executed from the slave database.	Pos | Event_type --------- + --------------- 59242888 | Query 59242957 | Table_map 59243013 | Update_rows_v1 59243072 | Xid
8	View auto-incrementing column values Show create table	In this case, the auto-increment column of master is 7, and the auto-increment column of slave is 6, which is the same as the maximum value in the table.	Create table 'test _ autoinc '( 'Id' int (11) not null AUTO_INCREMENT, 'C1' int (11) default null, 'C2 'varchar (100) default null, Primary key ('id '), Unique key 'c1' ('c1 ') ) ENGINE = InnoDB AUTO_INCREMENT = 7	Create table 'test _ autoinc '( 'Id' int (11) not null AUTO_INCREMENT, 'C1' int (11) default null, 'C2 'varchar (100) default null, Primary key ('id '), Unique key 'c1' ('c1 ') ) ENGINE = InnoDB AUTO_INCREMENT = 6

After step 1, slave provides services if a master-slave switchover occurs. In this case, records with primary key 6 are inserted through the auto-incrementing column, a primary key conflict occurs.

How can this bug be solved? For the replace operation, the delete and insert events are also generated when the binlog is generated instead of an update event. Alternatively, the auto-increment column value is also updated when the update primary key is executed. Of course, this is only a purely theoretical analysis. The specific method to solve this problem should be based on the internal implementation of mysql to avoid introducing new problems. This bug I colleague has submitted to the community, http://bugs.mysql.com/73563, you can look.

The problem of setting primary key auto-increment in mysql

Database modification mysql Character Set: mysql character set can be set when mysql is installed. Generally, use utf8 Character Set 1 to view the SQL statement "Show create table tablename" for creating a table "tablename; // view the SQL statement used to CREATE the tablename TABLE | maos_mail_batch | CREATE TABLE 'maos _ mail_batch '('id' varchar (32) character set latin1 NOT NULL, 'batch _ time' varchar (30) character set latin1 not null, 'batch _ INTRODUCE 'varchar (2000) character set latin1 default NULL, 'batch _ Sum' int (11) default NULL, 'batch _ state' varch Ar (1) character set latin1 default NULL, primary key ('id ')) ENGINE = MyISAM default charset = utf8 | 2 modify the character set of the database and table alter database maildb default character set utf8; // modify the character set of the database alter table mailtable default character set utf8; // modify the character set of the table. If you want to change the default character set and all character columns (CHAR, VARCHAR, TEXT) of the table to the new character set, use the following statement: alter table tbl_name convert to character set charset_name; warning: the previous operation converted the column type between CHARACTER sets. If one column uses a character set (such as latin1), but the stored value actually uses another character set (such as utf8), this is not what you want. In this case, you must perform the following operations on such columns. Alter table t1 CHANGE c1 c1 BLOB; alter table t1 CHANGE c1 c1 text character set utf8; this method can implement this function because, no conversion is performed when you convert data to a BLOB column or from a BLOB column. 3. Primary Key of mysql: two primary keys of Mysql. Primary key and not null auto_incriment when creating a mysql table, a primary key is added to a field. The Primary key does not need to be inserted during data insert. mysql automatically adds 0, however, if the value is not set during the second insert operation, mysql database still defaults to 0, which leads to duplicate primary keys. This is not acceptable. When the primary key is defined, the primary key value must be filled in when the data is inserted. When a mysql table is created, the primary key not null auto_increment = 1 is added to a field, which is also a primary key. The growth starts with 1. This field does not need to be filled in. mysql database automatically fills in the value without the primary key. Alter table tb add primary key (id); Alter table tb change id int (10) not null auto_increment = 1; 4. Delete the auto-incrementing primary key id. Delete the auto-incrementing primary key. Alter table tb change id int (10). // Delete the auto-incrementing primary key. ...... remaining full text>
 
Mysql auto-increment primary keys will repeat

No