MySQL self-adding principal inconsistent tests (R12 notes, day 37)

There is a problem in MySQL that is particularly noteworthy, that is, the problem of duplicate values of self-increment columns, which has been briefly analyzed before, but after thinking about it, there are still many places to explain, one is how the self-increment columns of the slave library are maintained and whether to restart the slave library, the self-increment columns will be affected.

Let's continue to test it. First of all, reproduce the problem.

Create table T1 and insert 3 rows of data.

Use test

[test] > drop table if exists T1

Query OK, 0 rows affected, 1 warning (0.01 sec)

> create table T1 (id int auto_increment, an int, primary key (id)) engine=innodb

Query OK, 0 rows affected (0.02 sec)

Insert into T1 values (1Pol 2)

Insert into T1 values (null,2)

Insert into T1 values (null,2)

[test] > select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

| | 2 | 2 |

| | 3 | 2 |

Because there are three rows of data, the value of the self-incrementing column is 4. 0.

[test] > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0.00 sec) We delete the record with the highest id value id=3

Mysql > delete from T1 where id=3

Query OK, 1 row affected (0.02 sec) you will find that there is no change in the value of AUTO_INCREMENT=4 at this time.

If we dig into the contents of binlog, we will find that the insert statement is very special.

# / usr/local/mysql_5.7.17/bin/mysqlbinlog-- socket=/home/data/s1/s1.sock-- port=24801-vv / home/data/s1/binlog.000001 can see that the insert statement is a unique grammatical form of MySQL.

# SET

# @ 1room3 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2room2 / * INT meta=0 nullable=1 is_null=0 * /

# at 2271

Delete will also be deleted based on row-level changes and locating to specific records.

# DELETE FROM `test`.`t1`

# WHERE

# @ 1room3 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2room2 / * INT meta=0 nullable=1 is_null=0 * /

# at 2509

Let's restart the database.

# mysqladmin-socket=/home/data/s1/s1.sock-port=24801 shutdown

# / bin/sh / usr/local/mysql_5.7.17/bin/mysqld_safe-- defaults-file=/home/data/s1/s1.cnf & after rebooting, you will find that the situation has changed. The original self-increment 4 has now become 3, which is also calculated based on max (id) + 1.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

1 row in set (0.00 sec) at this time let's take a look at the slave library, will the self-increment value of the slave library change?

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

1 row in set (0. 00 sec) at this time, you will find that the value of the master-slave increment column is different after restarting the database.

So let's test it further and insert a record in the main library so that the value of the self-incrementing column is 4. 5.

Mysql > insert into T1 values (null,2)

Query OK, 1 row affected (0. 01 sec) the value of the self-increment column is 4, while the value of the self-increment column of the slave library remains unchanged.

Continue to insert a record, and the self-incrementing column of the main library will be 5.

Mysql > insert into T1 values (null,2)

Query OK, 1 row affected (0. 00 sec) and slave library, will the self-increment column continue to change at this time? Let's verify that the self-increment from the library takes effect again at this time.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

It is also important to note that the value of the self-incrementing column is specified, which is somewhat different from Oracle, but very similar.

At this time, the data in the main database is as follows:

Mysql > select * from T1

+-+ +

| | id | a |

+-+ +

| | 1 | 2 |

| | 2 | 2 |

| | 3 | 2 |

| | 4 | 2 |

| | 5 | 2 |

+-+ +

5 rows in set (0. 00 sec) to facilitate testing, we continue to insert a piece of data, this time I specify the id value.

Mysql > insert into T1 values (6Jing 2)

The consolation of Query OK, 1 row affected (0.00 sec) is that the self-increment column will continue to increase in this case.

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`a`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

1 row in set (0.00 sec) view the slave library at this time, and this auto-increment column is still 7.

Through this case, we can see that there will be such a problem in MySQL. In fact, in the case of multi-environment historical data archiving, data inconsistencies are likely to occur if the main database is restarted.

I also see a lot of people talking about this issue in MySQL's official bug list, and it seems that a lot of people have run into this hole. In fact, the realization of this problem is not a very tedious work, why it has not been repaired.

This problem existed in MySQL a long time ago and still exists today. When will it be fixed? according to the official plan, it will be fixed in 8.0. Let's wait and see.