MySQL Bugs: #15621: Rollback does not cancel auto index

Bug #15621	Rollback does not cancel auto index
Submitted:	9 Dec 2005 9:39	Modified:	9 Dec 2005 10:34
Reporter:	Robin Hickmott	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14-nt	OS:	Windows (Windows XP)
Assigned to:	Aleksey Kishkin	CPU Architecture:	Any

Description:
This may be normal behavior but seemed a bit strange to me. If you start a transaction insert say 50 rows and then one fails and you issue a rollback MySQL does not revert the tables auto index and subsequent rows get inserted as ID n + 49

How to repeat:
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`data` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = innodb;

START TRANSACTION;

INSERT INTO `test` ( `id` , `data` )
VALUES 
( NULL, 'DATA1'), 
( NULL, 'DATA2'),
( NULL, 'DATA3'),
( NULL, 'DATA4'),
( NULL, 'DATA5'),
( NULL, 'DATA6'),
( NULL, 'DATA7'),
( NULL, 'DATA8');

ROLLBACK;

START TRANSACTION;

INSERT INTO `test` ( `id` , `data` )
VALUES 
( NULL, 'DATA1');

COMMIT;

Suggested fix:
Not Sure... I guess rolling back the autoindex with the data :)

Robin,

it's intended behaviour, as far as autoincrement must provide just unique values for table and it's OK if tables  has gaps in the sequence of values assigned to the AUTO_INCREMENT column.

Not a problem its just as im developing a quotation and during development I noticed the quote numbers still went up when the query was being rolled back.

May thanks

same bug in version 5.0, under windows xp pro, Server version: 5.0.37-community-nt MySQL Community Edition (GPL)