| Bug #18477 | MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE | ||
|---|---|---|---|
| Submitted: | 24 Mar 2006 6:47 | Modified: | 11 Apr 2006 4:20 |
| Reporter: | Richard Harms | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S1 (Critical) |
| Version: | 5.1.7-beta | OS: | Linux (Fedora Core 4, x86_64) |
| Assigned to: | Ingo Strüwing | Target Version: | |
[24 Mar 2006 8:24]
Heikki Tuuri
The reason is that mysql_alter_table() in sql_table.cc sets create_info->frm_only = TRUE,
which causes rea_create_table() not to call InnoDB at all.
But in the current InnoDB implementation of foreign keys it is necessary to rebuild the
whole table. During the rebuild we also check that the new foreign key constraint is
satisified by the table.
(gdb) print create_info->frm_only
$1 = true
(gdb) step
338 DBUG_RETURN(0);
(gdb)
343 } /* rea_create_table */
(gdb) bt
#0 rea_create_table (thd=0x8f63038,
path=0x4a921990 "./test/@0023sql@002d3aa2_1", db=0x8f49598 "test",
table_name=0x4a922c20 "#sql-3aa2_1", create_info=0x8f635e4,
create_fields=@0x0, keys=0, key_info=0x0, file=0x8f79c08) at unireg.cc:343
#1 0x0829b5f4 in mysql_create_table_internal (thd=0x8f63038,
db=0x8f49598 "test", table_name=0x4a922c20 "#sql-3aa2_1",
create_info=0x8f635e4, fields=@0x4a921f90, keys=@0x8f635e4,
internal_tmp_table=true, select_field_count=0) at sql_table.cc:2243
#2 0x0829baad in mysql_create_table (thd=0x8f63038, db=0x8f49598 "test",
table_name=0x0, create_info=0x0, fields=@0x0, keys=@0x0,
internal_tmp_table=true, select_field_count=0) at sql_table.cc:2320
#3 0x0829e4c8 in mysql_alter_table (thd=0x8f63038, new_db=0x8f49598 "test",
new_name=0x8f79600 "tablea", create_info=0x8f635e4, table_list=0x8f79628,
fields=@0x4a922c20, keys=@0x8f634f0, order_num=0, order=0x0,
handle_duplicates=DUP_ERROR, ignore=false, alter_info=0x8f6372c,
do_send_ok=true) at sql_table.cc:4653
#4 0x081e4c0e in mysql_execute_command (thd=0x8f63038) at sql_parse.cc:3049
#5 0x081ed0a9 in mysql_parse (thd=0x8f63038, inBuf=0x8f63074 "\020P\\\b\001",
length=111) at sql_parse.cc:5882
#6 0x081e26e1 in dispatch_command (command=150351928, thd=0x8f63038,
packet=0x8f91551 "ALTER TABLE tablea ADD CONSTRAINT ta_tb_id_fk FOREIGN KEY
(ta_tb_id) REFERENCES tableb(tb_id) ON DELETE CASCADE",
packet_length=150443352) at sql_parse.cc:1741
#7 0x081e223d in do_command (thd=0x8f63038) at sql_parse.cc:1537
#8 0x081e15b2 in handle_one_connection (arg=0x8f63038) at sql_parse.cc:1179
#9 0x4016cb63 in start_thread () from /lib/tls/libpthread.so.0
#10 0x402b318a in clone () from /lib/tls/libc.so.6
(gdb)
Fix: compare_tables() in sql_table.cc should return ALTER_TABLE_DATA_CHANGED if a new
FOREIGN KEY constraint is added in the ALTER.
[30 Mar 2006 9:26]
Ingo Strüwing
The problem is the following: The SQL layer ignores foreign key definitions except for syntax checks. It does not store foreign key information in its table structures and hence not in the .frm file. The InnoDB table handler extracts (parses) the CREATE/ALTER TABLE statement to get at the foreign key information. For SHOW CREATE TABLE the InnoDB table handler creates the textual information regarding foreign keys. The consequence is that in compare_tables() it is not possible to detect if the old table has foreign keys. Hence it cannot be detected if a foreign key is to be dropped. I could detect that the new table has to have a foreign key, but I cannot decide if this is a new one, a changed one, or the same as the in the old table. The only way out is to make use of the handler::check_if_incompatible_data() method, which is called at a last ressort at the end of compare_tables(). This method needs to be extended in ha_innodb.cc to detect foreign key changes. Since changing the InnoDB table handler is new to me, I estimate a two weeks time frame.
[30 Mar 2006 10:40]
Heikki Tuuri
Ingo, another solution: add a flag to thd->lex to denote that ALTER TABLE contained a FOREIGN KEY creation clause or a DROP FOREIGN KEY clause. If that is the case, then compare_tables() should say that the table has to be rebuilt. You have to modify the MySQL SQL parser a little. I guess this is fix requires one day of work. It was my intention to submit a patch to MySQL, but I have been busy with other bugs for the past 3 days. Regards, Heikki
[30 Mar 2006 20:29]
Ingo Strüwing
Heikkis proposal seems to work. Who ever merged the fix for Bug#16387 (InnoDB crash when dropping a foreign key <table>_ibfk_0) to 5.1 could have detected this bug earlier. The result file showed the wrong behaviour.
[30 Mar 2006 21:10]
Bugs System
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/4339
[6 Apr 2006 9:03]
Ingo Strüwing
Patch approved by Heikki Tuuri.
[8 Apr 2006 15:22]
Ingo Strüwing
InnoDB requires a full table rebuild for foreign key changes. It was not possible in compare_tables() to detect such changes. On Heikkis proposal I added a new flag to the syntax parser where foreign key definition changes are done. I test for this flag in compare_tables() now. Pushed to 5.1.10.
[11 Apr 2006 4:20]
Paul DuBois
Noted in 5.1.10 changelog. <literal>InnoDB</literal>: <literal>ALTER TABLE</literal> to add or drop a foreign key for an <literal>InnoDB</literal> table had no effect. (Bug #18477)
Description: MySQL 5.1.7 was compiled using: ./configure --with-innodb The alter table command to add the foreign key appears to be ignored by the server. How to repeat: SQL executed for each example: CREATE TABLE tablea ( ta_id BIGINT NOT NULL, ta_tb_id BIGINT NOT NULL ) ENGINE=InnoDB; CREATE TABLE tableb ( tb_id BIGINT NOT NULL ) ENGINE=InnoDB; ALTER TABLE tablea ADD PRIMARY KEY (ta_id); ALTER TABLE tablea ADD UNIQUE KEY ta_tb_id_idx (ta_tb_id); ALTER TABLE tableb ADD PRIMARY KEY (tb_id); ALTER TABLE tablea ADD CONSTRAINT ta_tb_id_fk FOREIGN KEY (ta_tb_id) REFERENCES tableb(tb_id) ON DELETE CASCADE; Incorrect behavior: [rich@dr1 ~]$ mysqladmin ver mysqladmin Ver 8.41 Distrib 5.1.7-beta, for unknown-linux-gnu on x86_64 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.1.7-beta Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 day 22 hours 7 min 29 sec Threads: 1 Questions: 40021 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 4 Queries per second avg: 0.241 [rich@dr1 ~]$ uname -a Linux dr1.darkrealms.com 2.6.15-1.1830_FC4smp #1 SMP Thu Feb 2 17:36:59 EST 2006 x86_64 x86_64 x86_64 GNU/Linux mysql> show create table tablea; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tablea | CREATE TABLE `tablea` ( `ta_id` bigint(20) NOT NULL, `ta_tb_id` bigint(20) NOT NULL, PRIMARY KEY (`ta_id`), UNIQUE KEY `ta_tb_id_idx` (`ta_tb_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into tablea value (1,1); Query OK, 1 row affected (0.01 sec) mysql> select * from tablea; +-------+----------+ | ta_id | ta_tb_id | +-------+----------+ | 1 | 1 | +-------+----------+ 1 row in set (0.00 sec) Correct behavior: [rich@dr1 ~]$ mysqladmin ver mysqladmin Ver 8.41 Distrib 5.0.19, for unknown-linux-gnu on x86_64 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.19 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 51 sec Threads: 1 Questions: 13 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 7 Queries per second avg: 0.255 mysql> show create table tablea; +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tablea | CREATE TABLE `tablea` ( `ta_id` bigint(20) NOT NULL, `ta_tb_id` bigint(20) NOT NULL, PRIMARY KEY (`ta_id`), UNIQUE KEY `ta_tb_id_idx` (`ta_tb_id`), CONSTRAINT `ta_tb_id_fk` FOREIGN KEY (`ta_tb_id`) REFERENCES `tableb` (`tb_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into tablea value (1,1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/tablea`, CONSTRAINT `ta_tb_id_fk` FOREIGN KEY (`ta_tb_id`) REFERENCES `tableb` (`tb_id`) ON DELETE CASCADE)