MySQL Bugs: #16387: InnoDB crash when dropping a foreign key <table>_ibfk

Bug #16387	InnoDB crash when dropping a foreign key <table>_ibfk_0
Submitted:	11 Jan 2006 20:52	Modified:	17 Jun 2010 15:44
Reporter:	Viktor Voroshylo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.17/5.0.19 BK/4.1.17 BK	OS:	FreeBSD (FreeBSD/Suse Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
Version: '5.0.17'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port: mysql-server-5.0.17

060111 15:57:34InnoDB: Assertion failure in thread 163656192 in file dict0dict.c line 2812
InnoDB: Failing assertion: id != biggest_id
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

How to repeat:
**** last statement will crash server ****

CREATE TABLE a1 ( a1_key int, primary key (a1_key) ) engine=InnoDB;

CREATE TABLE a2 ( a1_key int ) engine=InnoDB;

ALTER TABLE `a2` ADD FOREIGN KEY (`a1_key`) REFERENCES `a1` (`a1_key`);

ALTER TABLE a2 
  ADD CONSTRAINT   `a2_ibfk_0` FOREIGN KEY (`a1_key`) REFERENCES `a1` (`a1_key`),
  DROP FOREIGN KEY `a2_ibfk_1`;

ALTER TABLE a2 DROP FOREIGN KEY `a2_ibfk_0`;

Thank you for the bug report I was able to repeat:

030411 19:17:25 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.19-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 7458)]
[Thread 1131862960 (zombie) exited]
[New Thread 1131862960 (LWP 7460)]
030411 19:18:41InnoDB: Assertion failure in thread 1131862960 in file dict0dict.c line 2812
InnoDB: Failing assertion: id != biggest_id
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 7460)]
0x08423487 in dict_table_get_highest_foreign_id (table=0x40399468) at dict0dict.c:2812
2812                                    ut_a(id != biggest_id);
Current language:  auto; currently c
(gdb) 

/home/miguel/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.17-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1138224048 (LWP 7480)]
[Thread 1138224048 (zombie) exited]
[New Thread 1138224048 (LWP 7482)]
[Thread 1138224048 (zombie) exited]
[New Thread 1138224048 (LWP 7484)]
030411 19:22:25InnoDB: Assertion failure in thread 1138224048 in file dict0dict.c line 2764
InnoDB: Failing assertion: id != biggest_id
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1138224048 (LWP 7484)]
0x082d9567 in dict_table_get_highest_foreign_id (table=0x41228c68) at dict0dict.c:2764
2764                                    ut_a(id != biggest_id);
Current language:  auto; currently c
(gdb)

Eek, InnoDB does not support adding and dropping a constraint in the same ALTER TABLE. We should block it explicitly.

If you add and drop the constraint in separate ALTER TABLEs, then I assume it does not crash?

Regards,

Heikki

Mysqlfront 3.2 (build 8.3) creating this statement when I rename constraint.
Moreover this happends only if I do not rename index that is used by constraint. If  rename index before renaming constraint it works fine.

I patched the parser and mysql_execute_command() in order to disable the simultaneous DROP FOREIGN KEY and ADD FOREIGN KEY in the same ALTER TABLE statement. However, the crash still occurs in 4.1 with the following commands:

CREATE TABLE a1 ( a1_key int, primary key (a1_key) ) engine=InnoDB;
CREATE TABLE a2 ( a1_key int ) engine=InnoDB;
ALTER TABLE `a2` ADD FOREIGN KEY (`a1_key`) REFERENCES `a1` (`a1_key`);
ALTER TABLE a2 DROP FOREIGN KEY `a2_ibfk_1`;
ALTER TABLE a2 ADD CONSTRAINT   `a2_ibfk_0`
FOREIGN KEY (`a1_key`) REFERENCES `a1`(`a1_key`);

ALTER TABLE a2 DROP FOREIGN KEY `a2_ibfk_0`;

Right before the last ALTER TABLE, SHOW CREATE TABLE a2 shows the following:
CREATE TABLE `a2` (
  `a1_key` int(11) default NULL,
  KEY `a2_ibfk_0` (`a1_key`),
  CONSTRAINT `a2_ibfk_0` FOREIGN KEY (`a1_key`) REFERENCES `a1` (`a1_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I guess that the problem is that a2_ibfk_0 refers to both a key and a constraint.

Marko

Here is a yet simpler test case:

CREATE TABLE a1 ( a1_key int, primary key (a1_key) ) engine=InnoDB;
CREATE TABLE `a2` (
  `a1_key` int(11) default NULL,
  KEY `a2_ibfk_0` (`a1_key`),
  CONSTRAINT `a2_ibfk_0` FOREIGN KEY (`a1_key`) REFERENCES `a1` (`a1_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE a2 DROP FOREIGN KEY `a2_ibfk_0`;

The problem appears to be that InnoDB mistakes constraints of the form <tablename>_ibfk_0 as generated ones, while the generated constraints always are suffixed with a positive (nonzero) number. The bug is duplicated in dict_table_rename_in_cache().

The failing assertion ut_a(id != biggest_id) in dict0dict.c can be replaced with ut_a(!id || id != biggest_id).

I will investigate if it is still possible to get an assertion failure with foreign key constraint names whose suffix differs by leading zeroes, like <tablename>_ibfk_001 and <tablename>_ibfk_1. Perhaps we should make sure that the character following _ibfk_ is not '0', in case strtoul() implementations differ in the treatment of leading zeroes.

Constraints or keys whose name start with <tablename>_ibfk_ are treated specially in RENAME TABLE. Consider the following test case:

CREATE TABLE a1(a1_key int, primary key(a1_key)) engine=InnoDB;
CREATE TABLE a2(a1_key INT,CONSTRAINT a2_ibfk_snafu FOREIGN KEY(a1_key)REFERENCES a1(a1_key))ENGINE=InnoDB;
RENAME TABLE a2 TO a3;
SHOW CREATE TABLE a3;

Observe that the user-specified constraint name a2_ibfk_snafu has been renamed to a3_ibfk_snafu! Foreign key constraints whose names do not begin with <table_name>_ibfk_ will not be renamed.

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/1855

Fixed in 4.1.19

Fixed in 5.0.19.
Patch: http://lists.mysql.com/commits/1958.

Documented in 4.1.19 and 5.0.19 changelogs:

 <listitem>
        <para>Server crash when dropping InnoDB constraints named
          <literal><replaceable>TABLENAME</replaceable>_ibfk_0</literal>.
        (Bug #16387)</para>
      </listitem>

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)