mysql8.0 version repair self-increment column value reuse (fix bug#199)

Note: Prior to mysql8.0, the InnoDB storage engine table self-increment ID may be reused.

This can cause problems in a number of scenarios, including but not limited to: primary and Standby switching, historical data migration, and other scenarios. In the bug#199 of a lot of replies, you can see a lot of peer complaints. For example, if T1 has a history table t1_history used to store T1 table history data, then mysqld restart, the Ti_history table may already have (2,2) this data, and after the restart we inserted (2,2), when the newly inserted (2,2) migrated to the history table, Will violate the PRIMARY KEY constraint. Whether this kind of problem will occur in the data migration, we also need to pay attention to. For example, we use mysqldump to export data and then import it into another environment. mysqldump exporting data is a way to specify a self-growth value, not a null.

When you build a table, you can specify the Auto_increment value, not specifying the default is 1, which represents the starting value size of the current self-increment column, and if the newly inserted data does not specify a value for the self-increment column, the value of the self-increment column is the starting value. For InnoDB tables, this value is not persisted to the file. Instead, it exists in memory (DICT_TABLE_STRUCT.AUTOINC). Then again, since this value does not last, why does the show create table T1 see that the auto_increment value follows the change each time we insert a new value. In fact show create table T1 is obtained directly from Dict_table_struct.autoinc (Ha_innobase::update_create_info).

Know that the auto_increment is in real-time storage memory. So, where does mysqld get auto_increment after the restart? The memory value is definitely lost, and in fact MySQL takes the execution of a similar select MAX (ID) +1 from T1; method to get auto_increment, and this method is the cause of the self-increment ID duplication.

InnoDB auto_increment Lock Mode:

There are three possible settings for the Innodb_autoinc_lock_mode configuration parameter. For "Legacy", "continuous" or "interleaved" locking modes, the settings are 0, 1, or 2, respectively. Starting with MySQL 8.0, the interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, the continuous lock mode was the default (innodb_autoinc_lock_mode=1).

"The solution of the idea in MySQL8.0:"

The counter for the self-increment primary key is persisted to the redo log. Each time the counter changes, it is written to the redo log. If a database restart occurs, INNODB initializes its memory value based on the counter information in the Redo log. To minimize the impact on system performance, the counters are written to the redo log and are not refreshed immediately.

--common scenarios where problems occur because the self-increment primary key is not persisted:

1. The business will increase the primary key as a business primary key, while the business requires that the primary key cannot be duplicated.

2. The data will be archived. Primary key collisions may occur during the archiving process.

Therefore, it is strongly recommended that you do not use the self-increment primary key as the business primary key. Shaving these two scenes, in fact, the self-increasing primary key does not persist the problem is not very large, far from the imagination of the "infamous."

--: Finally, a solution to the archive scenario is given,

Create a stored procedure that initializes the Table1 (online table) based on the maximum value of the primary key from the Table2 (archive table). This stored procedure can be placed in the file specified by the Init_file parameter, and the SQL in the file is executed when the database is started.

DELIMITER;;

CREATE PROCEDURE ' Auto_increment_fromtable2 ' (in table1 varchar (255), table2 varchar (255))

BEGIN

Set @qry = Concat (' SELECT @max1: = (' id ' + 1) from ", Table1, ' ORDER by ' id ' DESC LIMIT 1; ');

Prepare stmt from @qry;

Execute stmt;

deallocate prepare stmt;

Set @qry = Concat (' SELECT @max2: = (' id ' + 1) from ", Table2, ' ORDER by ' id ' DESC LIMIT 1; ');

Prepare stmt from @qry;

Execute stmt;

deallocate prepare stmt;

IF @max1 < @max2 Then

Set @qry = concat (' ALTER TABLE ', table1, ' auto_increment= ', @max2);p repare stmt from @qry; Execute stmt;deallocate prepar e stmt;

SELECT ' updated ' as ' status ';

Else

SELECT ' no update needed ' as ' status ';

END IF;

END;;

DELIMITER;

###################################################################

The following is the case of the self-increasing primary key reuse before the MySQL8.0 is reproduced:

----Create a test table emp:

MySQL [test]> CREATE TABLE emp (ID int auto_increment,name varchar (ten), primary key (ID)); MySQL [test]> INSERT INTO EMP values (1, ' Zhang '); MySQL [test]> INSERT INTO EMP values (null, ' Liu '); MySQL [test]> INSERT INTO EMP values (null, ' Huang '); MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 |  Zhang | | 2 |  Liu | | 3 | Huang |+----+-------+3 rows in Set (0.00 sec)

---from the following information can be seen, the EMP table ID increment of the next number is 4

MySQL [test]> Show CREATE TABLE emp\g*************************** 1.  Row *************************** table:empcreate table:create Table ' emp ' (' id ' int (one) not NULL auto_increment, ' Name ' varchar () default NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=4 default charset=utf81 row in set (0. SEC)

---delete id=2 and 3 data, and then insert a single piece of data

MySQL [test]> Delete from emp where id=2 or id=3; MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 | Zhang |+----+-------+1 row in Set (0.01 sec) MySQL [test]> INSERT INTO EMP values (NULL, ' HHHH '); MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 |  Zhang | | 4 | HHHH |+----+-------+2 rows in Set (0.00 sec)

(as can be seen from the above results, if you do not restart the database, although the previous data is deleted, but the insertion of the data in this case its self-increment ID or the order of the data before the deletion is incremented.) ）

---Delete the data with ID 4, keep only the first row of data, and then restart MySQL as follows:

MySQL [test]> Delete from emp where id=4; MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 | Zhang |+----+-------+1 row in Set (0.00 sec)

After you restart MySQL----, insert a second piece of data:

MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 | Zhang |+----+-------+1 row in Set (0.00 sec) MySQL [test]> Show CREATE TABLE emp\g---(Restart MySQL here to view the table's self-increment ID, this is 2) * * 1.  Row *************************** table:empcreate table:create Table ' emp ' (' id ' int (one) not NULL auto_increment, ' Name ' varchar () default NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=2 default charset=utf81 row in set (0. XX sec)

---Insert another piece of data at this point, you will find that the self-increment ID is reused:

MySQL [test]> INSERT INTO EMP values (null, ' Feng '); MySQL [test]> SELECT * from emp;+----+-------+| ID |  Name |+----+-------+| 1 |  Zhang | | 2 | Feng |+----+-------+2 rows in Set (0.00 sec)

From the above test, we see that the auto_increment is 2 before inserting the new data, and then inserted (null, ' Feng '), the above test reflects the situation where the InnoDB storage engine's table self-increment ID may be reused after the mysqld reboot. If there is a situation in which the database may be inconsistent from the library!!!!!!!

Attention:

In addition, when MySQL starts a transaction, there is a class insert operation, and the increment value increases, but when the transaction is rolled back, the self-increment does not decrease. In other words, self-increment will be empty.

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

Two, different insert Insert method, will have the self-increment ID has the different influence, as follows:

1, first: a notation with a null value

Mysql> create table emp (id int auto_increment, a int, primary  key  (ID))  engine=innodb;mysql> insert into emp values  (2,2), (3,2); mysql [test]> show create table emp\g     --- This time to view the table's self-increment is 4*************************** 1. row ***************************        Table: empCreate Table: CREATE TABLE  ' emp '   (   ' ID '  int (one)  NOT NULL AUTO_INCREMENT,   ' a '  int (one)  default null,   PRIMARY KEY  (' id '))  engine=innodb auto_increment=4 default charset= utf81 row in set  (0.00 sec)      MySQL [test]>   insert into emp values  (4,2), (null,2), (null,2);   --- Use NULL to insert the value mysql [test]> show create table emp\G   ----will find that self-increment becomes 8, but the Data ID column for viewing the table is the largest 6,***********************  1. row ***************************       Table:  empcreate table: create table  ' emp '   (   ' id '  int (one)  not null  AUTO_INCREMENT,   ' A '  int (one)  DEFAULT NULL,  PRIMARY KEY  (' Id '))  ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set  (0.00 SEC) mysql [test]> select * from emp;+----+------+| id | a     |+----+------+|  1 |    2 | |   2 |    2 | |   3 |    2 | |   4 |    2 | |   5 |    2 | |   6 |    2 |+----+------+6 rows in set  (0.01 sec) Mysql [test]> insert  into emp values  (null,3);   ---when you insert a piece of data into the table, the value of the ID becomes 8mysql [test]>   select * from emp;+----+------+| id | a    |+--- -+------+|  1 |    2 | |   2 |    2 | |   3 |    2 | |   4 |    2 | |   5 |    2 | |   6 |    2 | |   8 |    3 |+----+------+7 rows in set  (0.00  SEC) from the above test can be seen, the use of NULL notation, the self-growth value will increase a value. 2. The second type: Insert the data using the insert into ...select  method as follows: Mysql> create table t1 (id int  auto_increment primary key,name varchar (255)); mysql> creatE table t2 (Name varchar (255)) Engine=innodb;mysql> insert into t2 values ( ' AA '), (' BB '); mysql> insert into t1 (name)  select *from t2;   -- -Insert data from the T2 table into the t1mysql> select * from t1;+----+------+| id | name |+--- -+------+|  1 | aa   | |   2 | bb   |+----+------+2 rows in set  (0.00 sec) mysql> show create table t1;   ---Then look at the self-increment of the T1 table and find that it is now 4, and the data is only 2 |  table | create table                                                                                               | t1     | CREATE TABLE  ' T1 '   (   ' id '  int (one)  not null auto_ increment,   ' name '  varchar (255)  DEFAULT NULL,  PRIMARY KEY  (' id ') )  engine=innodb auto_increment=4 default charset=utf8 |1 row in set   (0.00 SEC)

This also illustrates the use of insert. Select mode, self-growth will also increase a value.

Attention:

We can see the first type of notation with a null value, which adds one more value from the growth value; Select, self-growth also adds a value. What problem does this bring? You will find that the self-growth value from the library is normal (when the copy format is row), this is the fact that the master-slave data is inconsistent, but the impact is not big, unless the record ID is greater than the self-growth ID, the insertion of data duplication will be an error.

The reasons for this are also related to the positioning of the INSERT statement, which currently has these types of INSERT statements.

1, simple insert, such as INSERT into T (name) VALUES (' Test ')

2. Bulk INSERT, such as load Data | Insert INTO ... select .....

3, mixed insert, such as INSERT into T (id,name) VALUES (1, ' a '), (null, ' B '), (5, ' C ');

This has a big relationship with the parameter Innodb_autoinc_lock_mode, with the default parameter value of 1. Innodb_autoinc_lock_mode This parameter controls the behavior of the associated lock when inserting data into a table with auto_increment columns, with three values:

0: This expression tradition (tradition)

It provides a backward compatibility capability in which all INSERT statements ("Insert like") are given a table-level auto_inc lock at the beginning of the statement, releasing the lock at the end of the statement. Note that this is the statement level rather than the transaction level, and a transaction may contain one or more statements.

It guarantees predictability, continuity, and repeatability of the value assignment, which also guarantees that the INSERT statement can generate the same value as the master (which guarantees security based on statement replication) when copied to slave. Since the Auto_inc lock is kept to the end of the statement in this mode, this affects concurrent insertions.

1: This expression consecutive (continuous)

This mode is optimized for simple insert, because the number of simple insert disposable values can be determined immediately, so MySQL can generate several consecutive values at a time for this INSERT statement In general, this is also safe for replication (it guarantees the security of statement-based replication). Since MySQL has now recommended the binary format as the row format, there is no security issue with replication.

This mode is also the default mode of MySQL, the advantage of this mode is that the Auto_inc lock does not persist until the end of the statement, as long as the statement has the corresponding value can be released early lock.

2: This expression interleaved (staggered)

Since the Auto_inc lock is not available in this mode, the performance in this mode is the best, but it also has a problem that the Auto_incremant value it gets is not contiguous for the same statement.

Note: The default value of Innodb_autoinc_lock_mode This parameter has been changed to 2!!!!! in the MySQL8.0 version

mysql8.0 version repair self-increment column value reuse (fix bug#199)