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

When a record is inserted, the value generated by the self-added column may conflict with the primary key of the existing record, resulting in an error. First of all, try to solve the problem by manually increasing the value of the self-added column to ensure that it is greater than the existing primary key in the table. After adjustment, the guide data is normal.

Preconditions for problems:

1. mysql replication is based on row pattern

2. innodb table

3. Table contains self-increasing primary keys and unique constraints

4. load data infile Insert data using replace into syntax [Overwrite directly if duplicate unique constraint is encountered]

How the problem occurs:

1. When the master library encounters duplicate unique constraints, perform replace operation;

2. replace actually changes to delete+insert on the main library, but binlog records update;

3. The backup library repeats the update action to update the primary key, but since the update action does not update the self-added column value, the updated record value is greater than the self-added 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,'abcdd');

insert into test_autoinc(c1,c2) values(4,'abcdd');

insert into test_autoinc(c1,c2) values(5,'abcdd');

1

operation

remarks

Master

slave

2

View Self-Added Column Values

Show create table test_autoinc\G

After inserting 5 records, the self-increment value becomes 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 locus,

You can see the binlog event generated by the replace action later

mysql-bin.000038

59242888

5

replace operation

replace into test_autoinc(c1,c2) values(2,'eeee');

Affect two records, main library 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 parse logs with the mysqlbinlog tool to query update statements executed from the library

Pos | Event_type

---------+---------------

59242888 | Query

59242957 | Table_map

59243013 |Update_rows_v1

59243072 | Xid

8

View Self-Added Column Values

Show create table test_autoinc\G;

At this time, the self-increment column of master is 7, and the self-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=InnoDBAUTO_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=InnoDBAUTO_INCREMENT=6

9 Manually enlarge the self-increasing primary key Show create table test_autoinc\G;

Manual increase self-increasing id

alter table test_autoinc auto_increment=12;

Show create table test_autoinc\G;

alter table test_autoinc auto_increment=12;

Show create table test_autoinc\G;

The self-increasing id of master and slave is consistent.

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.