Repeated values in the innodb auto-increment column _ MySQL

Repeated innodb auto-increment column values 1. duplicate values in the innodb auto-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 (1,2);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 disable mysql, start mysql, and insert a data entry.

insert into t1 values (null,2);select * FROM T1;+----+------+| id | a |+----+------+| 1 | 2 |+----+------+| 2 | 2 |+----+------+

We can see that (2, 2) is inserted, and if I do not restart, what we get when we insert the same data is (4, 2 );

The above test shows that after mysql is restarted, the table auto-increment id of the innodb storage engine may be reused.

Auto-incremental id reuse occurs in some scenarios. In the preceding example, if t1 has a history table t1_history used to store the historical data of table t1, the data () may already exist in ti_history before the restart of mysqld, after the restart, we inserted () again. when the newly inserted () is migrated to the history table, the primary key constraint is violated.

2 Reasons for repeated values in the innodb auto-increment column

mysql> show create table t1/G;*************************** 1. row ***************************Table: t1Create 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=utf81 row in set (0.00 sec)

You can specify the AUTO_INCREMENT value when creating a table. If this parameter is not specified, the value is 1 by default. this value indicates the size of the starting value of the current auto-incrementing column. if the newly inserted data does not specify the value of the auto-incrementing column, the value of the auto-incrementing column is the starting value. This value is stored in the. frm file during table creation. After we insert new data, the starting value of the auto-incrementing column will become larger. Will the larger value be stored back to the. frm file?

For innodb tables, this value is not stored in. frm. Instead, it is stored in memory (dict_table_struct.autoinc ). Now, since this value is not stored in. frm, why does show create table t1 see that the AUTO_INCREMENT value changes after each new value is inserted. In fact, show create table t1 does not read frm to get the AUTO_INCREMENT value, but directly obtained from dict_table_struct.autoinc (ha_innobase: update_create_info ).

The AUTO_INCREMENT value in. frm is not updated in real time, but the auto_increment value is updated when we execute some DDL reconstruction.

We know that AUTO_INCREMENT is stored in the memory in real time, and the AUTO_INCREMENT value in. frm is not in real time. So, after mysqld is restarted, where can we get AUTO_INCREMENT? The memory value must be lost ,. the AUTO_INCREMENT in frm is inaccurate (probably smaller than the actual one ). in fact, mysql uses the select max (id) + 1 from t1; method to obtain AUTO_INCREMENT. This method causes repeated auto-increment IDs.

3 does myisam have this problem?

Myisam does not have this problem. The myisam table. frm file also saves the AUTO_INCREMENT value. like innodb, this value is not real-time. Myisam stores this value in the. MYI File (mi_state_info_write) in real time ). After mysqld is restarted, the AUTO_INCREMENT value (mi_state_info_read) is read from. MYI ). Therefore, restarting the myisam table will not cause repeated auto-incremental IDs.

4. fixed repeated innodb auto-increment column problems

Myisam chooses to store AUTO_INCREMENT in the. MYI file header in real time. In fact, other information is stored in the. MYI header in real time. that is to say, writing AUTO_INCREMENT is only a convenient operation. The performance loss can be ignored. There are two ways to solve this problem in the InnoDB table. 1) the maximum auto_increment value is permanently stored in the frm file. 2) the maximum value of auto_increment is persistent to the location of trx_id on the root page of the clustered index. The first method directly writes a file, which consumes a lot of performance. this is an additional operation, rather than a convenient operation. If so, we adopt the second solution. Why do you choose to store trx_id in the root page of the clustered index. Trx_id is stored in the trx_id of the page header, which is only valid for the secondary index page and insert buf page header (MVCC ). the trx_id value of the root page header of the clustered index is not used, and the initial value is always 0. 8 bytes in this position can store self-added values. Each time we update the AUTO_INCREMENT value, we also modify this value to the trx_id position in the root page header of the clustered index. This write operation is the same as the real data write operation. it complies with the write-ahead log principle, but only requires redo log instead of undo log. Because we do not need to roll back the changes in AUTO_INCREMENT (that is, the auto-increment column value will be retained after rollback, even if the insert rollback is performed, the auto_increment value will not be rolled back)

Therefore, the AUTO_INCREMENT value is stored in the location of the trx_id on the root page of the clustered index. it is actually the modification of the internal stub page and the addition of a redo log (a small amount ), the write of this redo log is asynchronous. it can be said that it is a convenient operation of the original transaction log. Therefore, the performance loss of AUTO_INCREMENT values stored on the clustered index root page is extremely small.

5. performance comparison after restoration

We have added the global parameter innodb_autoinc_persistent value on/off. on indicates that the AUTO_INCREMENT value is stored on the clustered index root page in real time. Off is stored only in the memory.

./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

It can be seen that the performance loss is below % 1.

6. improvement

The innodb_autoinc_persistent_interval parameter is added to control the frequency of the persistent auto_increment value. For example, when innodb_autoinc_persistent_interval = 100 and auto_incrememt_increment = 1, the auto_increment value is controlled every 100 insert operations. The 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
MS (RT)	2.25	2.27	2.26	2.25

Note: If innodb_autoinc_persistent needs to be enabled, it should be specified in the parameter file,

innodb_autoinc_persistent= on

If set global innodb_autoinc_persistent = on is specified in this way, the maximum value of auto_increment will not be read from the clustered index root page after restart.

Two questions:

1 for innodb and myisam storage engines, AUTO_INCREMENT in. frm is redundant. Other storage engines are not studied and are of any use.

2. restart the innodb table and use select max (id) + 1 from t1 to obtain the AUTO_INCREMENT value. if the id has an index, this statement uses the index to search quickly. This explains why mysql requires that the auto-increment column be included in the index. If no index is specified, the following error is returned,

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

The myisam table also has this requirement, which is redundant.

Appendix:

The innodb_autoinc_lock_mode parameter mainly solves the master-slave replication problem of the auto-increment column and is used to control the continuity of the auto-increment column value. It has nothing to do with this article. for details, refer to here