Percona Server moved to https://jira.percona.com/projects/PS

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

Bug #1488055 reported by Ramesh Sivaraman
22
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Invalid
Undecided
Unassigned
5.5
Fix Released
High
Yura Sorokin
Percona Server moved to https://jira.percona.com/projects/PS 5.5.47-37.7
5.6
Fix Released
High
Yura Sorokin
Percona Server moved to https://jira.percona.com/projects/PS 5.6.28-76.1
5.7
Fix Released
High
Yura Sorokin
Percona Server moved to https://jira.percona.com/projects/PS 5.7.10-1rc1

Bug Description

# 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

Tags: qa qa57
Revision history for this message
Ramesh Sivaraman (rameshvs02) wrote :
Laurynas Biveinis (laurynas-biveinis)
summary: - handle_fatal_signal (sig=6) in heap_rfirst | heap/hp_rfirst.c:69
+ ALTER TABLE w/o ENGINE= clause changes table engine if enforce-storage-
+ engine active | handle_fatal_signal (sig=6) in heap_rfirst |
+ heap/hp_rfirst.c:69
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote : Re: ALTER TABLE w/o ENGINE= clause changes table engine if enforce-storage-engine active | handle_fatal_signal (sig=6) in heap_rfirst | heap/hp_rfirst.c:69

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.

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

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

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Re-test bug 1520403 testcase after fixing this one

Revision history for this message
Yura Sorokin (yura-sorokin) wrote :

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'"

Revision history for this message
Yura Sorokin (yura-sorokin) wrote :

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

Laurynas Biveinis (laurynas-biveinis)
no longer affects: percona-server/5.7
summary: - ALTER TABLE w/o ENGINE= clause changes table engine if enforce-storage-
- engine active | handle_fatal_signal (sig=6) in heap_rfirst |
- heap/hp_rfirst.c:69
+ 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
Roel Van de Paar (roel11)
tags: added: qa57
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.