AUTO_INCREMENT inconsitent with MyISAM on mySQL 4.0.13-log

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

-----------SQL Code: ----------
/*these lines were mostly written ad hoc & manually, but I believe it works
and shows the issue at hand */

CREATE TABLE `testje` (
`id` INT( 4 ) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL AUTO_INCREMENT ,
`val` VARCHAR( 1 ) NOT NULL ,
PRIMARY KEY ( `id` )
); /* Default is myISAM */

INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 2, val = 'b' */

ALTER TABLE `facturen` TYPE = INNODB
INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 1, val = 'b' */


ALTER TABLE `facturen` TYPE = ISAM
INSERT INTO `testje` (val) VALUES ('a');
REMOVE FROM `testje`;

INSERT INTO `testje` (val) VALUES ('b');
SELECT * FROM `testje` /*the result shows: id = 1, val = 'b' */

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]