InnoDB self-Increment column duplicate value problem

Source: Internet
Author: User
Tags table definition

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.