Hi List,

FIRSTLY: Please give me a CC, because I won't be on this list forever to look for responses!

My AUTO_INCREMENT behaves different on different table types. According to the manual it should have lowered/refreshed the LAST_INSERT_ID() on removal of records so it is equal to MAX(auto_incrementing_field)+1 (see: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html). On the InnoDB specific page about AUTO_INCREMENTING (see: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html) it says it should behave the same (I leave transaction rollbacks out of the emphasis).

Still, both on MySQL 4.0.13-log and 3.23.49-log on two different servers and different it behaves as I've written down in the SQL code below.

My question is: Am I misinterpreting the documentation or have I spotted inconsistent behavoir? The first could well be the case, but then the docs might not be too clear on the matter, while talking a great deal about SIGNED values and their problems with auto_increment and other problems...

Before trying a different table type I've flushed the mysqld, myisamchk'ed it, restored backups and checked if newly generated tables behaved the same, and finally came to the conclusion that ANY insert will increase the AUTO_INCREMENT, while that does NOT mean it's MAX(field)+1 because of modifications and deletions.

The SQL is below.

Kind regards,

Erik de Bruijn www.lowvoice.nl