Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5105

crash in make_join_statistics with multi-table update involving view

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0.4, 5.3.12, 5.5.33a
    • 5.5.34, 10.0.6, 5.3.13
    • None
    • None

    Description

      Version: '10.0.4-MariaDB'  mariadb.org binary distribution
      [ERROR] mysqld got exception 0xc0000005 ;
       
      mysqld.exe!make_join_statistics()[sql_select.cc:3740]
      mysqld.exe!JOIN::optimize_inner()[sql_select.cc:1303]
      mysqld.exe!JOIN::optimize()[sql_select.cc:1010]
      mysqld.exe!mysql_select()[sql_select.cc:3208]
      mysqld.exe!mysql_multi_update()[sql_update.cc:1414]
      mysqld.exe!mysql_execute_command()[sql_parse.cc:3309]
      mysqld.exe!mysql_parse()[sql_parse.cc:6269]
      mysqld.exe!dispatch_command()[sql_parse.cc:1280]
      mysqld.exe!do_command()[sql_parse.cc:989]
      mysqld.exe!threadpool_process_request()[threadpool_common.cc:232]
      mysqld.exe!io_completion_callback()[threadpool_win.cc:568]

      How to repeat:

      drop table if exists a,b,c,d;
      create table a(a int,b tinyint,c tinyint)engine=innodb;
      create table b(a tinyint,b float,c int,key b(b))engine=innodb;
      create table d(a int,b int,c int,key(a),key(b),key(c))engine=myisam;
      create or replace view c as select b.b a,a.b b from a,b where a.a=b.a;
      update `d` natural join `c` set a:=1;

      Attachments

        Activity

          The crash appeared on 10.0-base tree with the following revision:

          revno: 3607 [merge]
          revision-id: igor@askmonty.org-20130416055118-tivkzx4pngd2mkh0
          branch nick: maria-10.0-base-merge
          timestamp: Mon 2013-04-15 22:51:18 -0700
          message:
          Merge mwl253 -> 10.0-base

          elenst Elena Stepanova added a comment - The crash appeared on 10.0-base tree with the following revision: revno: 3607 [merge] revision-id: igor@askmonty.org-20130416055118-tivkzx4pngd2mkh0 branch nick: maria-10.0-base-merge timestamp: Mon 2013-04-15 22:51:18 -0700 message: Merge mwl253 -> 10.0-base

          The problem can be reproduced in 5.3 with the following modification of the original test case:

          create table t1(a int,b tinyint,c tinyint)engine=myisam;
          create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam;
          create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam;
          create view v as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a;
          update t3 natural join v set a:=1;
          drop view v;
          drop table t1,t2,t3;

          igor Igor Babaev (Inactive) added a comment - The problem can be reproduced in 5.3 with the following modification of the original test case: create table t1(a int,b tinyint,c tinyint)engine=myisam; create table t2(a tinyint,b float,c int, d int, e int, f int, key (b), key(c), key(d), key(e), key(f))engine=myisam; create table t3(a int,b int,c int, d int, e int, f int, key(a), key(b), key(c), key(d), key(e), key(f))engine=myisam; create view v as select t2.b a, t1.b b, t2.c c, t2.d d, t2.e e, t2.f f from t1,t2 where t1.a=t2.a; update t3 natural join v set a:=1; drop view v; drop table t1,t2,t3;
          aakso Anton Aksola added a comment -

          I'm also experiencing crashes in make_join_statistics. In my case it is happening when query is used in procedure and when the cursor is opened. I'm not sure if it's the exact same bug. The issue occurs only when the procedure is executed the second time.

          The issue can be reproduced without any data in the tables. Please see http://pastebin.com/PZU1igsV for the full schema and procedure.

          -to reproduce-
          MariaDB [staging]> call update_item_names_by_oid('TEST', 'TEST');
          Query OK, 0 rows affected, 1 warning (0.02 sec)

          MariaDB [staging]> call update_item_names_by_oid('TEST', 'TEST');
          ERROR 2013 (HY000): Lost connection to MySQL server during query

          -mysqld.log-
          131008 11:00:07 [ERROR] mysqld got signal 11 ;
          Server version: 5.5.33a-MariaDB-log
          key_buffer_size=67108864
          read_buffer_size=104857600
          max_used_connections=1
          max_threads=514
          thread_count=1
          It is possible that mysqld could use up to
          key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 322192329 K bytes of memory
          Hope that's ok; if not, decrease some variables in the equation.

          Thread pointer: 0x0x7f5760718000
          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 = 0x7f57445aed58 thread_stack 0x48000
          ??:0(my_print_stacktrace)[0xa896be]
          ??:0(handle_fatal_signal)[0x6d00bb]
          :0()[0x7f5c5c75c500]
          ??:0(make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*))[0x5d1c59]
          ??:0(JOIN::optimize())[0x5d253f]
          ??:0(st_select_lex::optimize_unflattened_subqueries(bool))[0x57d970]
          ??:0(JOIN::optimize())[0x5d417f]
          ??:0(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x56905d]
          ??:0(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x569e9d]
          ??:0(st_select_lex::handle_derived(LEX*, unsigned int))[0x57b687]
          ??:0(JOIN::optimize())[0x5d1ea3]
          ??:0(mysql_select(THD*, Item**, TABLE_LIST, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5da342]
          ??:0(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5db033]
          ??:0(execute_sqlcom_select(THD*, TABLE_LIST*))[0x58595c]
          ??:0(mysql_execute_command(THD*))[0x58c0f6]
          ??:0(mysql_open_cursor(THD*, select_result*, Server_side_cursor**))[0x7ef625]
          ??:0(sp_cursor::open(THD*))[0x7e0718]
          ??:0(sp_instr_copen::exec_core(THD*, unsigned int*))[0x7d715d]
          ??:0(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x7dc520]
          ??:0(sp_instr_copen::execute(THD*, unsigned int*))[0x7dc83a]
          ??:0(sp_head::execute(THD*, bool))[0x7d9e39]
          ??:0(sp_head::execute_procedure(THD*, List<Item>*))[0x7dab87]
          ??:0(mysql_execute_command(THD*))[0x58c538]
          ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58ee19]
          ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x590270]
          ??:0(do_handle_one_connection(THD*))[0x649caf]
          ??:0(handle_one_connection)[0x649d4c]
          :0()[0x7f5c5c754851]
          :0()[0x7f5c5b0ca6dd]

          Trying to get some variables.
          Some pointers may be invalid and cause the dump to abort.
          Query (0x7f5757020018): is an invalid pointer
          Connection ID (thread ID): 1
          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=off

          aakso Anton Aksola added a comment - I'm also experiencing crashes in make_join_statistics. In my case it is happening when query is used in procedure and when the cursor is opened. I'm not sure if it's the exact same bug. The issue occurs only when the procedure is executed the second time. The issue can be reproduced without any data in the tables. Please see http://pastebin.com/PZU1igsV for the full schema and procedure. - to reproduce - MariaDB [staging] > call update_item_names_by_oid('TEST', 'TEST'); Query OK, 0 rows affected, 1 warning (0.02 sec) MariaDB [staging] > call update_item_names_by_oid('TEST', 'TEST'); ERROR 2013 (HY000): Lost connection to MySQL server during query - mysqld.log - 131008 11:00:07 [ERROR] mysqld got signal 11 ; Server version: 5.5.33a-MariaDB-log key_buffer_size=67108864 read_buffer_size=104857600 max_used_connections=1 max_threads=514 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 322192329 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x7f5760718000 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 = 0x7f57445aed58 thread_stack 0x48000 ??:0(my_print_stacktrace) [0xa896be] ??:0(handle_fatal_signal) [0x6d00bb] :0( ) [0x7f5c5c75c500] ??:0(make_join_statistics(JOIN*, List<TABLE_LIST>&, Item*, st_dynamic_array*)) [0x5d1c59] ??:0(JOIN::optimize()) [0x5d253f] ??:0(st_select_lex::optimize_unflattened_subqueries(bool)) [0x57d970] ??:0(JOIN::optimize()) [0x5d417f] ??:0(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*)) [0x56905d] ??:0(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int)) [0x569e9d] ??:0(st_select_lex::handle_derived(LEX*, unsigned int)) [0x57b687] ??:0(JOIN::optimize()) [0x5d1ea3] ??:0(mysql_select(THD*, Item** , TABLE_LIST , unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)) [0x5da342] ??:0(handle_select(THD*, LEX*, select_result*, unsigned long)) [0x5db033] ??:0(execute_sqlcom_select(THD*, TABLE_LIST*)) [0x58595c] ??:0(mysql_execute_command(THD*)) [0x58c0f6] ??:0(mysql_open_cursor(THD*, select_result*, Server_side_cursor**)) [0x7ef625] ??:0(sp_cursor::open(THD*)) [0x7e0718] ??:0(sp_instr_copen::exec_core(THD*, unsigned int*)) [0x7d715d] ??:0(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*)) [0x7dc520] ??:0(sp_instr_copen::execute(THD*, unsigned int*)) [0x7dc83a] ??:0(sp_head::execute(THD*, bool)) [0x7d9e39] ??:0(sp_head::execute_procedure(THD*, List<Item>*)) [0x7dab87] ??:0(mysql_execute_command(THD*)) [0x58c538] ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*)) [0x58ee19] ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int)) [0x590270] ??:0(do_handle_one_connection(THD*)) [0x649caf] ??:0(handle_one_connection) [0x649d4c] :0( ) [0x7f5c5c754851] :0( ) [0x7f5c5b0ca6dd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f5757020018): is an invalid pointer Connection ID (thread ID): 1 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=off

          The bug was fixed in the 5.3 tree in the revisions 3702,3703,3704.

          igor Igor Babaev (Inactive) added a comment - The bug was fixed in the 5.3 tree in the revisions 3702,3703,3704.

          People

            igor Igor Babaev (Inactive)
            sbester1 sbester1
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.