MySQL Bugs: #29624: ALTER TABLE with AUTO_INCREMENT does not respect foreign key constraints

Bug #29624	ALTER TABLE with AUTO_INCREMENT does not respect foreign key constraints
Submitted:	8 Jul 2007 2:08	Modified:	17 Jul 2007 19:30
Reporter:	Vincent Huisman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.46-BK,5.1.20-beta/5.1.11-beta/4.0.27/?	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I'm not sure, but I think this bug happens on any version. I first encountered it on 4.0.27 and then verified it on 5.1.11-beta, and because I figured the newest bugfixes would be in the betas, I tried 5.1.20-beta and got the same result. I marked it S3 because I don't think it'll happen often in real-life situations, which makes it less critical.

Basically, after creating tables with a foreign key, changes in the first table should cascade to the second table. This works just fine. However, when I alter the first table and add AUTO_INCREMENT on the PK, which is the referenced FK, rows may be updated (0 changes to 1 because AUTO_INCREMENT wants that to happen). However, the change is not cascaded to the second table, invalidating the dataset/constraints. Adding the same information as which is present in one of the rows returns an error 'Cannot add or update a child row: a foreign key constraint fails', which shows that the current data is supposedly invalid.

How to repeat:
mysql> CREATE TABLE cash_groups_test (
    ->   id int(11) NOT NULL,
    ->   parent int(11) NOT NULL default '0',
    ->   name varchar(255) NOT NULL default '',
    ->   PRIMARY KEY (id) ,
    ->   INDEX indparent (parent)
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO cash_groups_test VALUES (0, 0, 'Main'), (2, 0, 'Child');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE cash_entries_test (
    ->   id int(11) NOT NULL AUTO_INCREMENT ,
    ->   parent int(11) NOT NULL ,
    ->   date int(11) NOT NULL ,
    ->   amount int(11) NOT NULL ,
    ->   description varchar(255) NOT NULL ,
    ->   PRIMARY KEY (id) ,
    ->   INDEX indparent (parent) ,
    ->   FOREIGN KEY (parent) REFERENCES cash_groups_test(id) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) TYPE = innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO cash_entries_test VALUES (0, 0, 0, 0, 'entry 1'), (0, 2, 0, 0, 'entry 2'), (0, 0, 0, 0, 'entry 3'), (0, 2, 0, 0, 'entry 4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM cash_groups_test;
+----+--------+-------+
| id | parent | name  |
+----+--------+-------+
|  0 |      0 | Main  |
|  2 |      0 | Child |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM cash_entries_test;
+----+--------+------+--------+-------------+
| id | parent | date | amount | description |
+----+--------+------+--------+-------------+
|  1 |      0 |    0 |      0 | entry 1     |
|  2 |      2 |    0 |      0 | entry 2     |
|  3 |      0 |    0 |      0 | entry 3     |
|  4 |      2 |    0 |      0 | entry 4     |
+----+--------+------+--------+-------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE cash_groups_test CHANGE id id INT(11) NOT NULL AUTO_INCREMENT;
Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM cash_groups_test;
+----+--------+-------+
| id | parent | name  |
+----+--------+-------+
|  1 |      0 | Main  |
|  2 |      0 | Child |
+----+--------+-------+
2 rows in set (0.00 sec)

********************************************************
As you can see here, the id for 'Main', which used to be 0, changed into 1.
********************************************************

mysql> SELECT * FROM cash_entries_test;
+----+--------+------+--------+-------------+
| id | parent | date | amount | description |
+----+--------+------+--------+-------------+
|  1 |      0 |    0 |      0 | entry 1     |
|  2 |      2 |    0 |      0 | entry 2     |
|  3 |      0 |    0 |      0 | entry 3     |
|  4 |      2 |    0 |      0 | entry 4     |
+----+--------+------+--------+-------------+
4 rows in set (0.00 sec)

********************************************************
The change wasn't reflected in this table
********************************************************

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

mysql> SELECT * FROM cash_entries_test;
+----+--------+------+--------+-------------+
| id | parent | date | amount | description |
+----+--------+------+--------+-------------+
|  1 |      0 |    0 |      0 | entry 1     |
|  2 |      3 |    0 |      0 | entry 2     |
|  3 |      0 |    0 |      0 | entry 3     |
|  4 |      3 |    0 |      0 | entry 4     |
+----+--------+------+--------+-------------+
4 rows in set (0.00 sec)

********************************************************
And as we can see here, the foreign key works properly so it should have been updated.
********************************************************

mysql> INSERT INTO cash_entries_test VALUES (0, 0, 0, 0, 'entry 5');
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

********************************************************
Attempt to insert the same information as which was present in another row: error. Apparently the data which is in the table shouldn't have been there in the first place.
********************************************************

mysql> UPDATE cash_groups_test SET id=4 WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM cash_entries_test;
+----+--------+------+--------+-------------+
| id | parent | date | amount | description |
+----+--------+------+--------+-------------+
|  1 |      0 |    0 |      0 | entry 1     |
|  2 |      3 |    0 |      0 | entry 2     |
|  3 |      0 |    0 |      0 | entry 3     |
|  4 |      3 |    0 |      0 | entry 4     |
+----+--------+------+--------+-------------+
4 rows in set (0.00 sec)

********************************************************
Works fine, shows that the IDs are really not linked anymore.

Copy-paste-able:

DROP TABLE IF EXISTS cash_entries_test;
DROP TABLE IF EXISTS cash_groups_test;
CREATE TABLE cash_groups_test (
  id int(11) NOT NULL,
  parent int(11) NOT NULL default '0',
  name varchar(255) NOT NULL default '',
  PRIMARY KEY (id) ,
  INDEX indparent (parent)
) TYPE=InnoDB;
INSERT INTO cash_groups_test VALUES (0, 0, 'Main'), (2, 0, 'Child');
CREATE TABLE cash_entries_test (
  id int(11) NOT NULL AUTO_INCREMENT ,
  parent int(11) NOT NULL ,
  date int(11) NOT NULL ,
  amount int(11) NOT NULL ,
  description varchar(255) NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX indparent (parent) ,
  FOREIGN KEY (parent) REFERENCES cash_groups_test(id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = innodb;
INSERT INTO cash_entries_test VALUES (0, 0, 0, 0, 'entry 1'), (0, 2, 0, 0, 'entry 2'), (0, 0, 0, 0, 'entry 3'), (0, 2, 0, 0, 'entry 4');
SELECT * FROM cash_groups_test;
SELECT * FROM cash_entries_test;
ALTER TABLE cash_groups_test CHANGE id id INT(11) NOT NULL AUTO_INCREMENT;
SELECT * FROM cash_groups_test;
SELECT * FROM cash_entries_test;
UPDATE cash_groups_test SET id=3 WHERE id=2;
SELECT * FROM cash_entries_test;
INSERT INTO cash_entries_test VALUES (0, 0, 0, 0, 'entry 5');
UPDATE cash_groups_test SET id=4 WHERE id=1;
SELECT * FROM cash_entries_test;

Suggested fix:
ALTERing a table, adding an AUTO_INCREMENT obviously updates a row. Suggested fix is that the foreign key 'on update' is actually checked and the change is properly cascaded, as with normal updates.

Thank you for a bug report. Verified just as described with latest 5.0.46-BK on Linux.

I think, ALTER TABLE should just not change 0 to anything for existing rows. But if it does, then change should indeed be apllied to correspondent rows based on foreign key constraint.

The bug is due to the fact that InnoDB does not check if ALTER TABLE changes the values of some existing column. A similar bug occurs if you modify the column type so that MySQL has to 'truncate' the values in it. Fortunately, SQL mode = traditional nowadays blocks that other bug.

The behavior where making a colum auto-inc changes the values of the column is dubious. It would be more logical that MySQL would keep the value 0, so that this bug would not occur.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30612

ChangeSet@1.2527, 2007-07-10 17:57:10+03:00, gkodinov@magare.gmz +3 -0
  Bug #29624: ALTER TABLE with AUTO_INCREMENT does not respect 
   foreign key constraints
  
  ALTER TABLE may create a temporary table; copy the data 
  from the original table and then redirect the original table
  to the temp table.
  It copies the data using the internal equivalent of 
  INSERT ... SELECT ...
  However INSERT into an AUTO_INCREMENT field treats 0 and NULL
  as "magic" values and substitutes them with the next 
  auto-increment value.
  This is a welcome effect if the AUTO_INCREMENT field is newly
  added. But this substitution should not be done if the field 
  that has the AUTO_INCREMENT flag in the modified table existed 
  in the original table.
  Fixed by disabling the substitution of magic values when 
  copying the data in ALTER TABLE for columns that
  are not newly added.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30712

ChangeSet@1.2527, 2007-07-11 18:20:07+03:00, gkodinov@magare.gmz +3 -0
  Bug #29624: ALTER TABLE with AUTO_INCREMENT does not respect 
   foreign key constraints
  
  ALTER TABLE may create a temporary table; copy the data 
  from the original table and then redirect the original table
  to the temp table.
  It copies the data using the internal equivalent of 
  INSERT ... SELECT ...
  However INSERT into an AUTO_INCREMENT field treats 0 and NULL
  as "magic" values and substitutes them with the next 
  auto-increment value.
  This is a welcome effect if the AUTO_INCREMENT field is newly
  added. But this substitution should not be done if the field 
  that has the AUTO_INCREMENT flag in the modified table existed 
  in the original table.
  Fixed by disabling the substitution of magic values when 
  copying the data in ALTER TABLE for columns that
  are not newly added.

There are many data modifications that can happen during ALTER TABLE. Auto_increment and truncation are examples. Others are - character set conversions, extending (not truncating) BINARY column (binary columns are '\0'-padded, and zero bytes are not stripped on comparison), changing NULL column to NOT NULL, etc.

It's InnoDB bug (or undocumented limitation), that it doesn't expect these data modifications, and they break foreign key constraints.

This has to be documented both in MySQL's ALTER TABLE and InnoDB's FOREIGN KEY sections in the manual.

Paul, please add there:

"If ALTER TABLE changes column values, for example, because a column is truncated, InnoDB's FOREIGN KEY contraint checks do not notice possible violations caused by changing the values."

This behavior itself is a bug, of course. When/if MySQL gets its own foreign key constraints, this becomes fixed.

I have added the notes requested by Heikki.

Returning this report to NDI status because no version numbers were given for the patch that was made. (Or perhaps the status really should be Patch pending or queued?)

No patch was applied. Closing this report.

If You  have table with auto increment and  You want to  add  field at  begin  with id ? At that no need  to  drop table  simply follow the four  step ?

=>  Remove  the  Auto incremnt  Of  that  field  
=> update  All  ids with  increment  of  1  in descending  order 
=>  insert your  recode  with your desire id.
=>  and  add  your field  auto increment.