| Bug #104499 | MySQL auto_inc value changed after a failed insert | ||
|---|---|---|---|
| Submitted: | 31 Jul 2021 20:35 | Modified: | 26 Aug 2021 22:04 |
| Reporter: | Zongzhi Chen (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb | ||
[2 Aug 2021 13:19]
MySQL Verification Team
Hi Mr. zongzhi, Thank you for your bug report. We managed, very easily to repeat the behaviour that you report: ERROR 1062 (23000) at line 7: Duplicate entry '3' for key 't1.b' a b 1 1 2 2 3 3 4 4 5 5 6 6 Table Create Table t1 CREATE TABLE `t1` (\n `a` int unsigned NOT NULL AUTO_INCREMENT,\n `b` int NOT NULL,\n PRIMARY KEY (`a`),\n UNIQUE KEY `b` (`b`)\n) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci -------------------------- RESTART ------------------------------------------------------- Table Create Table t1 CREATE TABLE `t1` (\n `a` int unsigned NOT NULL AUTO_INCREMENT,\n `b` int NOT NULL,\n PRIMARY KEY (`a`),\n UNIQUE KEY `b` (`b`)\n) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci a b 1 1 2 2 3 3 4 4 5 5 6 6 4294967295 7 a b 1 1 2 2 3 3 4 4 5 5 6 6 20 9 Table Create Table t1 CREATE TABLE `t1` (\n `a` int unsigned NOT NULL AUTO_INCREMENT,\n `b` int NOT NULL,\n PRIMARY KEY (`a`),\n UNIQUE KEY `b` (`b`)\n) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Verified as reported. Thank you for your contribution !!!!
[26 Aug 2021 22:04]
Zongzhi Chen
I find the comment in the code said this is the general behavior of MySQL
```
if (!index->table->is_temporary() && !index->table->skip_alter_undo &&
dict_table_has_autoinc_col(index->table)) {
ib_uint64_t counter =
row_get_autoinc_counter(entry, index->table->autoinc_field_no);
if (counter != 0) {
/* Always log the counter change first, so it won't
be affected by any follow-up failure. */
dict_table_autoinc_log(index->table, counter, &mtr);
}
}
```
And If add a condition that only the successfully insert will change the autoinc then it can pass the mtr. However, it violate the behavior.
I don't know why innodb must add the autoinc if the insert operation failed?
[27 Aug 2021 12:02]
MySQL Verification Team
Hi Mr. zongzhi, It is simply that, if the table has auto-inc column, then always the counter has to be get from InnoDB before proceeding further.
Description: After a failed insert into mysql which change the auto_inc value. The auto_inc value in memory hasn't changed. However, this new auto_inc value has saved into disk. Then after restart, the table's auto_inc is set to the new value. If the new auto_inc value is too large, then the insert before restart can be success, however, after restart, the insert is failed. below is the mtr that reproduce the case. How to repeat: --echo # Create table CREATE TABLE t1(a INT UNSIGNED AUTO_INCREMENT KEY, b INT not null unique) ENGINE = InnoDB; INSERT INTO t1 VALUES(null, 1), (null, 2), (null, 3); -- error 1062 INSERT INTO t1 VALUES(4294967295, 3); INSERT INTO t1 VALUES(4, 4), (5, 5), (6, 6); SELECT * FROM t1; SHOW CREATE TABLE t1; --echo # Scenario 1: Normal restart --source include/restart_mysqld.inc --echo # We expect persist autoinc is 4294967295 SHOW CREATE TABLE t1; INSERT INTO t1 VALUES(null, 7); SELECT * FROM t1; delete from t1 where b = 7; alter table t1 AUTO_INCREMENT= 20; INSERT INTO t1 VALUES(null, 9); SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; ===================================================== The mtr result ----------- MYSQLTEST OUTPUT START ----------- # Create table CREATE TABLE t1(a INT UNSIGNED AUTO_INCREMENT KEY, b INT not null unique) ENGINE = InnoDB; INSERT INTO t1 VALUES(null, 1), (null, 2), (null, 3); INSERT INTO t1 VALUES(4294967295, 3); ERROR 23000: Duplicate entry '3' for key 't1.b' INSERT INTO t1 VALUES(4, 4), (5, 5), (6, 6); SELECT * FROM t1; a b 1 1 2 2 3 3 4 4 5 5 6 6 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int unsigned NOT NULL AUTO_INCREMENT, `b` int NOT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # Scenario 1: Normal restart # restart # We expect persist autoinc is 4294967295 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int unsigned NOT NULL AUTO_INCREMENT, `b` int NOT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(null, 7); SELECT * FROM t1; a b 1 1 2 2 3 3 4 4 5 5 6 6 4294967295 7 delete from t1 where b = 7; alter table t1 AUTO_INCREMENT= 20; INSERT INTO t1 VALUES(null, 9); SELECT * FROM t1; a b 1 1 2 2 3 ▽ 4 5 5 6 6 20 9 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int unsigned NOT NULL AUTO_INCREMENT, `b` int NOT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1; Suggested fix: MySQL should not change the auto_inc value in disk if the insert is failed..