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

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.

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.