Bug #5915 ALTER TABLE behaves differently when converting column to auto_increment in 4.1

• Submitted: 6 Oct 2004 9:25
• Modified: 7 Oct 2004 15:14
• Reporter: Dmitri Lenev
• Status: Closed
• Category: Server
• Severity: S2 (Serious)
• Version: 4.1-bk
• Assigned to: Dmitri Lenev

[6 Oct 2004 9:25] Dmitri Lenev

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

[7 Oct 2004 15:11] Dmitri Lenev

Actually this bug was caused by the following changeset:

ChangeSet 1.2017.10.25 2004/09/26 17:11:28 [e-mail]
  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).

[7 Oct 2004 15:14] Dmitri Lenev

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 [e-mail]
  Fix for bug #5915 "ALTER TABLE behaves differently when converting column
  to auto_increment in 4.1".