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

The following mainly brings you mysql8.0 self-increment value reuse problem how to solve, I hope these contents can bring you practical use, this is also my edit mysql8.0 self-increment value reuse problem how to solve the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

This may cause problems in many scenarios, including but not limited to: master / slave switching, historical data migration and so on. In the pile of responses below bug#199, you can see a large number of peer complaints. For example, suppose T1 has a history table t1_history to store the historical data of T1 table, then before mysqld restart, the ti_ table may already have this data (2jue 2), and after restart we insert (2jue 2), when the newly inserted (2jue 2) migrates to the history table, it will violate the primary key constraint. We also need to pay attention to whether such problems will occur in data migration. For example, we use mysqldump to export data and then import it into another environment. Mysqldump exports data in which the method of self-growth is specified, not empty.

When you create a table, you can specify the AUTO_ index value, which defaults to 1 when not specified. This value represents the size of the starting value of the current increment column. If the newly inserted data does not specify the value of the increment column, then the value of the increment column is the starting value. For the InnoDB table, this value is not persisted to the file. It is stored in memory (dict_table_struct.autoinc). Then ask, since this value does not last, why does show create table T1 see that the AUTO_ index value changes every 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).

I know that AUTO_INCREMENT is stored in real-time memory. So, after mysqld restarts, where can I get AUTO_INCREMENT? The memory value must have been lost. In fact, MySQL uses a method like select max (id) + 1 from T1; to get AUTO_INCREMENT, which is the cause of self-incrementing id repetition.

InnoDB AUTO_INCREMENT Lock Mode:

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

"the solution in MySQL8.0:"

Persist the counter of the self-incrementing primary key to redo log. Each time the counter changes, it is written to the redo log. If the database is restarted, InnoDB initializes its memory value based on the counter information in redo log. To minimize the impact on system performance, counters are written to redo log and are not immediately refreshed.

-- Common scenarios where problems occur due to the lack of persistence of the self-increasing primary key:

1. The business uses the self-increasing primary key as the business primary key, and at the same time, the business requires that the primary key can not be repeated.

two。 The data will be archived. Primary key conflicts may occur during the archiving process.

Therefore, it is strongly recommended that you do not use a self-increasing primary key as a business primary key. Excluding these two scenarios, in fact, the problem of non-persistence of the self-increasing primary key is not very big, far less than the "notoriety" imagined.

Finally, a solution under the archiving scenario is given.

Create a stored procedure to initialize table2 (online table) based on the maximum value of the table1 (archive table) self-incrementing primary key. This stored procedure can be placed in the file specified by the init_file parameter, and the SQL in that file is executed when the database starts.

DELIMITER

CREATE PROCEDURE `auto_increment_ fromtable2` (IN table1 VARCHAR (255), IN table2 VARCHAR (255))

BEGIN

Set @ qry = concat ('SELECT @ max1: = (`id` + 1) FROM `', table1,' `ORDER BY `id` DESC LIMIT 1bot')

Prepare stmt from @ qry

Execute stmt

Deallocate prepare stmt

Set @ qry = concat ('SELECT @ max2: = (`id` + 1) FROM `', table2,' `ORDER BY `id` DESC LIMIT 1bot')

Prepare stmt from @ qry

Execute stmt

Deallocate prepare stmt

IF @ max1

< @max2 THEN set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt; SELECT 'updated' as `status`; else SELECT 'no update needed' as `status`; END IF; END ;; DELIMITER ; ################################################################### 下面复现MySQL8.0之前，自增主键复用的情况： ----创建测试表emp： MySQL [test]>

Create table emp (id int auto_increment,name varchar (10), primary key (id)); MySQL [test] > insert into emp values; 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)

-as you can see from the information below, the next number in the emp table id is 4.

MySQL [test] > show create table emp\ gateway * 1. Row * * Table: empCreate Table: CREATE TABLE `emp` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (10) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.08 sec)

-delete data from id=2 and 3, and then insert a 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 [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 you can see from the above results, if the database is not restarted, although the previous data is deleted, its self-increasing id is still incremented before the data is deleted. )

-Delete the data with an id of 4, keep only the first row of data, and 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 restarting MySQL, insert a piece of data again:

MySQL [test] > select * from emp +-+-+ | id | name | +-+-+ | 1 | zhang | +-+-+ 1 row in set (0.00 sec) MySQL [test] > show create table emp\ G-- (after restarting MySQL, check the self-increment id of the table here. (this is 2) * * 1. Row * * Table: empCreate Table: CREATE TABLE `emp` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (10) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf81 row in set (0.00 sec)

-if you insert another piece of data at this time, you will find that the self-added id has been 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 can see that the AUTO_INCREMENT is 2 before inserting new data, and then insert (null,'feng'). The above test reflects that the table self-increment id of the InnoDB storage engine may be reused after mysqld restart. If there is a slave library, it may lead to database inconsistency!

Note:

In addition, when MySQL starts a transaction, the self-increment of classful INSERT operations increases, but when the transaction is rolled back, the self-increment does not decrease. In other words, there will be holes in self-appreciation.

Thank you for

2. Different insert insertion methods will have different effects on self-increasing id, as follows:

1. The first one: writing with null value.

Mysql > create table emp (id int auto_increment, an int, primary key (id)) engine=innodb;mysql > insert into emp values (1), (2), (3) MySQL [test] > show create table emp\ G-at this point, the self-increment of the table is 4 percent * 1. Row * * Table: empCreate Table: CREATE TABLE `emp` (`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) MySQL [test] > insert into emp values (4jue 2), (null,2), (null,2) -use null to insert the value MySQL [test] > show create table emp\ G-this time you will find that the self-increment has become 8, but the maximum id column for viewing the table is 6. * * 1. Row * * Table: empCreate Table: CREATE TABLE `emp` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` int (11) 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 | 4 | 2 | 5 | 2 | 6 | 2 | + 6 rows in set (0.01sec) MySQL [test] > insert into emp values (null,3);-at this point, if a piece of data is inserted into the table, the value of id becomes 8MySQL [test] > select * from emp. +-+ | id | a | +-+-+ | 1 | 2 | 2 | 3 | 2 | 4 | 2 | 5 | 2 | 6 | 2 | 8 | 3 | +-+ 7 rows in set (0.00 sec) it can be seen from the above test that the self-growth value will be increased by one more value when null is written. 2. The second way: insert data using insert into... select, 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 T2 table into 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 However, there are only 2 data entries | Table | Create Table | T1 | CREATE TABLE `t1` (`id` int (11) 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)

It also shows that using insert.. Select mode, self-growth will also increase one more value.

Note:

We can see the first way of writing with a null value, the self-increasing value will increase by one more value, and the second kind of insert.. Select, self-growth will also increase by one more value. What problems will this bring? You will find that the self-growth value from the database is normal (when the copy format is ROW), that is, the master-slave data is inconsistent, but the impact is not significant, unless the record ID is greater than the self-growing ID, so the insertion data repeat will report an error.

The reason also has something to do with the positioning of insert statements. At present, there are 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... . From... .

3. Mixed insert, such as insert into t (id,name) values (1) null,'b'), (5)

This has a lot to do with the parameter innodb_autoinc_lock_mode, which defaults to 1. The parameter innodb_autoinc_lock_mode controls the behavior of the associated lock when inserting data into a table with an auto_increment column, with three values:

0: this means tradition (traditional)

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

It ensures the predictability, continuity and repeatability of value assignment, which ensures that insert statements can generate the same values as those of master when they are copied to slave (it ensures the security of statement-based replication). Because in this mode the auto_inc lock is held until the end of the statement, this affects concurrent insertions.

1: this means consecutive (continuous)

Simple insert is optimized in this mode, and because the number of values inserted by simple insert at a time can be determined immediately, MySQL can generate several consecutive values at a time for this insert statement; generally speaking, this is also safe for replication (it ensures the security of statement-based replication). Since MySQL has now recommended setting the binary format to ROW format, there are no replication security issues.

This mode is also the default mode of MySQL, and the advantage of this mode is that the auto_inc lock is not held until the end of the statement, as long as the statement gets the corresponding value, the lock can be released ahead of time.

2: this means interleaved (interlaced)

Since there is no auto_inc lock in this mode, the performance in this mode is the best; but it also has a problem, which is that the auto_ increment value it gets may not be contiguous for the same statement.

Note: the default value of this parameter for innodb_autoinc_lock_mode has been changed to 2 in the MySQL8.0 version!

For the above about mysql8.0 self-increment value reuse problem how to solve, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.