MySQL Bugs: #3431: Error whilst adding new column for a InnoDB table.

Bug #3431	Error whilst adding new column for a InnoDB table.
Submitted:	9 Apr 2004 20:17	Modified:	11 Sep 2005 14:43
Reporter:	Gary Bentley	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	mysql Ver 12.18 Distrib 4.0.12, for Win	OS:	Windows (W2K Pro)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When adding a new column via "ALTER TABLE ADD ..." and that table already satisfies the following conditions:

   1. It contains an auto_increment primary key column.
   2. One of the tuples in the table has the primary key set to 0.
   3. One of the tuples in the table has the primary key set to 1.

Then the following error is generated when the ALTER TABLE ADD command is executed:

ERROR 1062: Duplicate entry '1' for key 1

How to repeat:
1. Create table:

create table testing (
k_key mediumint unsigned auto_increment not null,
value varchar(10),
primary key (k_key)) Type=InnoDB;

2. Populate table with:

   INSERT INTO testing SET value = 'TEST1';
   INSERT INTO testing SET value = 'TEST2';

3. Check table occupancy:

   SELECT * FROM testing;

+-------+-------+
| k_key | value |
+-------+-------+
|     1 | TEST1 |
|     2 | TEST2 |
+-------+-------+

4. Modify tuple keys:

   UPDATE testing SET k_key = 0 WHERE k_key = 1;
   UPDATE testing SET k_key = 1 WHERE k_key = 2;

5. Check table occupancy:

   SELECT * FROM testing;

+-------+-------+
| k_key | value |
+-------+-------+
|     0 | TEST1 |
|     1 | TEST2 |
+-------+-------+

6. Insert a new column via ALTER TABLE ADD (this has been verified for a number of different column types):

  ALTER TABLE testing ADD value2 VARCHAR(10);

7. Error message returned:

  ERROR 1062: Duplicate entry '1' for key 1

Suggested fix:
Allow the addition of the new column, I suspect that this is because the handler is trying to modify the primary key.

Note, this problem does not occur with the MyISAM table handler.

Workaround, change the primary key of the tuple with key 0 to something else, add the new column, modify the key back to 0.  Note: this is not a very good solution since the auto increment value of the primary key will be set to whatever you set the value of the key to plus 1.  So the primary key should be set to max(key) + 1.  However you then "lose" that key...

It should be noted that on further examination even the use of "ALTER TABLE CHANGE" or "ALTER TABLE MODIFY" causes the error.

I now suspect that the issue is caused by the table handler re-creating the table when the alter table command is issued and so the "duplicate key" error is caused when it tries to re-populate the table...

Finally... on further testing, it is the presence of the primary key set to 0 that causes the error.

For example the following works fine:

create table testing (
k_key mediumint unsigned auto_increment not null,
value varchar(10),
primary key (k_key)) Type = InnoDB;

insert into testing set value = 'TEST1';

alter table testing add value2 varchar(10);

mysql> select * from testing;
+-------+-------+--------+
| k_key | value | value2 |
+-------+-------+--------+
|     1 | TEST1 | NULL   |
+-------+-------+--------+

A further issue is the following:

create table testing (
k_key mediumint unsigned auto_increment not null,
value varchar(10),
primary key (k_key)) Type = InnoDB;

insert into testing set value = 'TEST1';

update testing set k_key = 0 where k_key = 1;

mysql> select * from testing;
+-------+-------+--------+
| k_key | value | value2 |
+-------+-------+--------+
|     0 | TEST1 | NULL   |
+-------+-------+--------+

alter table testing add value2 varchar(10);

mysql> select * from testing;
+-------+-------+--------+
| k_key | value | value2 |
+-------+-------+--------+
|     1 | TEST1 | NULL   |
+-------+-------+--------+

Which is WRONG! (And confirms my earlier suspicions...)

The key has been changed when the new column is added!

Additional Info for to explain the obtained behavior:

4.4 How an AUTO_INCREMENT column works in InnoDB

------------

InnoDB uses the following algorithm to initialize the auto-increment counter. After a database startup, when a user the first time does an insert to a table T or calls SHOW TABLE STATUS where the table T is shown, then InnoDB executes SELECT MAX(auto-inc-column) FROM T FOR UPDATE and assigns that value incremented by one to the column and the auto-increment counter of the table. If the table is empty then the value 1 is assigned. Note that in this initialization we do a normal x-locking read on the table and the lock lasts to the end of the transaction.

InnoDB follows the same procedure in initializing the auto-increment counter for a freshly created table.

If the user specifies in an insert the value 0 to the auto-increment column, then InnoDB treats the row like the value would not have been specified and generates a new value to it.

----------------------------------------------------------------------------

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.2-alpha-nt

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

mysql> create table testing (
    -> k_key mediumint unsigned auto_increment not null,
    -> value varchar(10),
    -> primary key (k_key)) Type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.20 sec)

mysql>
mysql> INSERT INTO testing SET value = 'TEST1';
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO testing SET value = 'TEST2';
Query OK, 1 row affected (0.05 sec)

mysql>
mysql> -- we won't have new k_key == 1
mysql>
mysql> UPDATE testing SET k_key = 0 WHERE k_key = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE testing SET k_key = 3 WHERE k_key = 2;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> ALTER TABLE testing ADD value2 VARCHAR(10);
Query OK, 2 rows affected (0.40 sec)
Records: 2  Duplicates: 0  Warnings: 0

Just on this:

"If the user specifies in an insert the value 0 to the auto-increment column,
then InnoDB treats the row like the value would not have been specified and
generates a new value to it."

Is this really desirable behaviour (although I'm sure it's standard behaviour ;)?  For example in my app I am using the key 0 to be a "special value", meaning that I can be sure that the tuple with key 0 has a special meaning for the app (and it should be portable across all instances of the db, i.e. when you "dump and restore" the db, the special value is retained regardless of the dump order of the tuples).

Given the above description of how the auto_increment field will work when I restore the table I cannot guarantee the value of the key... is this correct?  Is there any wordaround for this?  The DISABLE KEYS command seems to have no impact.

I also encountered this on 4.0.17-standard on Sparc Solaris 9 with MySQL-built binaries and 4.0.20a-debug-log source distribution for Win32. But that's to be expected if this is intended behavior.

However, I don't quite understand the logic behind this behavior. For instance:

-- 0 is a valid value for an integer column (signed or unsigned)
-- I can insert any integer value > 0 into an auto_increment field and it is unaltered.
-- But specifying a 0 (a valid value for the integer column) *silently* alters the value to the next increment

Even if you accept all of this it doesn't really explain why adding a column to a table that has an auto_increment field causes a re-sequencing of the auto_increment field. *And* adding the column doesn't seem to set the existing 0 = 1 + greatest value in the table, it tries to set it 1, which seems to suggest that the entire auto_increment field is resequenced.

Anyway, I realize this is not a bug - it's just a complaint.

Hi!

MySQL performs an ALTER TABLE by creating a new table and inserting to it.

Having 0 and 1 in an auto-inc column in this case will generate a duplicate key error.

You should not store 0 to an auto-inc column.

Regards,

Heikki

Gary,

could you test again with 5.0, where you can specify an auto-inc column to contain also value 0.

Regards,

Heikki

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".