Bug #15754 truncate table does not reset auto increment column in stored procedure

• Submitted: 14 Dec 2005 18:15
• Modified: 14 Dec 2005 18:31
• Reporter: Patrick Lin
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.15
• OS: Linux (Fedora Core 3)
• Assigned to: Assigned Account
• CPU Architecture: Any

[14 Dec 2005 18:15] Patrick Lin

Description:
The auto increment column is not reset when a table is truncated/deleted inside of a stored procedure when using select into to populate the table.

How to repeat:
delimiter //

drop procedure if exists testdelete
//

create procedure testdelete ()
reads sql data
begin

    drop temporary table if exists ids;

    create temporary table ids (
        id integer unsigned not null primary key
    );

    insert into ids (id) value (1);
    insert into ids (id) value (2);
    insert into ids (id) value (3);

    drop temporary table if exists testme;

    create temporary table testme (
        testid integer unsigned not null auto_increment primary key,
        anumber integer unsigned not null
    );

    insert into testme (anumber) select id from ids;

    select * from testme;

    truncate table testme;

    insert into testme (anumber) select id from ids;

    select * from testme;

    drop temporary table testme;
end
//

delimiter ;

[14 Dec 2005 18:31] MySQL Verification Team

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Duplicate of bug:

http://bugs.mysql.com/bug.php?id=14945