MySQL Bugs: #18477: MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE

Bug #18477	MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
Submitted:	24 Mar 2006 5:47	Modified:	11 Apr 2006 2:20
Reporter:	Richard Harms	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.7-beta	OS:	Linux (Fedora Core 4, x86_64)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

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)

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.

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.

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

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.

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

Patch approved by Heikki Tuuri.

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.

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)