| Bug #38771 | Foreign key not works when altering table to auto_increment | ||
|---|---|---|---|
| Submitted: | 13 Aug 2008 13:47 | Modified: | 1 Feb 2013 5:25 |
| Reporter: | Adam Brunner | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.51a-9+lenny2, 5.0.66a/5.1/6.0 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key alter table auto_increment | ||
[13 Aug 2008 17:12]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.66a:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE mytemp0 (
-> id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> name VARCHAR(10) NOT NULL,
-> PRIMARY KEY(id)
-> ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin;
Query OK, 0 rows affected (0.28 sec)
mysql>
mysql> INSERT INTO mytemp0 (name) VALUE('myname0');
Query OK, 1 row affected (0.08 sec)
mysql>
mysql> SELECT * FROM mytemp0;
+----+---------+
| id | name |
+----+---------+
| 1 | myname0 |
+----+---------+
1 row in set (0.00 sec)
mysql> CREATE TABLE mytemp1 (
-> id TINYINT UNSIGNED NOT NULL,
-> name VARCHAR(10) NOT NULL,
-> PRIMARY KEY(id)
-> ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO mytemp1 (name) VALUE('myname1');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> INSERT INTO mytemp1 VALUES(0, 'myname1');
Query OK, 1 row affected (0.39 sec)
mysql> SELECT * FROM mytemp1;
+----+---------+
| id | name |
+----+---------+
| 0 | myname1 |
+----+---------+
1 row in set (0.00 sec)
mysql> CREATE TABLE mytemp2 (
-> mytemp0_id TINYINT UNSIGNED NOT NULL,
-> mytemp1_id TINYINT UNSIGNED NOT NULL,
-> name VARCHAR(10) NOT NULL,
-> CONSTRAINT fk_mytemp2_mytemp0_id FOREIGN KEY (mytemp0_id) REFERENCES
mytemp0(id) ON
-> DELETE CASCADE ON UPDATE CASCADE,
-> CONSTRAINT fk_mytemp2_mytemp1_id FOREIGN KEY (mytemp1_id) REFERENCES
mytemp1(id) ON
-> DELETE CASCADE ON UPDATE CASCADE
-> ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin;
Query OK, 0 rows affected (0.41 sec)
mysql> INSERT INTO mytemp2 (mytemp0_id, mytemp1_id, name) VALUE(1, 0, 'myname2')
;
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM mytemp2;
+------------+------------+---------+
| mytemp0_id | mytemp1_id | name |
+------------+------------+---------+
| 1 | 0 | myname2 |
+------------+------------+---------+
1 row in set (0.00 sec)
mysql> ALTER TABLE mytemp1 MODIFY COLUMN id TINYINT UNSIGNED NOT NULL AUTO_IN
CREMENT;
Query OK, 1 row affected (0.41 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM mytemp1;
+----+---------+
| id | name |
+----+---------+
| 1 | myname1 |
+----+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytemp2;
+------------+------------+---------+
| mytemp0_id | mytemp1_id | name |
+------------+------------+---------+
| 1 | 0 | myname2 |
+------------+------------+---------+
1 row in set (0.00 sec)
[20 Sep 2008 8:36]
Mattias Jonsson
The problem is the alter, it should not change any row content. I think it is a server bug which might be fixed with:
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc 2008-09-15 09:19:56 +0000
+++ sql/sql_table.cc 2008-09-20 08:30:59 +0000
@@ -7207,8 +7207,7 @@
This condition also covers case when we are don't actually alter
auto_increment column.
*/
- if (def->field == from->found_next_number_field)
- thd->variables.sql_mode|= MODE_NO_AUTO_VALUE_ON_ZERO;
+ thd->variables.sql_mode|= MODE_NO_AUTO_VALUE_ON_ZERO;
}
(copy_end++)->set(*ptr,def->field,0);
}
[1 Feb 2013 5:25]
Dmitry Lenev
Hello! This bug has the same reason as bug #46599 "ALTER TABLE causes inconsistent values for foreign keys" and was fixed in MySQL server version 5.6.7 by the same patch. I am closing this bug-report as a duplicate of #46599.
Description: When altering a field to AUTO_INCREMENT, constraint fields not cascade. How to repeat: CREATE TABLE mytemp0 ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(10) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO mytemp0 (name) VALUE('myname0'); SELECT * FROM mytemp0; +----+---------+ | id | name | +----+---------+ | 1 | myname0 | +----+---------+ CREATE TABLE mytemp1 ( id TINYINT UNSIGNED NOT NULL, name VARCHAR(10) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO mytemp1 (name) VALUE('myname1'); SELECT * FROM mytemp1; +----+---------+ | id | name | +----+---------+ | 0 | myname1 | +----+---------+ CREATE TABLE mytemp2 ( mytemp0_id TINYINT UNSIGNED NOT NULL, mytemp1_id TINYINT UNSIGNED NOT NULL, name VARCHAR(10) NOT NULL, CONSTRAINT fk_mytemp2_mytemp0_id FOREIGN KEY (mytemp0_id) REFERENCES mytemp0(id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_mytemp2_mytemp1_id FOREIGN KEY (mytemp1_id) REFERENCES mytemp1(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO mytemp2 (mytemp0_id, mytemp1_id, name) VALUE(1, 0, 'myname2'); SELECT * FROM mytemp2; +------------+------------+---------+ | mytemp0_id | mytemp1_id | name | +------------+------------+---------+ | 1 | 0 | myname2 | +------------+------------+---------+ ALTER TABLE mytemp1 MODIFY COLUMN id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT; SELECT * FROM mytemp1; +----+---------+ | id | name | +----+---------+ | 1 | myname1 | +----+---------+ SELECT * FROM mytemp2; +------------+------------+---------+ | mytemp0_id | mytemp1_id | name | +------------+------------+---------+ | 1 | 0 | myname2 | +------------+------------+---------+ Suggested fix: Should be field 'mytemp1_id' value 1.