CREATE TABLE ... LIKE ... may create a system table with an unsupported enforced engine | Assertion `!"Transactional table"' failed in sql/sql_base.cc:10502

[Ramesh Sivaraman]

Error info

mysqld: /sda/ps5.7/PS-5.7_dbg/sql/sql_base.cc:10502: bool open_nontrans_system_tables_for_read(THD*, TABLE_LIST*, Open_tables_backup*): Assertion `!"Transact
ional table"' failed.
12:02:18 UTC - mysqld got signal 6 ;

Testcase

# mysqld options required for replay: --log-output=none --sql_mode=ONLY_FULL_GROUP_BY --enforce-storage-engine=BLACKHOLE
DROP DATABASE test;CREATE DATABASE test;USE test;
RENAME TABLE mysql.proc TO proc_backup;
CREATE TABLE mysql.proc LIKE proc_backup;
select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug0%';

The attached tarball (1454325471_bug_bundle.tar.gz) gives the testcase as an exact match of our system, including some handy utilities

$ vi 1454325471_mybase # STEP1: Update the base path in this file (usually the only change required!). If you use a non-binary distribution, please update SOURCE_DIR location also
$ ./1454325471_init # STEP2: Initializes the data dir
$ ./1454325471_start # STEP3: Starts mysqld
$ ./1454325471_cl # STEP4: To check mysqld is up
$ ./1454325471_run_pquery # STEP5: Run the testcase with the pquery binary
$ ./1454325471_run # OPTIONAL: Run the testcase with the mysql CLI (may not reproduce the issue, as the pquery binary was used for the original testcase reduction)
$ vi /dev/shm/1454325471/error.log.out # STEP6: Verify the error log
$ ./1454325471_gdb # OPTIONAL: Brings you to a gdb prompt with gdb attached to the used mysqld and attached to the generated core
$ ./1454325471_parse_core # STEP7: Creates 1454325471_STD.gdb and 1454325471_FULL.gdb; standard and full variables gdb stack traces

GDB info

#0 0x00007fd87a30e741 in pthread_kill () from /lib64/libpthread.so.0
#1 0x0000000001811a80 in my_write_core (sig=6) at /sda/ps5.7/PS-5.7_dbg/mysys/stacktrace.c:247
#2 0x0000000000e60b43 in handle_fatal_signal (sig=6) at /sda/ps5.7/PS-5.7_dbg/sql/signal_handler.cc:223
#3 <signal handler called>
#4 0x00007fd87849b5f7 in raise () from /lib64/libc.so.6
#5 0x00007fd87849cce8 in abort () from /lib64/libc.so.6
#6 0x00007fd878494566 in __assert_fail_base () from /lib64/libc.so.6
#7 0x00007fd878494612 in __assert_fail () from /lib64/libc.so.6
#8 0x000000000148c0f4 in open_nontrans_system_tables_for_read (thd=0x7fd839019000, table_list=0x7fd87a8c1f30, backup=0x7fd87a8c25c0) at /sda/ps5.7/PS-5.7_dbg/sql/sql_base.cc:10502
#9 0x000000000144f65c in open_proc_table_for_read (thd=0x7fd839019000, backup=0x7fd87a8c25c0) at /sda/ps5.7/PS-5.7_dbg/sql/sp.cc:418
#10 0x000000000157b9f5 in fill_schema_proc (thd=0x7fd839019000, tables=0x7fd83902c588, cond=0x7fd83902c010) at /sda/ps5.7/PS-5.7_dbg/sql/sql_show.cc:6403
#11 0x0000000001584189 in do_fill_table (thd=0x7fd839019000, table_list=0x7fd83902c588, qep_tab=0x7fd839095ec8) at /sda/ps5.7/PS-5.7_dbg/sql/sql_show.cc:8558
#12 0x000000000158456e in get_schema_tables_result (join=0x7fd839094868, executed_place=PROCESSED_BY_JOIN_EXEC) at /sda/ps5.7/PS-5.7_dbg/sql/sql_show.cc:8660
#13 0x000000000155a9a1 in JOIN::prepare_result (this=0x7fd839094868) at /sda/ps5.7/PS-5.7_dbg/sql/sql_select.cc:909
#14 0x00000000014bd57c in JOIN::exec (this=0x7fd839094868) at /sda/ps5.7/PS-5.7_dbg/sql/sql_executor.cc:124
#15 0x0000000001559576 in handle_query (thd=0x7fd839019000, lex=0x7fd83901b5d0, result=0x7fd839094688, added_options=0, removed_options=0) at /sda/ps5.7/PS-5.7_dbg/sql/sql_select.cc:184
#16 0x000000000150e373 in execute_sqlcom_select (thd=0x7fd839019000, all_tables=0x7fd83902c588) at /sda/ps5.7/PS-5.7_dbg/sql/sql_parse.cc:5347
#17 0x000000000150776d in mysql_execute_command (thd=0x7fd839019000, first_level=true) at /sda/ps5.7/PS-5.7_dbg/sql/sql_parse.cc:2882
#18 0x000000000150f32a in mysql_parse (thd=0x7fd839019000, parser_state=0x7fd87a8c4500) at /sda/ps5.7/PS-5.7_dbg/sql/sql_parse.cc:5786
#19 0x0000000001504341 in dispatch_command (thd=0x7fd839019000, com_data=0x7fd87a8c4c90, command=COM_QUERY) at /sda/ps5.7/PS-5.7_dbg/sql/sql_parse.cc:1445
#20 0x000000000150325b in do_command (thd=0x7fd839019000) at /sda/ps5.7/PS-5.7_dbg/sql/sql_parse.cc:1008
#21 0x000000000163ad13 in handle_connection (arg=0x7fd861ffbc10) at /sda/ps5.7/PS-5.7_dbg/sql/conn_handler/connection_handler_per_thread.cc:313
#22 0x00000000018b119c in pfs_spawn_thread (arg=0x7fd8533f2220) at /sda/ps5.7/PS-5.7_dbg/storage/perfschema/pfs.cc:2192
#23 0x00007fd87a309dc5 in start_thread () from /lib64/libpthread.so.0
#24 0x00007fd87855c21d in clone () from /lib64/libc.so.6

[Laurynas Biveinis (laurynas-biveinis)]

Cannot reproduce:

RENAME TABLE mysql.proc TO proc_backup;

CREATE TABLE mysql.proc LIKE proc_backup;
ERROR 42000: Unknown storage engine 'MyISAM'

query 'select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug0%'' failed: 1146: Table 'mysql.proc' doesn't exist

Ramesh, please confirm your source tree revision and CLI output.

[Ramesh Sivaraman (rameshvs02)]

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> RENAME TABLE mysql.proc TO proc_backup;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mysql.proc LIKE proc_backup;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug0%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Source tree commit

[ramesh@qaserver-05 PS-5.7]$ git log
commit b1ece8fcc160df945e08f2894f0a4597652cf326
Author: Yura Sorokin <email address hidden>
Date: Mon Jan 18 14:16:36 2016 +0200

    Merge pull request #316 from percona-ysorokin/ps-5.7-bug1534246-ctype_ucs

    Fix bug lp:1534246 (5.7) "'ctype_ucs.test' fails sporadically"
[ramesh@qaserver-05 PS-5.7]$

[Roel Van de Paar (roel11)]

[roel@localhost PS-5.7]$ git log | head
commit 2057849563980af68f5c8a19d793fede341fd141
Merge: 6f7d1de 364c88c
Author: georgelorchpercona <email address hidden>
Date: Fri Jan 29 12:54:20 2016 -0700

    Merge pull request #333 from georgelorchpercona/ps-5.7-DB-924

    DB-924 : 5.7 support new group commit algorithm

commit 364c88cbec228c6d2456f1ad93121d4b42d7bd66

==============

mysql> DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql> RENAME TABLE mysql.proc TO proc_backup;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE mysql.proc LIKE proc_backup;
ERROR 1067 (42000): Invalid default value for 'modified' <----------------------- (!)
mysql> select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug0%';
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist

[Roel Van de Paar (roel11)]

Invalid default value for 'modified' - seems very odd for a mysql table, correct?

[Laurynas Biveinis (laurynas-biveinis)]

Not particularly odd. Roel, compare sql_mode in your reproduction attempt with that of Ramesh.

[Roel Van de Paar (roel11)]

Ah. I had missed that. Reproduces with last 5.7 build today.

mysql> CREATE TABLE mysql.proc LIKE proc_backup;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select routine_name,routine_schema from information_schema.routines where routine_schema like 'bug0%';
ERROR 2013 (HY000): Lost connection to MySQL server during query

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/404, https://github.com/percona/percona-server/pull/405, https://github.com/percona/percona-server/pull/406

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/407

[mig5 (mig5)]

Could this have introduced a regression #1600056 ?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3368