Bug #83799 InnoDB: Assertion failure in thread x in file handler0alter.cc line 5817

Submitted: 14 Nov 2016 5:50 Modified: 14 Nov 2016 6:53
Reporter: Roel Van de Paar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.34, 5.7.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: debug

[14 Nov 2016 5:50] Roel Van de Paar 
Description:
Version: '5.6.34-debug'  socket: '/sda/MS081116-mysql-5.6.34-linux-x86_64-debug/socket.sock'  port: 11652  MySQL Community Server (GPL)
2016-11-14 16:24:18 29583 [ERROR] InnoDB: dict_load_foreigns() returned 38 for alter table t2 add constraint b foreign key(b)references t2(b)
2016-11-14 16:24:18 7f34ae1ef700  InnoDB: Assertion failure in thread 139864236291840 in file handler0alter.cc line 5817
InnoDB: Failing assertion: 0
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.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:24:18 UTC - mysqld got signal 6 ;

(gdb) bt
#0  0x00007f34adbfa741 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1  0x0000000000a9c852 in my_write_core (sig=6) at /git/mysql-server_dbg/mysys/stacktrace.c:422
#2  0x000000000072c554 in handle_fatal_signal (sig=6) at /git/mysql-server_dbg/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f34ac1995f7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#5  0x00007f34ac19ace8 in __GI_abort () at abort.c:90
#6  0x0000000000b939fd in ha_innobase::commit_inplace_alter_table (this=0x7f3478c98410, altered_table=0x7f3478d55e00, ha_alter_info=0x7f34ae1eba00, commit=true)
    at /git/mysql-server_dbg/storage/innobase/handler/handler0alter.cc:5817
#7  0x0000000000645619 in handler::ha_commit_inplace_alter_table (this=0x7f3478c98410, altered_table=0x7f3478d55e00, ha_alter_info=0x7f34ae1eba00, commit=true)
    at /git/mysql-server_dbg/sql/handler.cc:4387
#8  0x00000000008481e2 in mysql_inplace_alter_table (thd=0x7f3485387000, table_list=0x7f3478c1f140, table=0x7f3478c51600, altered_table=0x7f3478d55e00,
    ha_alter_info=0x7f34ae1eba00, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f34ae1eb450, alter_ctx=0x7f34ae1ebf60)
    at /git/mysql-server_dbg/sql/sql_table.cc:6685
#9  0x000000000084c48a in mysql_alter_table (thd=0x7f3485387000, new_db=0x7f3478c1f6a0 "test", new_name=0x0, create_info=0x7f34ae1ecdf0, table_list=0x7f3478c1f140,
    alter_info=0x7f34ae1ecd60, order_num=0, order=0x0, ignore=false) at /git/mysql-server_dbg/sql/sql_table.cc:8511
#10 0x000000000098a9d7 in Sql_cmd_alter_table::execute (this=0x7f3478c1f898, thd=0x7f3485387000) at /git/mysql-server_dbg/sql/sql_alter.cc:313
#11 0x00000000007df928 in mysql_execute_command (thd=0x7f3485387000) at /git/mysql-server_dbg/sql/sql_parse.cc:4975
#12 0x00000000007e2d7e in mysql_parse (thd=0x7f3485387000, rawbuf=0x7f3478c1f010 "alter table t2 add constraint b foreign key(b)references t2(b)", length=62,
    parser_state=0x7f34ae1ee5a0) at /git/mysql-server_dbg/sql/sql_parse.cc:6385
#13 0x00000000007d5faf in dispatch_command (command=COM_QUERY, thd=0x7f3485387000, packet=0x7f348536b001 "alter table t2 add constraint b foreign key(b)references t2(b)",
    packet_length=62) at /git/mysql-server_dbg/sql/sql_parse.cc:1339
#14 0x00000000007d5016 in do_command (thd=0x7f3485387000) at /git/mysql-server_dbg/sql/sql_parse.cc:1036
#15 0x000000000079c9a5 in do_handle_one_connection (thd_arg=0x7f3485387000) at /git/mysql-server_dbg/sql/sql_connect.cc:982
#16 0x000000000079c48e in handle_one_connection (arg=0x7f3485387000) at /git/mysql-server_dbg/sql/sql_connect.cc:898
#17 0x0000000000ae8ac9 in pfs_spawn_thread (arg=0x7f3490f896a0) at /git/mysql-server_dbg/storage/perfschema/pfs.cc:1860
#18 0x00007f34adbf5dc5 in start_thread (arg=0x7f34ae1ef700) at pthread_create.c:308
#19 0x00007f34ac25aced in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
SET DEBUG='+d,innodb_test_open_ref_fail';
SET @@SESSION.foreign_key_checks=0;
CREATE TABLE t2(b INT,c INT);
alter table t2 add constraint b foreign key(b)references t2(b);
[14 Nov 2016 5:51] Roel Van de Paar 
code in storage/innobase/handler/handler0alter.cc (from 5.7);

              /* Test the case when referenced_table failed to
                open, if trx->check_foreigns is not set, we should
                still be able to add the foreign key */
                DBUG_EXECUTE_IF("innodb_test_open_ref_fail",
                                referenced_table = NULL;);

and as SESSION.foreign_key_checks=0 it seems it should still be able to add the foreign key [but perhaps not when it's on the same table?]
[14 Nov 2016 5:55] Roel Van de Paar 
ref bug 76445
[14 Nov 2016 6:53] MySQL Verification Team 
Hello Roel,

Thank you for the report and test case.
Observed that 5.6.34/5.7.16 debug builds are affected.

Thanks,
Umesh
[10 Oct 2017 16:51] MySQL Verification Team 
https://bugs.mysql.com/bug.php?id=88046 marked as duplicate of this one.
[23 Aug 2020 13:09] lalit Choudhary 
Few more test cases for 5.6.49, not reproducible with latest 5.7.31 

--------------
MySQL 5.6.49
--------------
Case1:

crash whiling adding column, 

drop database test; create database test; use test;
CREATE TABLE t1 (a INT, KEY(a), UNIQUE(a), FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS= OFF ;
ALTER TABLE t1 DROP INDEX a;
ALTER TABLE t1 ADD COLUMN v INT;

mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD COLUMN v INT;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql [localhost] {msandbox} (test) > 

mysql log:
Version: '5.6.49'  socket: '/tmp/mysql_sandbox5649.sock'  port: 5649  MySQL Community Server (GPL)
2020-08-23 18:23:00 28872 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 ADD COLUMN v INT

Case2:adding index,

drop database test1; create database test1; use test1;
CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
SET SESSION FOREIGN_KEY_CHECKS= OFF ;
ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);

mysql [localhost] {msandbox} (test1) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql [localhost] {msandbox} (test1) > 

mysql log:
Version: '5.6.49'  socket: '/tmp/mysql_sandbox5649.sock'  port: 5649  MySQL Community Server (GPL)
2020-08-23 18:23:00 28872 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 ADD COLUMN v INT
2020-08-23 18:26:14 28872 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f)

Next restart has warnings,

2020-08-23 18:28:53 3207 [Note] InnoDB: Completed initialization of buffer pool
2020-08-23 18:28:53 3207 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_000000000000001e_INDEX_6 not found.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_BEING_DELETED not found.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_BEING_DELETED_CACHE not found.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_CONFIG not found.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_DELETED not found.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Load table 'test1/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23 18:28:53 3207 [Warning] InnoDB: Parent table of FTS auxiliary table test1/FTS_0000000000000017_DELETED_CACHE not found.

-------------
MySQL 5.7.31
-------------

In MySQL 5.7.31 behavior is different it gives warnings for ALTER, no error in mysql log.

Case1:
mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD COLUMN v INT;
Query OK, 0 rows affected, 1 warning (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1088 | InnoDB: Could not add foreign key constraints. |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

Case2:

mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
Query OK, 0 rows affected, 2 warnings (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
| Warning | 1088 | InnoDB: Could not add foreign key constraints.   |
+---------+------+--------------------------------------------------+
2 rows in set (0.00 sec)
[23 Aug 2020 13:11] lalit Choudhary 
Crash when using debug build 5.6
[23 Aug 2020 14:39] lalit Choudhary 
5.7 also affected with different behavior.

Run case1 and case2,restart mysql and try to access these tables,

mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
Query OK, 0 rows affected, 2 warnings (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql [localhost] {msandbox} (test) > show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
| Warning | 1088 | InnoDB: Could not add foreign key constraints.   |
+---------+------+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > show create table t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

2020-08-23T14:30:18.236096Z 2 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23T14:30:18.236248Z 2 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

mysql [localhost] {msandbox} (test) > drop database test;
ERROR 1010 (HY000): Error dropping database (can't rmdir './test', errno: 39)

2020-08-23T14:36:40.187472Z 2 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2020-08-23T14:36:40.187696Z 2 [ERROR] InnoDB: Cannot load table test/t1 from InnoDB internal data dictionary during drop database

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.