Bug #37531 After truncate, auto_increment behaves incorrectly for InnoDB

• Submitted: 19 Jun 2008 19:38
• Modified: 20 Jun 2010 0:58
• Reporter: Ryan Thiessen
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.25/6.0
• OS: Any
• CPU Architecture: Any
• Tags: auto_increment, truncate

[19 Jun 2008 19:38] Ryan Thiessen

Description:
After a TRUNCATE operation, the auto_increment counter does not work properly when you INSERT specific values for the auto_increment field.

Ideally after a TRUNCATE operation, INSERT operations should behave exactly as they do after the CREATE TABLE operation.

Tested on 5.1.25/InnoDB/Linux/Windows; 5.1.22/InnoDB/Linux
Cannot repeat failure on 5.0.58/InnoDB/Linux or 5.1.25/MyISAM/Linux

How to repeat:
create table autoinc_truncate_bug(pk int primary key auto_increment) engine=innodb;
insert into autoinc_truncate_bug values (1), (2), (3);
insert into autoinc_truncate_bug values (Null), (Null), (Null);
truncate table autoinc_truncate_bug;
insert into autoinc_truncate_bug values (1), (2), (3);
# succeeds, but auto_increment counter not updated
insert into autoinc_truncate_bug values (Null), (Null), (Null);
# final insert fails with duplicate key error

[19 Jun 2008 20:58] MySQL Verification Team

Thank you for the bug report. This bug not affects 5.0 and it is repeatable on 6.0  and 5.1.23 and 5.1BK.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.26-rc-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >create table autoinc_truncate_bug(pk int primary key auto_increment) engine=innodb;
Query OK, 0 rows affected (0.20 sec)

mysql 5.1 >insert into autoinc_truncate_bug values (1), (2), (3);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1 >insert into autoinc_truncate_bug values (Null), (Null), (Null);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1 >truncate table autoinc_truncate_bug;
Query OK, 6 rows affected (0.05 sec)

mysql 5.1 >insert into autoinc_truncate_bug values (1), (2), (3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1 ># succeeds, but auto_increment counter not updated
mysql 5.1 >insert into autoinc_truncate_bug values (Null), (Null), (Null);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql 5.1 ># final insert fails with duplicate key error
mysql 5.1 >

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.64-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > create database db5;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use db5
Database changed
mysql 5.0 > create table autoinc_truncate_bug(pk int primary key auto_increment) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 > insert into autoinc_truncate_bug values (1), (2), (3);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > insert into autoinc_truncate_bug values (Null), (Null), (Null);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > truncate table autoinc_truncate_bug;
Query OK, 6 rows affected (0.05 sec)

mysql 5.0 > insert into autoinc_truncate_bug values (1), (2), (3);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > # succeeds, but auto_increment counter not updated
mysql 5.0 > insert into autoinc_truncate_bug values (Null), (Null), (Null);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > # final insert fails with duplicate key error
mysql 5.0 >

[19 Jun 2008 21:58] Ken Jacobs

It seems a readily available and effective workaround is to ALTER the autoinc value after the table is truncated.  This is not to suggest that this should not be corrected, only that the triage code of W1 (none) has overlooked a simple workaround.

create table autoinc_truncate_bug(pk int primary key auto_increment) engine=innodb;
insert into autoinc_truncate_bug values (1), (2), (3);
insert into autoinc_truncate_bug values (Null), (Null), (Null);
truncate table autoinc_truncate_bug;

# re-initialize the autoinc value right after truncation
alter table truncate_bug ATUT_INCREMENT = n; /* set n as desired */

insert into autoinc_truncate_bug values (1), (2), (3);
# succeeds, but auto_increment counter not updated
insert into autoinc_truncate_bug values (Null), (Null), (Null);
# final insert fails with duplicate key error

[19 Jun 2008 22:33] MySQL Verification Team

Adding work-around flag.

[19 Jun 2008 23:14] MySQL Verification Team

A small correction about the work-around provided by Ken (he asked me to do):

Read:

alter table truncate_bug AUTO_INCREMENT = n;

and not

alter table truncate_bug ATUT_INCREMENT = n; 

Thanks Ken.

[19 Jun 2008 23:14] Sunny Bains

The cached last value (prebuilt->last_value) needs to be reset on TRUNCATE TABLE. I have a very simple fix for this bug.

Regards,
-sunny

[24 Jun 2008 0:01] Omer Barnir

triage: setting to P3 as this is a regression

[2 Aug 2008 5:40] Calvin Sun

Fixed in snapshot innodb-5.1-ss2545.tar.gz.

[20 Aug 2008 22:23] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52096

2715 Timothy Smith	2008-08-20
      Cherry-pick some changes from innodb-5.1-ss2545 snapshot.  Includes fixes for
      Bug#37531, Bug#36941, Bug#36941, Bug#36942, Bug#38185.
      
      Also include test case from Bug 34300 which was left out from earlier snapshot
      (5.1-ss2387).
      
      Also include fix for Bug #29507, "TRUNCATE shows to many rows effected", since
      the fix for Bug 37531 depends on it.

[21 Aug 2008 17:59] Bugs System

Pushed into 5.1.28  (revid:[e-mail]-20080820221833-brmru9b77ddt3tfx) (version source revid:[e-mail]-20080821081500-f2d61fh4u61owz3p) (pib:3)

[21 Aug 2008 23:41] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52267

2794 Timothy Smith	2008-08-21 [merge]
      Merge up from 5.1-bugteam.  Among other things, this applies some changes from
      the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941,
      Bug#36942, and Bug#38185.

[21 Aug 2008 23:43] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52268

2794 Timothy Smith	2008-08-21 [merge]
      Merge up from 5.1-bugteam.  Among other things, this applies some changes from
      the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941,
      Bug#36942, and Bug#38185.

[27 Aug 2008 1:38] Paul DuBois

Noted in 5.1.28 changelog.

After TRUNCATE TABLE for an InnoDB table, inserting explicit values
into an AUTO_INCREMENT column could fail to increment the counter and
result in a duplicate-key error for subsequent insertion of NULL.

Setting report to NDI pending push into 6.0.x.

[13 Sep 2008 23:42] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080820221833-brmru9b77ddt3tfx) (version source revid:[e-mail]-20080808091208-ht48kyzsk7rim74g) (pib:3)

[16 Sep 2008 4:58] Paul DuBois

Noted in 6.0.7 changelog.

[5 May 2010 15:10] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 15:57] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:10] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:38] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:06] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:03] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:15] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:02] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:43] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)