MySQL Forums :: Newbie :: auto_increment behaviour


Advanced Search

auto_increment behaviour
Posted by: Lewis Graham ()
Date: August 24, 2009 01:36AM

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 ---------------

Options: ReplyQuote


Subject Written By Posted
auto_increment behaviour Lewis Graham 08/24/2009 01:36AM
Re: auto_increment behaviour Rick James 08/24/2009 10:40PM
Re: auto_increment behaviour Lewis Graham 08/25/2009 06:04AM
Re: auto_increment behaviour Lewis Graham 08/25/2009 11:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.