auto_increment behaviour

[Lewis Graham]

I have a funny issue with the engine "renumbering" auto_increment columns when it is stopped and restarted.

Running the two attached scripts shows the problem (need to stop and restart the service between running the scripts). The output from the second script is:

+----+-------+
| ID | Name |
+----+-------+
| 1 | Test1 |
| 2 | Test2 |
| 3 | Test4 |
+----+-------+

so clearly ID 3 is being re-used. Is there any background to this issue or a way of preventing it?

--------------- SNIP SCRIPT 1 ---------------
CHARSET utf8;
SET NAMES utf8;
DROP DATABASE IF EXISTS test;

CREATE DATABASE test
DEFAULT CHARSET utf8
DEFAULT COLLATE utf8_general_ci;

USE test;

CREATE TABLE test
(
ID INT NOT NULL auto_increment,
Name VARCHAR(64),
CONSTRAINT test_PK PRIMARY KEY(ID)
) ENGINE=InnoDB;

INSERT INTO test( Name ) VALUES( "Test1" );
INSERT INTO test( Name ) VALUES( "Test2" );
INSERT INTO test( Name ) VALUES( "Test3" );
COMMIT;

SELECT * FROM test;

DELETE FROM test WHERE ID = 3;

SELECT * FROM test;
--------------- END SNIP SCRIPT 1 ---------------
--------------- SNIP SCRIPT 2 ---------------
USE test;

INSERT INTO test( Name ) VALUES( "Test4" );
COMMIT;

SELECT * FROM test;
--------------- END SNIP SCRIPT 2 ---------------