| Bug #55084 | Innodb crash and corruption after alter table | ||
|---|---|---|---|
| Submitted: | 8 Jul 2010 14:33 | Modified: | 29 Apr 2011 21:08 |
| Reporter: | Teste Silva | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S1 (Critical) |
| Version: | 1.0.8, 5.1.49 | OS: | Linux (rhel 5.5 64bit) |
| Assigned to: | Jimmy Yang | CPU Architecture: | Any |
| Tags: | innodb, regression | ||
[8 Jul 2010 14:33]
Teste Silva
[8 Jul 2010 16:51]
Valeriy Kravchuk
Looks like this happens during new index creation and may be related to bug #44571 and other related bug reports. It would be nice to get a crashing statement.
[8 Aug 2010 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[8 Sep 2010 18:30]
martin fuxa
Assertion failure in thread ... in file dict/dict0dict.c line 4854; InnoDB: Failing assertion: new_index
Attachment: t2029.log (text/x-log), 6.48 KiB.
[8 Sep 2010 18:32]
martin fuxa
Same problem, see attached log. cat /etc/redhat-release CentOS release 4.8 (Final) uname --all Linux freya 2.6.9-89.0.25.ELsmp #1 SMP Thu May 6 12:33:40 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux Server version 5.1.49 with bin-log
[10 Sep 2010 21:28]
Sveta Smirnova
Thank you for the feedback. From binary log is not clear which query failed: latest one is UPDATE while trace shows it should be ALTER. Please try to identify which query causes crash: probably you have core file or general log enabled?
[1 Oct 2010 23:13]
martin fuxa
Havn't :( But maybe it's related to #21395 query may be ALTER TABLE ... DROP INDEX ... on foreign key. I tried repeat this query on 5.1.49 and 5.1.50 at i686 - not same 64 bit - without crash.
[4 Nov 2010 1:57]
Yasufumi Kinoshita
The following places may be able to cause race condition about table->foreign_list, because it seems not to obtain (or allow not to own) dict_sys->mutex.
And the race condition can make the ALTER TABLE work based on wrong judgement?
foreign_key_column_is_being_renamed():
===============
/* check whether there are foreign keys at all */
if (UT_LIST_GET_LEN(prebuilt->table->foreign_list) == 0
&& UT_LIST_GET_LEN(prebuilt->table->referenced_list) == 0) {
===============
row_ins_index_entry():
===============
if (foreign && UT_LIST_GET_FIRST(index->table->foreign_list)) {
err = row_ins_check_foreign_constraints(index->table, index,
===============
row_ins_check_foreign_constraints():
===============
foreign = UT_LIST_GET_FIRST(table->foreign_list);
while (foreign) {
===============
[9 Nov 2010 3:52]
Jimmy Yang
For assertion on ut_a(new_index), the only caller in dict0dict.c is by dict_table_replace_index_in_foreign_list() when we drop an index:
ha_innobase::final_drop_index()->row_merge_drop_index()->dict_table_replace_index_in_foreign_list().
Since for foreign key constraint, we must have index on referenced and referencing key columns, so when we drop an index involving the foreign keys, it must find an alternative. Otherwise, such index cannot be dropped. In dict_table_replace_index_in_foreign_list(), it calls dict_foreign_find_equiv_index() to find a alternative index to substitute the role of the dropping index.
The reason it asserts there must be such index is due to dict_foreign_find_equiv_index() had already been called in ha_innobase::prepare_drop_index():
ha_innobase::prepare_drop_index->dict_foreign_find_equiv_index()
if it cannot find an alternative index, it will return HA_ERR_DROP_INDEX_FK error.
And between ha_innobase::prepare_drop_index and ha_innobase::final_drop_index, the table is locked in MySQL level by metadata locks or LOCK_open. So there is no other "alter table" or rename can come in to change table definition.
As such, two consecutive calls to dict_foreign_find_equiv_index() must return identical result. If the first succeeds, the second in dict_table_replace_index_in_foreign_list() must succeed. So that is why the assertion ut_a(index) in dict_table_replace_index_in_foreign_list().
However, one thing to notice is that the check in ha_innobase::prepare_drop_index is only done if @@foreign_key_checks is on. If someone turn off @@foreign_key_checks, then the first call to dict_foreign_find_equiv_index() is skipped, and we could well trigger the error:
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
mysql> alter table child drop index idx5;
Query OK, 0 rows affected (3 min 29.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
dict_table_replace_index_in_foreign_list (table=0x9015b28, index=0x902dfd0)
at /home/jy/work/mysql5.5_7/mysql-trunk-innodb/storage/innobase/dict/dict0dict.c:4825
4825 ut_a(new_index);
(gdb) p new_index
$17 = (dict_index_t *) 0x0
(gdb) n
101108 18:47:06 InnoDB: Assertion failure in thread 2999352176 in file /home/jy/work/mysql5.5_7/mysql-trunk-innodb/storage/innobase/dict/dict0dict.c line 4825
InnoDB: Failing assertion: new_index
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/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
Program received signal SIGABRT, Aborted.
0x00333422 in __kernel_vsyscall ()
[9 Nov 2010 6:47]
Jimmy Yang
we will first fix above assertion in the case of foreign_key_checks is set to 0. For foreign_key_checks = 1, we still need to see if it repros. As mentioned above, dict_foreign_find_equiv_index( has been called twice, one in ha_innobase::prepare_drop_index and one in ha_innobase::final_drop_index. In between, MySQL has the dictionary locked, so all other DDLs (alter, drop/create etc.) will be blocked. So both dict_foreign_find_equiv_index() calls should give the same result.
[18 Nov 2010 16:11]
MySQL Verification Team
This seems fixed in 5.1.51 as I can't recreate using reproducible case. on 5.1.49
[18 Nov 2010 16:26]
MySQL Verification Team
nevermind my last comment, mistake on my part, it's present as tested in 5.1.52 pluggin, doesn't happen with built in innodb.
[15 Dec 2010 6:44]
Jimmy Yang
Shannon, for the case you described (InnoDB: Failing assertion: new_index), it must not have the fix, since that assertion is changed along with the fix.
This fix is checked into mysql-5.1-innodb plugin on Nov 10th. So I am not sure if your server have the fix. Please check the innodb_plugin/ChangeLog:
2010-11-10 The InnoDB Team
* dict/dict0dict.c, handler/handler0alter.cc, include/dict0dict.h
row/row0merge.c:
Fix Bug#55084 InnoDB crash and corruption after ALTER TABLE
In any case, the fix fixes following scenario:
mysql> CREATE TABLE parent (id INT NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE child (id INT, parent_id INT,
-> INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id) REFERENCES parent(id)
-> ON DELETE CASCADE
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table child drop index par_ind;
=>assertion failure
In short, if user turns off "foreign_key_checks" (to 0) before drop an index needed by the foreign key constraint (and without an alternative index for the foreign key constraint. And then an assert (on new_index) will be triggered.
So above case is resolved. And John, please go ahead for the documentation.
[29 Apr 2011 21:08]
John Russell
Added to 5.1.54 changelog: Dropping an InnoDB index used by a foreign key constraint, while foreign_key_checks was set to 0, could cause the server to crash with an assertion. This operation now does not cause a crash. The foreign key constraint can no longer be enforced once the associated index is removed, so do not rely on it for referential integrity in this case.
