MySQL Bugs: #88354: InnoDB: Assertion failure: api0api.cc:3706:!sdi_delete

Bug #88354	InnoDB: Assertion failure: api0api.cc:3706:!sdi_delete_failed
Submitted:	3 Nov 2017 22:53	Modified:	14 Nov 2017 15:27
Reporter:	Roel Van de Paar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.3 RC	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
2017-11-03T22:19:57.915688Z 0 [Note] /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld: ready for connections. Version: '8.0.3-rc-debug-log'  socket: '/sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/socket.sock'  port: 16654  MySQL Community Server (GPL)
[...]
2017-11-03T22:20:10.519358Z 6 [Warning] InnoDB: Cannot find a free slot for an undo log. Do you have too many active transactions running concurrently?
2017-11-03T22:20:10.519489Z 6 [Warning] InnoDB: sdi_delete failed: tablespace_id: 3 Key: 1 332 Error returned: 47
2017-11-03T22:20:10.519508Z 6 [ERROR] InnoDB: Assertion failure: api0api.cc:3706:!sdi_delete_failed
InnoDB: thread 140155813820160

Core was generated by `/sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld --no-defaults --core'.
Program terminated with signal 6, Aborted.
#0  0x00007f78912489b1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
61	  val = INTERNAL_SYSCALL (tgkill, err, 3, THREAD_GETMEM (THREAD_SELF, pid),
(gdb) bt
#0  0x00007f78912489b1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1  0x000000000369e428 in my_write_core (sig=6) at /git/mysql-server_dbg/mysys/stacktrace.cc:291
#2  0x000000000286b8f6 in handle_fatal_signal (sig=6) at /git/mysql-server_dbg/sql/signal_handler.cc:234
#3  <signal handler called>
#4  0x00007f788f5601f7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#5  0x00007f788f5618e8 in __GI_abort () at abort.c:90
#6  0x0000000003a8b8f9 in ut_dbg_assertion_failed (expr=0x48e58c3 "!sdi_delete_failed", 
    file=0x48e4fc0 "/git/mysql-server_dbg/storage/innobase/api/api0api.cc", line=3706)
    at /git/mysql-server_dbg/storage/innobase/ut/ut0dbg.cc:60
#7  0x0000000003aaa4b4 in ib_sdi_delete (tablespace_id=3, ib_sdi_key=0x7f78917e60f0, trx=0x7f787b0008c0)
    at /git/mysql-server_dbg/storage/innobase/api/api0api.cc:3706
#8  0x0000000003b99c09 in dict_sdi_delete (tablespace=..., table=0x7f784a4289d0, sdi_key=0x7f78917e7760)
    at /git/mysql-server_dbg/storage/innobase/dict/dict0sdi.cc:462
#9  0x0000000002c4bcf7 in dd::sdi_tablespace::__lambda3::operator() (__closure=0x7f78917e7770, tblspc=...)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:291
#10 0x0000000002c4c726 in (anonymous namespace)::__lambda0::operator() (__closure=0x7f78917e7700, tsid=8)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:150
#11 0x0000000002c4d2e9 in (anonymous namespace)::Apply_to_tsid::operator()<dd::Index, (anonymous namespace)::apply_to_tablespaces(THD*, const dd::Table&, CLOSURE_TYPE&&) [with CLOSURE_TYPE = dd::sdi_tablespace::drop_tbl_sdi(THD*, const handlerton&, const dd::Table&, const dd::Schema&)::__lambda3]::__lambda0>(const dd::Index &, <unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b2c3>) (this=0x7f78917e771f, t=..., 
    clos=<unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b2c3>)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:58
#12 0x0000000002c4ce8e in (anonymous namespace)::apply_to_table_graph<(anonymous namespace)::Apply_to_tsid, (anonymous namespace)::apply_to_tablespaces(THD*, const dd::Table&, CLOSURE_TYPE&&) [with CLOSURE_TYPE = dd::sdi_tablespace::drop_tbl_sdi(THD*, const handlerton&, const dd::Table&, const dd::Schema&)::__lambda3]::__lambda0>(const dd::Table &, <unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b3b2>, <unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b2c3>) (table=..., 
    ftor=<unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b3b2>, 
    clos=<unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0b2c3>)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:73
#13 0x0000000002c4c7d2 in (anonymous namespace)::apply_to_tablespaces<dd::sdi_tablespace::drop_tbl_sdi(THD*, const handlerton&, const dd::Table&, const dd::Schema&)::__lambda3>(THD *, const dd::Table &, <unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0a84f>) (thd=0x7f784a419000, table=..., 
    clos=<unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x7b634f3, DIE 0x7c0a84f>)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:151
#14 0x0000000002c4bf03 in dd::sdi_tablespace::drop_tbl_sdi (thd=0x7f784a419000, hton=..., table=..., schema=...)
    at /git/mysql-server_dbg/sql/dd/impl/sdi_tablespace.cc:298
#15 0x0000000002c3fd1f in dd::sdi::__lambda1::operator() (__closure=0x7f78917e8760, s=...)
    at /git/mysql-server_dbg/sql/dd/impl/sdi.cc:743
#16 0x0000000002c40373 in dd::(anonymous namespace)::with_schema<long long unsigned int, dd::sdi::drop(THD*, const dd::Table*)::__lambda1>(THD *, const unsigned long long &, <unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x79a769e, DIE 0x7a53815>) (thd=0x7f784a419000, key=@0x7f78917e8778: 5, 
    clos=<unknown type in /sda/MS201017-mysql-8.0.3-rc-linux-x86_64-debug/bin/mysqld, CU 0x79a769e, DIE 0x7a53815>)
    at /git/mysql-server_dbg/sql/dd/impl/sdi.cc:567
#17 0x0000000002c3fdfd in dd::sdi::drop (thd=0x7f784a419000, tp=0x7f784a4289d0) at /git/mysql-server_dbg/sql/dd/impl/sdi.cc:745
#18 0x0000000002765ca1 in dd::cache::Storage_adapter::drop<dd::Table> (thd=0x7f784a419000, object=0x7f784a4289d0)
    at /git/mysql-server_dbg/sql/dd/impl/cache/storage_adapter.cc:240
#19 0x0000000002681f61 in dd::cache::Dictionary_client::drop<dd::Table> (this=0x7f784a41f000, object=0x7f784a4289d0)
    at /git/mysql-server_dbg/sql/dd/impl/cache/dictionary_client.cc:2408
#20 0x0000000002555d95 in mysql_inplace_alter_table (thd=0x7f784a419000, new_schema=..., table_def=0x7f784a4289d0, 
    altered_table_def=0x7f784a428bd0, table_list=0x7f784a42ff48, table=0x0, altered_table=0x7f784a4b3820, 
    ha_alter_info=0x7f78917eb690, inplace_supported=HA_ALTER_INPLACE_NO_LOCK, target_mdl_request=0x7f78917e9390, 
    alter_ctx=0x7f78917e9cd0, columns=std::set with 1 elements = {...}, fk_invalidator=0x7f78917e9190)
    at /git/mysql-server_dbg/sql/sql_table.cc:10249
#21 0x000000000255e702 in mysql_alter_table (thd=0x7f784a419000, new_db=0x7f784a4304c0 "test", new_name=0x0, 
    create_info=0x7f78917ec980, table_list=0x7f784a42ff48, alter_info=0x7f78917eca70)
    at /git/mysql-server_dbg/sql/sql_table.cc:13381
#22 0x0000000002c00ec1 in Sql_cmd_alter_table::execute (this=0x7f784a4305a0, thd=0x7f784a419000)
    at /git/mysql-server_dbg/sql/sql_alter.cc:324
#23 0x00000000024be1ff in mysql_execute_command (thd=0x7f784a419000, first_level=true)
    at /git/mysql-server_dbg/sql/sql_parse.cc:4628
#24 0x00000000024c042b in mysql_parse (thd=0x7f784a419000, parser_state=0x7f78917ee2f0)
    at /git/mysql-server_dbg/sql/sql_parse.cc:5414
#25 0x00000000024b68e7 in dispatch_command (thd=0x7f784a419000, com_data=0x7f78917eeb80, command=COM_QUERY)
    at /git/mysql-server_dbg/sql/sql_parse.cc:1708
#26 0x00000000024b53f5 in do_command (thd=0x7f784a419000) at /git/mysql-server_dbg/sql/sql_parse.cc:1291
#27 0x000000000285b2d6 in handle_connection (arg=0x7f787d6eb3c0)
    at /git/mysql-server_dbg/sql/conn_handler/connection_handler_per_thread.cc:328
#28 0x00000000036ec54d in pfs_spawn_thread (arg=0x7f787d785520) at /git/mysql-server_dbg/storage/perfschema/pfs.cc:2985
#29 0x00007f7891243e25 in start_thread (arg=0x7f78917ef700) at pthread_create.c:308
#30 0x00007f788f62334d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

How to repeat:
CREATE DATABASE test;
USE test;
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
INSTALL PLUGIN version_tokens SONAME 'version_token.so';
CREATE FUNCTION version_tokens_set RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_show RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_edit RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_delete RETURNS STRING SONAME 'version_token.so';
CREATE FUNCTION version_tokens_lock_shared RETURNS INT SONAME 'version_token.so';
CREATE FUNCTION version_tokens_lock_exclusive RETURNS INT SONAME 'version_token.so';
CREATE FUNCTION version_tokens_unlock RETURNS INT SONAME 'version_token.so';
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
CREATE FUNCTION service_get_write_locks RETURNS INT SONAME 'locking_service.so';
CREATE FUNCTION service_release_locks RETURNS INT SONAME 'locking_service.so';
INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
CREATE FUNCTION f1(i1 YEAR)RETURNS GEOMETRYCOLLECTION READS SQL DATA READS SQL DATA RETURN CONCAT('function output:',i1);
CREATE TABLE t1(c1 CHAR(1)KEY,c2 NUMERIC(1,0) ZEROFILL,c3 FLOAT(1,1) ZEROFILL) ENGINE=InnoDB;
ALTER TABLE t1 MODIFY c3 CHAR(1)CHARACTER SET 'utf8' COLLATE 'utf8_bin';
SET @@GLOBAL.innodb_trx_rseg_n_slots_debug=1;
ALTER TABLE t1 MODIFY c3 CHAR(1)CHARACTER SET 'utf8' COLLATE 'utf8_bin';

Similar to bug 88355 but different stack

See bug 88325 also

Also see bug 88356

Another testcase

CREATE DATABASE transforms;
CREATE DATABASE test;
USE test;
CREATE TABLE t1(c1 CHAR(1)KEY,c2 NUMERIC(1,0) ZEROFILL,c3 FLOAT(1,1) ZEROFILL) ENGINE=InnoDB;
ALTER TABLE t1 MODIFY c3 CHAR(1)CHARACTER SET 'utf8' COLLATE 'utf8_bin';
SET @@GLOBAL.innodb_trx_rseg_n_slots_debug=1;
ALTER TABLE t1 MODIFY c3 CHAR(1)CHARACTER SET 'utf8' COLLATE 'utf8_bin';

Hello Roel,

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

Thanks,
Umesh

Posted by developer:
 
 I verified that tolerating this assert will lead to ALTER failure and rollbacks the DDL operation.

you will get error ER_SDI_OPERATION_FAILED) :
ERROR HY000: Failed to delete SDI 'test.t1' in tablespace 'test/t1'.

It is not worth to adjust the asserts as it is using is using some variable only defined in debug build. So IMHO,
this is not a severe loss of service.
 
We define this innodb_trx_rseg_n_slots_debug for debug purpose only, and it's
true that it can be set to 1, which is not a proper value. If we only allow
one rollback segment, then no background thread can run, which will result in
all kinds of crashes easily.
 
So this bug itself is somehow bogus, but maybe we can document this
innodb_trx_rseg_n_slots_debug to say a proper value should be bigger or equal
to 2.