InnoDB self-Increment column duplicate value problem

1 InnoDB The problem of duplicate values in the self-increment column

Start with the problem and reproduce the bug

Use Test;drop table t1;create table T1 (ID int. auto_increment, a int, primary key (ID)) Engine=innodb;insert into T1 values INSERT into T1 values (null,2), insert into T1 values (null,2), select * FROM t1;+----+------+| ID | A |+----+------+| 1 | 2 | | 2 | 2 | | 3 | 2 |+----+------+delete from T1 where id=2;delete from T1 where Id=3;select * from t1;+----+------+| ID | A |+----+------+| 1 | 2 |+----+------+

Here we close MySQL, then start MySQL, and then insert a piece of data

INSERT into T1 values (null,2), select * FROM t1;+----+------+| ID | A |+----+------+| 1 | 2 |+----+------+| 2 | 2 |+----+------+

We see Insert (2,2), and if I do not restart, insert the same data we get should be (4,2);

The above test reflects the possibility that the InnoDB storage engine's table self-increment ID may be reused after MySQL restarts.

Self-increment ID reuse problems occur next to certain scenarios. Still using the above example, assuming that T1 has a history table t1_history used to store T1 table historical data, then mysqld restart, ti_history may already have (2,2) this data, and after the restart we inserted (2,2), when the newly inserted (2,2) When migrating to a history table, the primary key constraint is violated.

2 InnoDB The reason for duplicate values in the self-increment column

Mysql> Show CREATE TABLE t1\g;*************************** 1. Row ***************************table:t1create table:create Table ' t1 ' (' id ' int (one) not NULL auto_increment, ' a ' int (11) Default Null,primary KEY (' id ')) engine=innodb auto_increment=4 default charset=utf81 row in Set (0.00 sec)

You can specify a auto_increment value when you build a table, and default to 1 when not specified. This value represents the starting value size of the current self-increment column, and the value of the self-increment column is the starting value if the newly inserted data does not specify a value for the self-increment column.

For InnoDB tables, this value is present in memory (DICT_TABLE_STRUCT.AUTOINC). Then again, 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 missing,. In fact, MySQL takes the execution of similar select MAX (ID) +1 from T1; method to get auto_increment. This method causes the self-increment ID to be duplicated.

In MySQL, you can set the step size, but the parameter control step and offset of two variables is Uto_increment_increment,auto_increment_offset is in the global configuration file, you can pass show VARIABLES Like ' auto_inc% '; to see the step size of the modifier through the set auto_increment_increment=4, and this effect is for all table structures, use cautiously

3 MyISAM also have this problem?

MyISAM is not the problem. MyISAM table. frm file also has auto_increment value, same as InnoDB, this value is not real-time. MyISAM will store this value in real-time. The Myi file (mi_state_info_write). Mysqld will be re-started from. Read the Auto_increment value (Mi_state_info_read) in the myi. Therefore, the MyISAM table restart is not an issue with self-increment ID duplicates.

4 InnoDB self-increment column with duplicate problem fix

MyISAM choose to store auto_increment in the header of the. myi file in real time. As a matter of fact. Myi's head also saves other information in real time, meaning that writing auto_increment is just a passing operation. The performance loss can be ignored. InnoDB table if you want to solve this problem, there are two ways. 1) Persist the Auto_increment maximum value to the frm file. 2) Persist the auto_increment maximum to the location where the clustered index root page trx_id. The first method of direct write file performance is expensive, which is an extra operation, not a passing operation. If we adopt the second option. Why choose to store the root page header of the clustered index trx_id. Page Header trx_id Storage trx_id, only valid for level two index pages and insert BUF headers (MVCC). The Clustered Index root page header trx_id This value is not used, and always remains the initial value of 0. Just this position. 8 bytes can be stored from value added. Each time we update the auto_increment value, this value is also modified to the location of the clustered index root page header trx_id. This write operation follows the Write-ahead log principle, just like a real data write operation, except that only redo log is required and no undo log is required. Because we do not need to roll back auto_increment changes (that is, the self-increment column values are retained after rollback, even if insert is rolled back, the auto_increment value is not rolled back)

Therefore, the auto_increment value is stored in the location of the clustered index root page trx_id, which is actually a modification of the internal stub page and a redo log (which is very small), and this redo log write is also asynchronous, which can be said to be a passing operation of the original transaction log. Therefore, the auto_increment value is stored on the clustered index root page and this performance loss is minimal.

5 Performance comparisons after repair

We have added the global parameter Innodb_autoinc_persistent value on/off, on indicates that the auto_increment value is stored in real time on the clustered index root page. Off is stored in memory only in the original way.

./bin/sysbench--test=sysbench/tests/db/insert.lua--mysql-port=4001--mysql-user=root \--mysql-table-engine= InnoDB--mysql-db=sbtest--oltp-table-size=0--oltp-tables-count=1 \--num-threads=100--mysql-socket=/u01/zy/ Sysbench/build5/run/mysql.sock  --max-time=7200--max-requests runset Global Innodb_autoinc_persistent=off;tps: 22199 Rt:2.25msset Global innodb_autoinc_persistent=on;tps:22003 rt:2.27ms

You can see that the performance loss is below% 1.

6 improvements

The new parameter innodb_autoinc_persistent_interval is used to control the frequency of persisted auto_increment values. For example: Innodb_autoinc_persistent_interval=100,auto_incrememt_increment=1, that is, every 100 times the insert controls the Auto_increment value of the persistence. Each persistent value is: the current value +innodb_autoinc_persistent_interval.

The test results are as follows

	Innodb_autoinc_persistent=off	Innodb_autoinc_persistent=on Innodb_autoinc_persistent_interval=1	Innodb_autoinc_persistent=on innodb_autoinc_persistent_interval=10	Innodb_autoinc_persistent=on innodb_autoinc_persistent_interval=100
Tps	22199	22003	22069	22209
RT (MS)	2.25	2.27	2.26	2.25

Note: If we use the need to turn on innodb_autoinc_persistent, we should specify in the parameter file,

Innodb_autoinc_persistent= on

If this specifies set global Innodb_autoinc_persistent=on, the Auto_increment maximum value will not be read from the clustered index root page after the restart.

Two questions:

1 for InnoDB and MyISAM storage engines, the auto_increment in. frm is redundant. Other storage engines have no research and do not know if there is any use.

2 InnoDB table, reboot by select MAX (ID) +1 from t1 to get the auto_increment value, if there is an index on the ID then this statement uses the index to find it quickly. So, this can explain why MySQL requires that the self-increment column must be included in the index. If no index is specified, the following error is reported.

ERROR 1075 (42000): incorrect table definition; There can is only one auto column and it must is defined as a key

And MyISAM table unexpectedly also has this request, feeling is superfluous.

Report:

Innodb_autoinc_lock_mode This parameter mainly solves the self-increment primary and standby replication problem, which controls the self-increment column value continuity. This article has nothing to do with the detailed reference here

InnoDB self-Increment column duplicate value problem