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

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

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);

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?]

ref bug 76445

Hello Roel,

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

Thanks,
Umesh

https://bugs.mysql.com/bug.php?id=88046 marked as duplicate of this one.

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)

Crash when using debug build 5.6

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