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!