Bug #26746 "delete from tbl" resets the auto_increment

• Submitted: 1 Mar 2007 11:10
• Modified: 1 Mar 2007 12:39
• Reporter: Thorsten Last Name
• Status: Closed
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.26-community-max-nt
• OS: Windows (Windows XP Pro)
• CPU Architecture: Any
• Tags: auto_increment, last_insert_id(), reset

[1 Mar 2007 11:10] Thorsten Last Name

Description:
A "delete from tbl" resets the auto_increment value if you restart the mysql service.

How to repeat:
-- 1.
create table T1(
  ID                        serial,
  Name                      varchar(50),
  primary key (ID)
);

-- 2.
insert into T1 (Name) values ('Row1');
insert into T1 (Name) values ('Row2');
insert into T1 (Name) values ('Row3');
commit;

-- 3.
Select * from T1 order by ID;

-- Output:
-- +----+------+
-- | ID | Name |
-- +----+------+
-- |  1 | Row1 |
-- |  2 | Row2 |
-- |  3 | Row3 |
-- +----+------+
-- 3 rows in set (0.00 sec)

-- 4.
-- Delete rows:
Delete from T1;
commit;

-- 5.
-- Insert new rows:
insert into T1 (Name) values ('Row4');
insert into T1 (Name) values ('Row5');
insert into T1 (Name) values ('Row6');
commit;

-- 6.
Select * from T1 order by ID;

-- Output:
-- +----+------+
-- | ID | Name |
-- +----+------+
-- |  4 | Row4 |
-- |  5 | Row5 |
-- |  6 | Row6 |
-- +----+------+
-- 3 rows in set (0.00 sec)

-- 7.
-- Stop mysql: net stop mysql5
-- Start mysql: net start mysql5

-- 8.
-- Delete rows:
Delete from T1;
commit;

-- 5.
-- Insert new rows:
insert into T1 (Name) values ('Row7');
insert into T1 (Name) values ('Row8');
insert into T1 (Name) values ('Row9');
commit;

-- 6.
Select * from T1 order by ID;

-- Output:
-- +----+------+
-- | ID | Name |
-- +----+------+
-- |  1 | Row7 |
-- |  2 | Row8 |
-- |  3 | Row9 |
-- +----+------+
-- 3 rows in set (0.00 sec)

-- I expected IDs "7, 8, 9" for "Row7, Row8, Row9".

Suggested fix:
*Never* automagically reset the "auto_increment" value.

[1 Mar 2007 11:32] MySQL Verification Team

Thank you for the bug report. I assume you have the InnoDB engine as
default table type. If it is the case please read:

http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html

"InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT  to the table, as described earlier."

in this case it is a documented behavior.

[1 Mar 2007 12:39] Thorsten Last Name

Wow, this was fast!

Yes, the table type is innoDB. 
I think it would be best to switch the type to "myIsam". Although ... there is nos support for transactions in myIsam tables...

I think, this bug-report is closed.