ALTER TABLE w/o ENGINE= clause or OPTIMIZE TABLE changes table engine if enforce-storage-engine active | handle_fatal_signal (sig=6) in heap_rfirst | heap/hp_rfirst.c:69

[Ramesh Sivaraman]

# mysqld options required for replay: --sql_mode=ONLY_FULL_GROUP_BY --enforce-storage-engine=MEMORY

DROP DATABASE test;CREATE DATABASE test;USE test;
CREATE FUNCTION func_rewrite_1(i INT)RETURNS INT DETERMINISTIC RETURN i+1;
alter table mysql.proc modify definer char(77)collate utf8_bin default'';
SHOW FUNCTION STATUS LIKE 'fn1';

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

$ vi 1440248482_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
$ ./1440248482_init # STEP2: Initializes the data dir
$ ./1440248482_start # STEP3: Starts mysqld
$ ./1440248482_cl # STEP4: To check mysqld is up
$ ./1440248482_run_pquery # STEP5: Run the testcase with the pquery binary
$ ./1440248482_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/1440248482/error.log.out # STEP6: Verify the error log
$ ./1440248482_gdb # OPTIONAL: Brings you to a gdb prompt with gdb attached to the used mysqld and attached to the generated core
$ ./1440248482_parse_core # STEP7: Create 1440248482_STD.gdb and 1440248482_FULL.gdb; standard and full variables gdb stack traces

** GDB info

#0 0x00007ff78fee6771 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1 0x0000000000abfcc4 in my_write_core (sig=6) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/my
sys/stacktrace.c:422
#2 0x00000000007330dd in handle_fatal_signal (sig=6) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-7
3.1/sql/signal_handler.cc:236
#3 <signal handler called>
#4 0x00007ff78eaea5d7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#5 0x00007ff78eaebcc8 in __GI_abort () at abort.c:90
#6 0x00007ff78eae3546 in __assert_fail_base (fmt=0x7ff78ec33128 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x10eafe4 "0", file=
file@entry=0x10eaf58 "/mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/storage/heap/hp_rfirst.c", line
=line@entry=69, function=function@entry=0x10eafe6 <__PRETTY_FUNCTION__.10906> "heap_rfirst") at assert.c:92
#7 0x00007ff78eae35f2 in __GI___assert_fail (assertion=0x10eafe4 "0", file=0x10eaf58 "/mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/storage/heap/hp_rfirst.c", line=69, function=0x10eafe6 <__PRETTY_FUNCTION__.10906> "heap_rfirst") at assert.c:101
#8 0x0000000000d92178 in heap_rfirst (info=0x7ff76f4c9a00, record=0x7ff76f451610 "\376", ' ' <repeats 384 times>, "\001", ' ' <repeats 192 times>, "\001\001\002\002", inx=0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/storage/heap/hp_rfirst.c:69
#9 0x0000000000d8cb99 in ha_heap::index_first (this=0x7ff78e015e10, buf=0x7ff76f451610 "\376", ' ' <repeats 384 times>, "\001", ' ' <repeats 192 times>, "\001\001\002\002") at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/storage/heap/ha_heap.cc:353
#10 0x00000000006461b4 in handler::ha_index_first (this=0x7ff78e015e10, buf=0x7ff76f451610 "\376", ' ' <repeats 384 times>, "\001", ' ' <repeats 192 times>, "\001\001\002\002") at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/handler.cc:3128
#11 0x000000000083d64c in fill_schema_proc (thd=0x7ff7776e5000, tables=0x7ff76f41fdf0, cond=0x0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_show.cc:6031
#12 0x00000000008454f9 in do_fill_table (thd=0x7ff7776e5000, table_list=0x7ff76f41fdf0, join_table=0x7ff76f479d20) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_show.cc:7984
#13 0x00000000008458c8 in get_schema_tables_result (join=0x7ff76f4fbb48, executed_place=PROCESSED_BY_JOIN_EXEC) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_show.cc:8085
#14 0x00000000008179dc in JOIN::prepare_result (this=0x7ff76f4fbb48, columns_list=0x7ff7904e2950) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_select.cc:823
#15 0x00000000007b3faf in JOIN::exec (this=0x7ff76f4fbb48) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_executor.cc:116
#16 0x00000000008184d5 in mysql_execute_select (thd=0x7ff7776e5000, select_lex=0x7ff7776e78b0, free_join=true) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_select.cc:1100
#17 0x00000000008187d1 in mysql_select (thd=0x7ff7776e5000, tables=0x7ff76f41fdf0, wild_num=0, fields=..., conds=0x0, order=0x7ff7776e7a78, group=0x7ff7776e79b0, having=0x0, select_options=2684619520, result=0x7ff76f4fbb20, unit=0x7ff7776e7268, select_lex=0x7ff7776e78b0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_select.cc:1221
#18 0x0000000000816868 in handle_select (thd=0x7ff7776e5000, result=0x7ff76f4fbb20, setup_tables_done_option=0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_select.cc:110
#19 0x00000000007ee7cd in execute_sqlcom_select (thd=0x7ff7776e5000, all_tables=0x7ff76f41fdf0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_parse.cc:5683
#20 0x00000000007e6c4d in mysql_execute_command (thd=0x7ff7776e5000) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_parse.cc:3006
#21 0x00000000007f14ea in mysql_parse (thd=0x7ff7776e5000, rawbuf=0x7ff76f41f010 "SHOW FUNCTION STATUS LIKE 'fn1'", length=31, parser_state=0x7ff7904e3f40) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_parse.cc:6968
#22 0x00000000007e329e in dispatch_command (command=COM_QUERY, thd=0x7ff7776e5000, packet=0x7ff7776f6001 "SHOW FUNCTION STATUS LIKE 'fn1'", packet_length=31) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_parse.cc:1442
#23 0x00000000007e210c in do_command (thd=0x7ff7776e5000) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_parse.cc:1054
#24 0x00000000007a9f60 in do_handle_one_connection (thd_arg=0x7ff7776e5000) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_connect.cc:1541
#25 0x00000000007a99e0 in handle_one_connection (arg=0x7ff7776e5000) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/sql/sql_connect.cc:1444
#26 0x0000000000dd14df in pfs_spawn_thread (arg=0x7ff78cffe2e0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.25-73.1/storage/perfschema/pfs.cc:1860
#27 0x00007ff78fee1df5 in start_thread (arg=0x7ff7904e5700) at pthread_create.c:308
#28 0x00007ff78ebab1ad in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

[Laurynas Biveinis (laurynas-biveinis)]

The problem is that ALTER TABLE changes the table storage engine even if not asked to when enforce-se is active. The fix would be to ignore enforced SE for existing table ALTER TABLEs. This is similar to https://mariadb.atlassian.net/browse/MDEV-8577.

[Laurynas Biveinis (laurynas-biveinis)]

See bug 1518183 for a 5.7 crash if mysql.event is altered to InnoDB (implicitly through enforce-storage-engine)

[Laurynas Biveinis (laurynas-biveinis)]

Re-test bug 1520403 testcase after fixing this one

[Yura Sorokin (yura-sorokin)]

This bug also affects "OPTIMIZE TABLE".
When there is already a table "t" created with engine "xxx" and Percona Server is restarted with "--enforce_storage_engine=yyy" the behavior is the following.
1. When "sql_mode" includes "NO_ENGINE_SUBSTITUTION" - "OPTIMIZE TABLE t" generates an error.
Table Op Msg_type Msg_text
test.t optimize note Table does not support optimize, doing recreate + analyze instead
test.t optimize error Unknown storage engine 'InnoDB'
test.t optimize status Operation failed

2. When "sql_mode" does not include "NO_ENGINE_SUBSTITUTION" - "OPTIMIZE TABLE t" silently changes table storage engine to "yyy" producing a substitution warning "1266 Using storage engine yyy for table 't'"

[Yura Sorokin (yura-sorokin)]

https://github.com/percona/percona-server/pull/255
https://github.com/percona/percona-server/pull/256

[Shahriyar Rzayev (rzayev-sehriyar)]

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