[MDEV-7445] Server crash with Signal 6 Created: 2015-01-12  Updated: 2023-10-18  Resolved: 2015-10-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.3.12, 5.5, 10.0
Fix Version/s: 5.5.46, 10.0.22, 10.1.8

Type: Bug Priority: Critical
Reporter: Leo Kwan Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: verified
Environment:

RHEL 6

Issue Links:
Blocks
PartOf
includes MDEV-7565 Server crash with Signal 6 (part 2) Closed
Relates
relates to MDEV-7846 Server crashes in Item_subselect::fix... Closed
relates to MDEV-16128 Server crash in Item_func::print_op o... Closed
Sprint: 10.1.8-3, 10.1.8-4

 Description   

If I run this store procedure twice in a row in the same session, the server will crash with Signal 6.

CREATE DEFINER = 'leo'@'%'
 
PROCEDURE PlayGround.procedure2()
 
BEGIN
 
 
 
  Select 
    (Select Sum(`TestCase`.Revenue) From PlayGround.Anything E           
      Where TestCase.TemplateID not in (Select 1 from PlayGround.Anything where 2=2)
 
     ) As `ControlRev`
 
  From 
  (Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
  Group By  TestCase.Revenue, TestCase.TemplateID;
 
 
 
END

PlayGround.Anything table is an empty table with only one primary key field `ID`, and it doesn't really have to do anything, it could be any table even mysql.slow_log. The content of the SP may look strange, but I was try to simplify my product SP to create a test case.

Here is the log

pure virtual method called
 
terminate called without an active exception
 
150112 11:47:42 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.0.15-MariaDB-wsrep-log
 
key_buffer_size=524288
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=501
 
thread_count=11
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 525444581 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f30c3f7c008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f316f0efcb0 thread_stack 0x40000
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0xbe922e]
 
/usr/sbin/mysqld(handle_fatal_signal+0x390)[0x751a10]
 
/lib64/libpthread.so.0(+0xf130)[0x7f3974025130]
 
/lib64/libc.so.6(gsignal+0x39)[0x7f39727dd5c9]
 
/lib64/libc.so.6(abort+0x148)[0x7f39727decd8]
 
/lib64/libstdc++.so.6(_ZN9__gnu_cxx27__verbose_terminate_handlerEv+0x165)[0x7f3972ecb9d5]
 
/lib64/libstdc++.so.6(+0x5e946)[0x7f3972ec9946]
 
/lib64/libstdc++.so.6(+0x5e973)[0x7f3972ec9973]
 
/lib64/libstdc++.so.6(+0x5f4df)[0x7f3972eca4df]
 
/usr/sbin/mysqld(_ZN10Item_cache5setupEP4Item+0x48)[0x779408]
 
/usr/sbin/mysqld(_ZN17Item_in_optimizer8fix_leftEP3THD+0x3e)[0x78b87e]
 
/usr/sbin/mysqld(_ZN17Item_in_optimizer10fix_fieldsEP3THDPP4Item+0x18)[0x78bbe8]
 
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x190)[0x7ace70]
 
/usr/sbin/mysqld(_ZN13Item_func_not10fix_fieldsEP3THDPP4Item+0x32e)[0x78681e]
 
/usr/sbin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item+0x188)[0x590868]
 
/usr/sbin/mysqld[0x623087]
 
/usr/sbin/mysqld(_ZN30subselect_single_select_engine7prepareEv+0x6d6)[0x7e2b66]
 
/usr/sbin/mysqld(_ZN14Item_subselect10fix_fieldsEP3THDPP4Item+0xd5)[0x7e0945]
 
/usr/sbin/mysqld(_Z12setup_fieldsP3THDPP4ItemR4ListIS1_E17enum_mark_columnsPS5_b+0x17c)[0x58eaec]
 
/usr/sbin/mysqld[0x622fd2]
 
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x8f6)[0x62e656]
 
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x27d)[0x62e95d]
 
/usr/sbin/mysqld[0x5cdd1e]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x569d)[0x5dc71d]
 
/usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x34)[0x86ab44]
 
/usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x80)[0x872000]
 
/usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x155)[0x872425]
 
/usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x6ac)[0x86f0dc]
 
/usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x694)[0x870704]
 
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x5faf)[0x5dd02f]
 
/usr/sbin/mysqld[0x5e0c61]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1ac5)[0x5e2c95]
 
/usr/sbin/mysqld(_Z10do_commandP3THD+0x1ba)[0x5e398a]
 
/usr/sbin/mysqld(_Z26threadpool_process_requestP3THD+0xc7)[0x6e3d17]
 
/usr/sbin/mysqld[0x7246ed]
 
/lib64/libpthread.so.0(+0x7df3)[0x7f397401ddf3]
 
/lib64/libc.so.6(clone+0x6d)[0x7f397289e01d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f30c2c56020): is an invalid pointer
 
Connection ID (thread ID): 13
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
150112 11:47:42 mysqld_safe Number of processes running now: 0
 
150112 11:47:42 mysqld_safe WSREP: not restarting wsrep node automatically
 
150112 11:47:42 mysqld_safe mysqld from pid file /mysqlc/conf/mysql.pid ended



 Comments   
Comment by Elena Stepanova [ 2015-01-13 ]

Thanks for the report.
On a debug build, I'm getting the assertion failure just by running the query once. On a release build, indeed, a crash happens after the 2nd execution of the SP.

sanja,
Please check both while fixing it!

Test case for debug version

 
Select 
  (Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
    Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
 
  ) As `ControlRev`
 
From 
(Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
Group By  TestCase.Revenue, TestCase.TemplateID;

Stack trace from 5.5 revno 4398 debug

 
sql/opt_subselect.cc:824: bool subquery_types_allow_materialization(Item_in_subselect*): Assertion `in_subs->left_expr->fixed' failed.
 

#6  0x00007f00ecddf6f1 in *__GI___assert_fail (assertion=0xda85ed "in_subs->left_expr->fixed", file=<optimized out>, line=824, function=0xda99e0 "bool subquery_types_allow_materialization(Item_in_subselect*)") at assert.c:81
 
#7  0x0000000000771679 in subquery_types_allow_materialization (in_subs=0x7f00e7a80d28) at 5.5/sql/opt_subselect.cc:824
 
#8  0x0000000000770d7b in is_materialization_applicable (thd=0x7f00e8950060, in_subs=0x7f00e7a80d28, child_select=0x7f00e7a88808) at 5.5/sql/opt_subselect.cc:528
 
#9  0x00000000007713fe in check_and_do_in_subquery_rewrites (join=0x7f00e7931958) at 5.5/sql/opt_subselect.cc:714
 
#10 0x0000000000663ff2 in JOIN::prepare (this=0x7f00e7931958, rref_pointer_array=0x7f00e7a88a78, tables_init=0x7f00e7a80758, wild_num=0, conds_init=0x7f00e7a88db0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f00e7a88808, unit_arg=0x7f00e7a80078) at 5.5/sql/sql_select.cc:764
 
#11 0x0000000000883932 in subselect_single_select_engine::prepare (this=0x7f00e7a88f40) at 5.5/sql/item_subselect.cc:3025
 
#12 0x000000000087c223 in Item_subselect::fix_fields (this=0x7f00e7a80d28, thd_param=0x7f00e8950060, ref=0x7f00e7a89010) at 5.5/sql/item_subselect.cc:245
 
#13 0x0000000000882c5e in Item_in_subselect::fix_fields (this=0x7f00e7a80d28, thd_arg=0x7f00e8950060, ref=0x7f00e7a89010) at 5.5/sql/item_subselect.cc:2708
 
#14 0x00000000008424b0 in Item_func::fix_fields (this=0x7f00e7a88f80, thd=0x7f00e8950060, ref=0x7f00e7931648) at 5.5/sql/item_func.cc:204
 
#15 0x00000000008288f8 in Item_func_not::fix_fields (this=0x7f00e7a88f80, thd=0x7f00e8950060, ref=0x7f00e7931648) at 5.5/sql/item_cmpfunc.cc:5441
 
#16 0x00000000005e3f70 in setup_conds (thd=0x7f00e8950060, tables=0x7f00e7a88108, leaves=..., conds=0x7f00e7931648) at 5.5/sql/sql_base.cc:8891
 
#17 0x00000000006a49a8 in setup_without_group (thd=0x7f00e8950060, ref_pointer_array=0x7f00e7931760, tables=0x7f00e7a88108, leaves=..., fields=..., all_fields=..., conds=0x7f00e7931648, order=0x0, group=0x0, hidden_group_fields=0x7f00e7931530) at 5.5/sql/sql_select.cc:577
 
#18 0x0000000000663c14 in JOIN::prepare (this=0x7f00e7931230, rref_pointer_array=0x7f00e7a87580, tables_init=0x7f00e7a88108, wild_num=0, conds_init=0x7f00e7a88f80, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f00e7a87310, unit_arg=0x7f00e7a87680) at 5.5/sql/sql_select.cc:727
 
#19 0x0000000000883932 in subselect_single_select_engine::prepare (this=0x7f00e7a80fc0) at 5.5/sql/item_subselect.cc:3025
 
#20 0x000000000087c223 in Item_subselect::fix_fields (this=0x7f00e7a80ea8, thd_param=0x7f00e8950060, ref=0x7f00e7a81038) at 5.5/sql/item_subselect.cc:245
 
#21 0x00000000005e2298 in setup_fields (thd=0x7f00e8950060, ref_pointer_array=0x7f00e7931078, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f00e7a835c8, allow_sum_func=true) at 5.5/sql/sql_base.cc:8166
 
#22 0x0000000000663b5c in JOIN::prepare (this=0x7f00e7a83298, rref_pointer_array=0x7f00e8953cd0, tables_init=0x7f00e7a82940, wild_num=0, conds_init=0x0, og_num=2, order_init=0x0, skip_order_by=false, group_init=0x7f00e7a83038, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f00e8953a60, unit_arg=0x7f00e8953380) at 5.5/sql/sql_select.cc:723
 
#23 0x000000000066c2ab in mysql_select (thd=0x7f00e8950060, rref_pointer_array=0x7f00e8953cd0, tables=0x7f00e7a82940, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0, group=0x7f00e7a83038, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f00e7a83278, unit=0x7f00e8953380, select_lex=0x7f00e8953a60) at 5.5/sql/sql_select.cc:3074
 
#24 0x0000000000662e2d in handle_select (thd=0x7f00e8950060, lex=0x7f00e89532d0, result=0x7f00e7a83278, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319
 
#25 0x000000000063c06c in execute_sqlcom_select (thd=0x7f00e8950060, all_tables=0x7f00e7a82940) at 5.5/sql/sql_parse.cc:4689
 
#26 0x000000000063524e in mysql_execute_command (thd=0x7f00e8950060) at 5.5/sql/sql_parse.cc:2234
 
#27 0x000000000063eb52 in mysql_parse (thd=0x7f00e8950060, rawbuf=0x7f00e7a87078 "Select \n(Select Sum(`TestCase`.Revenue) From mysql.slow_log E", ' ' <repeats 11 times>, "\nWhere TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)\n) As `ControlRev`\nFrom \n(Select  3 as Revenue, 4 as T"..., length=273, parser_state=0x7f00e83b5620) at 5.5/sql/sql_parse.cc:5909
 
#28 0x0000000000632795 in dispatch_command (command=COM_QUERY, thd=0x7f00e8950060, packet=0x7f00e8a07061 "Select \n(Select Sum(`TestCase`.Revenue) From mysql.slow_log E", ' ' <repeats 11 times>, "\nWhere TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)\n) As `ControlRev`\nFrom \n(Select  3 as Revenue, 4 as T"..., packet_length=273) at 5.5/sql/sql_parse.cc:1079
 
#29 0x0000000000631921 in do_command (thd=0x7f00e8950060) at 5.5/sql/sql_parse.cc:793
 
#30 0x0000000000733eee in do_handle_one_connection (thd_arg=0x7f00e8950060) at 5.5/sql/sql_connect.cc:1266
 
#31 0x00000000007339ad in handle_one_connection (arg=0x7f00e8950060) at 5.5/sql/sql_connect.cc:1181
 
#32 0x0000000000b6bf51 in pfs_spawn_thread (arg=0x7f00e8971ca0) at 5.5/storage/perfschema/pfs.cc:1015
 
#33 0x00007f00eebd9b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#34 0x00007f00ece9020d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Test case for release build

 
--delimiter |
 
CREATE PROCEDURE procedure2()
 
BEGIN
 
  Select 
    (Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
      Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
 
     ) As `ControlRev`
 
  From 
  (Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
  Group By  TestCase.Revenue, TestCase.TemplateID;
 
 
 
END |
 
--delimiter ;
 
call procedure2();
 
call procedure2();

Stack trace from 5.5.41 release build

 
pure virtual method called
 
terminate called without an active exception
 
150113 15:28:21 [ERROR] mysqld got signal 6 ;
 

#4  0x00007ff6852137c0 in *__GI_abort () at abort.c:92
 
#5  0x00007ff68585089d in __gnu_cxx::__verbose_terminate_handler() () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
 
#6  0x00007ff68584e996 in ?? () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
 
#7  0x00007ff68584e9c3 in std::terminate() () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
 
#8  0x00007ff68584f4df in __cxa_pure_virtual () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
 
#9  0x0000000000708cd0 in Item_cache::setup (this=0x7ff67fc63c90, item=0x2bfe) atsql/item.h:4095
 
#10 0x0000000000711a47 in Item_in_optimizer::fix_left (this=0x7ff67fc63b98, thd=<optimized out>, ref=<optimized out>) atsql/item_cmpfunc.cc:1450
 
#11 0x0000000000711d28 in Item_in_optimizer::fix_fields (this=0x2bf3, thd=0x2bfe, ref=0x6) atsql/item_cmpfunc.cc:1503
 
#12 0x000000000073aa14 in Item_func::fix_fields (this=0x7ff67fc60cb8, thd=0x7ff6833b4000, ref=<optimized out>) atsql/item_func.cc:204
 
#13 0x0000000000716a5f in Item_func_not::fix_fields (this=0x7ff67fc60cb8, thd=0x7ff6833b4000, ref=0x7ff67fdfe320) atsql/item_cmpfunc.cc:5441
 
#14 0x0000000000536744 in setup_conds (thd=0x7ff6833b4000, tables=0x7ff67fc65338, leaves=..., conds=0x7ff67fdfe320) atsql/sql_base.cc:8891
 
#15 0x00000000005acf13 in setup_without_group (hidden_group_fields=<optimized out>, group=0x0, order=<optimized out>, conds=0x7ff67fdfe320, all_fields=..., fields=..., leaves=..., tables=<optimized out>, ref_pointer_array=<optimized out>, thd=<optimized out>) atsql/sql_select.cc:577
 
#16 JOIN::prepare (this=0x7ff67fdfdf08, rref_pointer_array=0x7ff67fc25010, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ff67fc24da0, unit_arg=0x7ff67fc25110) atsql/sql_select.cc:719
 
#17 0x000000000076fe66 in subselect_single_select_engine::prepare (this=0x7ff67fc65f90) atsql/item_subselect.cc:3014
 
#18 0x000000000076e7dd in Item_subselect::fix_fields (this=0x7ff67fc60d78, thd_param=<optimized out>, ref=0x7ff67fc60ee8) atsql/item_subselect.cc:245
 
#19 0x0000000000534009 in setup_fields (thd=0x7ff6833b4000, ref_pointer_array=0x7ff67fc63840, fields=..., mark_used_columns=4294967295, sum_func_list=0x7ff67fdfd5d0, allow_sum_func=false) atsql/sql_base.cc:8166
 
#20 0x00000000005ace5c in JOIN::prepare (this=0x7ff67fdfd2a0, rref_pointer_array=0x7ff67fc64a10, tables_init=<optimized out>, wild_num=0, conds_init=<optimized out>, og_num=2, order_init=0x0, skip_order_by=false, group_init=0x7ff67fc62fb0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ff67fc647a0, unit_arg=0x7ff67fc640c8) atsql/sql_select.cc:719
 
#21 0x00000000005d4913 in mysql_select (thd=0x7ff6833b4000, rref_pointer_array=0x7ff67fc64a10, tables=0x7ff67fc62888, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0, group=0x7ff67fc62fb0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7ff67fdfd280, unit=0x7ff67fc640c8, select_lex=0x7ff67fc647a0) atsql/sql_select.cc:3072
 
#22 0x00000000005d4d94 in handle_select (thd=0x7ff6833b4000, lex=0x7ff67fc64018, result=0x7ff67fdfd280, setup_tables_done_option=0) atsql/sql_select.cc:319
 
#23 0x000000000057a670 in execute_sqlcom_select (thd=0x7ff6833b4000, all_tables=0x7ff67fc62888) atsql/sql_parse.cc:4689
 
#24 0x00000000005844a7 in mysql_execute_command (thd=0x7ff6833b4000) atsql/sql_parse.cc:2234
 
#25 0x00000000007f93de in sp_instr_stmt::exec_core (this=0x7ff67fc63190, thd=0x2bfe, nextp=0x6) atsql/sp_head.cc:3222
 
#26 0x00000000007f9571 in sp_lex_keeper::reset_lex_and_exec_core (this=0x7ff67fc631d0, thd=0x7ff6833b4000, nextp=0x7ff6865061f8, open_tables=false, instr=0x7ff67fc63190) atsql/sp_head.cc:3000
 
#27 0x00000000008006fc in sp_instr_stmt::execute (this=0x7ff67fc63190, thd=0x7ff6833b4000, nextp=0x7ff6865061f8) atsql/sp_head.cc:3146
 
#28 0x00000000007fd440 in sp_head::execute (this=0x7ff67fc24018, thd=0x7ff6833b4000, merge_da_on_success=true) atsql/sp_head.cc:1433
 
#29 0x00000000007fec9c in sp_head::execute_procedure (this=0x7ff67fc24018, thd=0x7ff6833b4000, args=0x7ff6833b7df8) atsql/sp_head.cc:2199
 
#30 0x0000000000582b55 in mysql_execute_command (thd=0x7ff6833b4000) atsql/sql_parse.cc:4123
 
#31 0x0000000000587081 in mysql_parse (thd=0x7ff6833b4000, rawbuf=0x7ff67fc1f018 "call procedure2()", length=<optimized out>, parser_state=0x7ff686507c10) atsql/sql_parse.cc:5909
 
#32 0x00000000005887ac in dispatch_command (command=COM_QUERY, thd=0x7ff6833b4000, packet=<optimized out>, packet_length=17) atsql/sql_parse.cc:1079
 
#33 0x0000000000588d52 in do_command (thd=0x7ff6833b4000) atsql/sql_parse.cc:793
 
#34 0x0000000000643aa3 in do_handle_one_connection (thd_arg=0x7ff6833b4000) atsql/sql_connect.cc:1266
 
#35 0x0000000000643bec in handle_one_connection (arg=<optimized out>) atsql/sql_connect.cc:1181
 
#36 0x00000000009b0658 in pfs_spawn_thread (arg=<optimized out>) atstorage/perfschema/pfs.cc:1015
 
#37 0x00007ff68613bb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#38 0x00007ff6852ba20d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Comment by Oleksandr Byelkin [ 2015-02-09 ]

IN subselect has no Item_in_optimiser traces at all (or I can't find).

Comment by Oleksandr Byelkin [ 2015-02-09 ]

check_and_do_in_subquery_rewrites(d) checks is_materialization_applicable() which prepared left part and so on, but it will happened only later when subselect->select_transformer(join) will be called.

Comment by Oleksandr Byelkin [ 2015-02-09 ]

We agreed that too late "transformation" with preparing left part is historical and it is better to move it a bit earlier.

Comment by Oleksandr Byelkin [ 2015-02-09 ]

It is two independent bugs.

Comment by Oleksandr Byelkin [ 2015-02-09 ]

Second execution bug caused by reference to uninitialized memory as first argument of Item_in_optimizer

Comment by Oleksandr Byelkin [ 2015-02-09 ]

For first test suite https://mariadb.atlassian.net/browse/MDEV-7565 was created

Comment by Oleksandr Byelkin [ 2015-02-09 ]

Problem of second execution is that left expression has two reference - subselect and in_optimizer so after calling fix_fields for subselect reference and substitution then we assign it to in_optimizer and on second execution pointer of in_optimizer points to freed memory.

Comment by Oleksandr Byelkin [ 2015-02-11 ]

Bug fixed by taking subselect reference on the left expression as main and in_optimizer will be synced with it.

Also incorrect usage items fixed in 2 places.

Comment by Oleksandr Byelkin [ 2015-02-13 ]

It is sent for testing.
lp:~maria-captains/maria/5.5-MDEV-7445-sanja

Comment by Sergei Petrunia [ 2015-02-13 ]

Reviewed, approved with comments. We need elenst to test this and other recent fixes for non-semijoin subqueries.

Comment by Elena Stepanova [ 2015-02-17 ]

I am running tests, but anyway the fix will go to the next release (5.5.43) because we want to do some more testing – it's unwise to push a risky change right before a release.

Comment by Elena Stepanova [ 2015-03-21 ]

The problem still exists on the development tree. The initial query passes now, but this one fails with the same assertion failure:

SELECT
 
  (SELECT user FROM mysql.user
 
    WHERE h.host in (SELECT host FROM mysql.user)
 
  ) AS sq
 
FROM mysql.host h GROUP BY h.host;
 

mysqld: sql/opt_subselect.cc:824: bool subquery_types_allow_materialization(Item_in_subselect*): Assertion `in_subs->left_expr->fixed' failed.
 
150321 23:35:05 [ERROR] mysqld got signal 6 ;
 
 
 
#6  0x00007f24176f9311 in *__GI___assert_fail (assertion=0xde022d "in_subs->left_expr->fixed", file=<optimized out>, line=824, function=0xde1620 "bool subquery_types_allow_materialization(Item_in_subselect*)") at assert.c:81
 
#7  0x000000000077963e in subquery_types_allow_materialization (in_subs=0x7f24120451d8) at sql/opt_subselect.cc:824
 
#8  0x000000000077924e in check_and_do_in_subquery_rewrites (join=0x7f24120ec218) at sql/opt_subselect.cc:684
 
#9  0x0000000000664f0f in JOIN::prepare (this=0x7f24120ec218, rref_pointer_array=0x7f241204ead8, tables_init=0x7f24120eaa18, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f241204e818, unit_arg=0x7f2412029018) at sql/sql_select.cc:764
 
#10 0x000000000088d8a2 in subselect_single_select_engine::prepare (this=0x7f24120140f8) at sql/item_subselect.cc:3025
 
#11 0x0000000000886181 in Item_subselect::fix_fields (this=0x7f24120451d8, thd_param=0x7f2412d18000, ref=0x7f24120ec0a0) at sql/item_subselect.cc:245
 
#12 0x000000000088cbca in Item_in_subselect::fix_fields (this=0x7f24120451d8, thd_arg=0x7f2412d18000, ref=0x7f24120ec0a0) at sql/item_subselect.cc:2708
 
#13 0x00000000005e24dd in setup_conds (thd=0x7f2412d18000, tables=0x7f24120ea418, leaves=..., conds=0x7f24120ec0a0) at sql/sql_base.cc:8891
 
#14 0x00000000006a5fb9 in setup_without_group (thd=0x7f2412d18000, ref_pointer_array=0x7f241227bb98, tables=0x7f24120ea418, leaves=..., fields=..., all_fields=..., conds=0x7f24120ec0a0, order=0x0, group=0x0, hidden_group_fields=0x7f24120ebf48) at sql/sql_select.cc:577
 
#15 0x0000000000664b31 in JOIN::prepare (this=0x7f24120ebc18, rref_pointer_array=0x7f241204e6d8, tables_init=0x7f24120ea418, wild_num=0, conds_init=0x7f24120451d8, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f241204e418, unit_arg=0x7f2412028818) at sql/sql_select.cc:727
 
#16 0x000000000088d8a2 in subselect_single_select_engine::prepare (this=0x7f2412014158) at sql/item_subselect.cc:3025
 
#17 0x0000000000886181 in Item_subselect::fix_fields (this=0x7f241207a798, thd_param=0x7f2412d18000, ref=0x7f2412016458) at sql/item_subselect.cc:245
 
#18 0x00000000005e07f7 in setup_fields (thd=0x7f2412d18000, ref_pointer_array=0x7f241200e798, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f24120eb978, allow_sum_func=true) at sql/sql_base.cc:8166
 
#19 0x0000000000664a79 in JOIN::prepare (this=0x7f24120eb618, rref_pointer_array=0x7f2412d1bce0, tables_init=0x7f24120eb018, wild_num=0, conds_init=0x0, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x7f24120141b8, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f2412d1ba20, unit_arg=0x7f2412d1b320) at sql/sql_select.cc:723
 
#20 0x000000000066d415 in mysql_select (thd=0x7f2412d18000, rref_pointer_array=0x7f2412d1bce0, tables=0x7f24120eb018, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7f24120141b8, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2412044298, unit=0x7f2412d1b320, select_lex=0x7f2412d1ba20) at sql/sql_select.cc:3074
 
#21 0x0000000000663d47 in handle_select (thd=0x7f2412d18000, lex=0x7f2412d1b270, result=0x7f2412044298, setup_tables_done_option=0) at sql/sql_select.cc:319
 
#22 0x000000000063c177 in execute_sqlcom_select (thd=0x7f2412d18000, all_tables=0x7f24120eb018) at sql/sql_parse.cc:4689
 
#23 0x0000000000635350 in mysql_execute_command (thd=0x7f2412d18000) at sql/sql_parse.cc:2234
 
#24 0x000000000063ec7c in mysql_parse (thd=0x7f2412d18000, rawbuf=0x7f241205e118 "SELECT\n(SELECT user FROM mysql.user\nWHERE h.host in (SELECT host FROM mysql.user)\n) AS sq\nFROM mysql.host h GROUP BY h.host", length=123, parser_state=0x7f2412bb5610) at sql/sql_parse.cc:5909
 
#25 0x0000000000632897 in dispatch_command (command=COM_QUERY, thd=0x7f2412d18000, packet=0x7f2412d8c001 "SELECT\n(SELECT user FROM mysql.user\nWHERE h.host in (SELECT host FROM mysql.user)\n) AS sq\nFROM mysql.host h GROUP BY h.host", packet_length=123) at sql/sql_parse.cc:1079
 
#26 0x0000000000631a23 in do_command (thd=0x7f2412d18000) at sql/sql_parse.cc:793
 
#27 0x0000000000739a04 in do_handle_one_connection (thd_arg=0x7f2412d18000) at sql/sql_connect.cc:1266
 
#28 0x00000000007394c3 in handle_one_connection (arg=0x7f2412d18000) at sql/sql_connect.cc:1181
 
#29 0x0000000000b8335b in pfs_spawn_thread (arg=0x7f2416fed850) at storage/perfschema/pfs.cc:1015
 
#30 0x00007f2418e9cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
 
#31 0x00007f24177aa70d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
 

revision-id: sanja@askmonty.org-20150213125837-0dcd06la410e59io
 
date: 2015-02-13 13:58:37 +0100
 
build-date: 2015-03-21 23:37:39 +0300
 
revno: 4425
 
branch-nick: 5.5-MDEV-7445-sanja
 
cmake . -DCMAKE_BUILD_TYPE=Debug -DWITH_VALGRIND=YES && make -j3

Comment by Oleksandr Byelkin [ 2015-03-22 ]

The fix is pushed in the test tree...

Comment by Elena Stepanova [ 2015-03-26 ]

See MDEV-7846, might be related.

Comment by Elena Stepanova [ 2015-03-31 ]

MDEV-7846 was the last one that I've got during tests for this issue. If you decide to re-implement the fix because of it, please assign it back to me so I re-run some tests. If you decide to treat MDEV-7846 as a separate issue, please push the patch for MDEV-7445 into the main tree.

Comment by Oleksandr Byelkin [ 2015-04-15 ]

waiting review for MDEV-7846

Generated at Thu Feb 08 07:19:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.