MySQL Bugs: #5915: ALTER TABLE behaves differently when converting column to auto

Bug #5915	ALTER TABLE behaves differently when converting column to auto_increment in 4.1
Submitted:	6 Oct 2004 7:25	Modified:	7 Oct 2004 13:14
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1-bk	OS:
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
In 4.0 ALTER TABLE replaces NULL and zero values with new id values when converting
some column to auto_increment column.
Behavior of current 4.1 snapshot is different in this respect (and it is not documented).

How to repeat:
In 4.0:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t (i int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (NULL),(NULL),(0),(0);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t modify i int not null auto_increment primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 2

mysql> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

In 4.1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.6-gamma-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (NULL),(NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t modify i int not null auto_increment primary key;
ERROR 1062 (23000): Duplicate entry '0' for key 1
mysql> update t set i = 0;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> alter table t modify i int not null auto_increment primary key;
ERROR 1062 (23000): Duplicate entry '0' for key 1

Actually this bug was caused by the following changeset:

ChangeSet 1.2017.10.25 2004/09/26 17:11:28 mskold@mysql.com
  Setting MODE_NO_AUTO_VALUE_ON_ZERO at copying in copy_data_between_tables

Besides changing behavior for zero values, this changeset exposed the fact that NULL values 
were first converted to 0 and then stored in auto_increment field. Thus their converison were 
affected by NO_AUTO_VALUE_ON_ZERO mode (which is bug too).

As part of the fix for this bug we are now automatically force NO_AUTO_VALUE_ON_ZERO mode in ALTER TABLE only when converting one auto_increment column to another auto_increment column (This includes the case when we don't touch this column at all).

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.2059.3.1 2004/10/07 13:02:39 dlenev@brandersnatch.localdomain
  Fix for bug #5915 "ALTER TABLE behaves differently when converting column
  to auto_increment in 4.1".