Description:
Adding an AUTO_INCREMENT to a column changes it's values.
I would't expect this from MySQL, because I haven't seen anything like this in the documentation, thus I
would say it is an unexpected behavior.
I use a system that lets me keep managing the contents of tables very simple (few rows of PHP code). But
it needs to have one row with id = 0, and the id column must be AUTO_INCREMENT. This is achievable with
INSERT INTO test VALUES (0, ...);
UPDATE test SET id = 0 WHERE id = LAST_INSERT_ID();
But, when I need to transfer such table to a production server, it gets complicated.
I have to unset the AUTO_INCREMENT, transfer the data, set the id of the row 0 to some other, set the
AUTO_INCREMENT, and set the row 0's id back to 0. This is REALLY VERY annoying, esp. when I have six such
tables.
Note that from 3.23.55 to 3.23.58 the behavior changed so now I can't even do that ALTER TABLE ... A_INC
if the id=0 is present, it says "#1062 - Duplicate entry '1' for key 1", which is even bit more annoying.
How to repeat:
CREATE TABLE test ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) );
INSERT INTO test VALUES (0);
INSERT INTO test VALUES (1);
ALTER TABLE test CHANGE id id INT UNSIGNED
NOT NULL AUTO_INCREMENT;
/* with 3.23.58 ==>> #1062 - Duplicate entry '1' for key 1 */
/* with 3.23.55 ==>> OK, but changes the ID's */
SELECT * FROM test;
==>> (with 3.23.55)
1
2
==>> (with 3.23.58, but AUTO_INCREMENT was not set)
0
1
Suggested fix:
In MY opinion, INSERTing 0 (zero) into AUTO_INCREMENT shouldn't be translated at all - one can use NULLIF(<<inserted_value>>, 0) if he needs to. Everybody uses NULL to insert next AI value, anyway.
Then, tables like mine could be trasfered easily, with just dumping it.
CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) );
INSERT INTO test VALUES (0);
INSERT INTO test VALUES (1);
SELECT * FROM test;
==>>
0
1
But (probably) I have no chance to force things to be like that, so at least, changing the column to AUTO_INCREMENT shouldn't try to change zeros to next AI value.
CREATE TABLE test ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) );
INSERT INTO test VALUES (0);
INSERT INTO test VALUES (1);
ALTER TABLE test CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT;
SELECT * FROM test;
==>>
0
1
I would really appreciate if something changed...
Thanks, Ondra
