Mysql 5.1.42 alter table auto_increment = 0 doesn't work, truncate does

Question

For my automated acceptance tests, I want inserts to start with id=1. I achieved this on one PC (XP 32bit, mysql 5.1.something) with (after deleting all rows from the table), "alter table tableName auto_increment = 0".

I'm now setting up a new PC (Windows 7 64bit, mysql 5.1.42), and this command seems to have no effect. I can see in the information_schema.tables table that the auto_increment value is not changed back to 0 --- it just keeps going up. If I try to change the value in that table directly, I'm told that access is denied to 'root'@'localhost'. Does this perhaps give a hint to my problem?

Other stackoverflow people had suggested that "truncate from tableName" is a good alternative. I'm happy to report that this works. But does anyone know why the "alter table" command won't reset the auto_increment?

Thanks!

Answer 1

NOt sure why it worked on one server, and doesn't work on the other, but the MySQL manual states (quoting, emphasis mine) :

To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to any that have already been used.
For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one.
For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

Maybe that's the cause of the problem : you are trying to put the auto_increment counter back to 0, but it's already higher than that value -- and as you cannot reset it to a value that's less than any value that's already been used, it doesn't work.