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

double call procedure in one session - hard shutdown the server

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.34, 10.0.7
    • 5.5.35, 10.0.8
    • None
    • None
    • windows 7, 64x
      mysqld 64x

    Description

      This problem exists if in table more than 10 rows
      eg.

      CREATE TABLE  `action` (
      		 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `create_ts` int(10) unsigned DEFAULT '0',
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
      		 
       
      		 
      # and my test for this table
      		 
      # 1) creating routine
      		 
       
      		 
      DELIMITER $$
      		 
      DROP PROCEDURE IF EXISTS `p` $$
      		 
      CREATE PROCEDURE p (IN step TINYINT(1))
      		 
      BEGIN
      		 
        DECLARE counts INT DEFAULT 0;
      		 
        DECLARE cur1 CURSOR FOR
      		 
       
      		 
        SELECT ct.id
      		 
        FROM (SELECT NULL) AS z
      		 
        JOIN (
      		 
          SELECT id
      		 
          FROM `action`
      		 
          LIMIT 10
      		 
        ) AS ct
      		 
        JOIN (SELECT NULL) AS x ON(
      		 
          EXISTS(
      		 
            SELECT 1
      		 
            FROM `action`
      		 
            WHERE id=ct.id
      		 
            LIMIT 1
      		 
          )
      		 
        );
      		 
       
      		 
        IF step=1 THEN
      		 
          TRUNCATE action;
      		 
          REPEAT
      		 
            INSERT INTO `action`
      		 
            (create_ts) VALUES
      		 
            (UNIX_TIMESTAMP());
      		 
       
      		 
            SET counts=counts+1;
      		 
          UNTIL counts>150 END REPEAT;
      		 
       
      		 
          SET max_sp_recursion_depth=1;
      		 
       
      		 
          CALL p(2);
      		 
          CALL p(2);
      		 
        ELSEIF step=2 THEN
      		 
          OPEN cur1; CLOSE cur1;
      		 
        END IF;
      		 
      END $$
      		 
      DELIMITER ;
      		 
       
      CALL p(1);

      And as result - Lost connection to MySQL server during query

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6054 ER_BAD_FIELD_ERROR (Unknown column in where clause) on the 2nd execution of PS

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment - - edited

            Thank you for the report and the test case.

            #3  <signal handler called>
            		 
            #4  0x000000000069260f in optimize_keyuse (join=0x7feea47f17e0, keyuse_array=0x7feea47f1ae8) at 10.0/sql/sql_select.cc:5350
            		 
            #5  0x000000000068ebd2 in make_join_statistics (join=0x7feea47f17e0, tables_list=..., conds=0x7feea47efbc8, keyuse_array=0x7feea47f1ae8) at 10.0/sql/sql_select.cc:3985
            		 
            #6  0x00000000006856db in JOIN::optimize_inner (this=0x7feea47f17e0) at 10.0/sql/sql_select.cc:1328
            		 
            #7  0x000000000068460a in JOIN::optimize (this=0x7feea47f17e0) at 10.0/sql/sql_select.cc:1013
            		 
            #8  0x0000000000643493 in st_select_lex::optimize_unflattened_subqueries (this=0x7feea4544828, const_only=false) at 10.0/sql/sql_lex.cc:3480
            		 
            #9  0x00000000007bd24c in JOIN::optimize_unflattened_subqueries (this=0x7feea47f1278) at 10.0/sql/opt_subselect.cc:4951
            		 
            #10 0x00000000006870fd in JOIN::optimize_inner (this=0x7feea47f1278) at 10.0/sql/sql_select.cc:1772
            		 
            #11 0x000000000068460a in JOIN::optimize (this=0x7feea47f1278) at 10.0/sql/sql_select.cc:1013
            		 
            #12 0x000000000068c473 in mysql_select (thd=0x7feea63ec070, rref_pointer_array=0x7feea4544a98, tables=0x7feea4545a70, wild_num=0, fields=..., conds=0x7feea454eca0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7feea47f10d8, unit=0x7feea4544148, select_lex=0x7feea4544828) at 10.0/sql/sql_select.cc:3280
            		 
            #13 0x0000000000682960 in handle_select (thd=0x7feea63ec070, lex=0x7feea4544088, result=0x7feea47f10d8, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372
            		 
            #14 0x00000000006566c0 in execute_sqlcom_select (thd=0x7feea63ec070, all_tables=0x7feea4545a70) at 10.0/sql/sql_parse.cc:5250
            		 
            #15 0x000000000064e7cc in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:2571
            		 
            #16 0x00000000009962b2 in mysql_open_cursor (thd=0x7feea63ec070, result=0x7feea474d510, pcursor=0x7feea474d540) at 10.0/sql/sql_cursor.cc:123
            		 
            #17 0x0000000000986f0c in sp_cursor::open (this=0x7feea474d508, thd=0x7feea63ec070) at 10.0/sql/sp_rcontext.cc:456
            		 
            #18 0x0000000000981cdf in sp_instr_copen::exec_core (this=0x7feea47c3f70, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8) at 10.0/sql/sp_head.cc:3773
            		 
            #19 0x00000000009800bb in sp_lex_keeper::reset_lex_and_exec_core (this=0x7feea454f480, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8, open_tables=false, instr=0x7feea47c3f70) at 10.0/sql/sp_head.cc:2954
            		 
            #20 0x0000000000981c44 in sp_instr_copen::execute (this=0x7feea47c3f70, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8) at 10.0/sql/sp_head.cc:3758
            		 
            #21 0x000000000097c252 in sp_head::execute (this=0x7feea474f088, thd=0x7feea63ec070, merge_da_on_success=true) at 10.0/sql/sp_head.cc:1360
            		 
            #22 0x000000000097e060 in sp_head::execute_procedure (this=0x7feea474f088, thd=0x7feea63ec070, args=0x7feea45ecdd8) at 10.0/sql/sp_head.cc:2147
            		 
            #23 0x00000000006548ef in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:4662
            		 
            #24 0x0000000000980962 in sp_instr_stmt::exec_core (this=0x7feea45e2ed8, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8) at 10.0/sql/sp_head.cc:3184
            		 
            #25 0x00000000009800bb in sp_lex_keeper::reset_lex_and_exec_core (this=0x7feea45e2f18, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8, open_tables=false, instr=0x7feea45e2ed8) at 10.0/sql/sp_head.cc:2954
            		 
            #26 0x0000000000980674 in sp_instr_stmt::execute (this=0x7feea45e2ed8, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8) at 10.0/sql/sp_head.cc:3103
            		 
            #27 0x000000000097c252 in sp_head::execute (this=0x7feea45bc088, thd=0x7feea63ec070, merge_da_on_success=true) at 10.0/sql/sp_head.cc:1360
            		 
            #28 0x000000000097e060 in sp_head::execute_procedure (this=0x7feea45bc088, thd=0x7feea63ec070, args=0x7feea63f09c8) at 10.0/sql/sp_head.cc:2147
            		 
            #29 0x00000000006548ef in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:4662
            		 
            #30 0x0000000000658f26 in mysql_parse (thd=0x7feea63ec070, rawbuf=0x7feea4503088 "CALL p(1)", length=9, parser_state=0x7feeaec9c4b0) at 10.0/sql/sql_parse.cc:6394
            		 
            #31 0x000000000064b8a8 in dispatch_command (command=COM_QUERY, thd=0x7feea63ec070, packet=0x7feea6f35071 "CALL p(1)", packet_length=9) at 10.0/sql/sql_parse.cc:1296
            		 
            #32 0x000000000064abf6 in do_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:993
            		 
            #33 0x000000000076b705 in do_handle_one_connection (thd_arg=0x7feea63ec070) at 10.0/sql/sql_connect.cc:1379
            		 
            #34 0x000000000076b458 in handle_one_connection (arg=0x7feea63ec070) at 10.0/sql/sql_connect.cc:1293
            		 
            #35 0x0000000000a1219b in pfs_spawn_thread (arg=0x7feea625b850) at 10.0/storage/perfschema/pfs.cc:1853
            		 
            #36 0x00007feeae073e9a in start_thread (arg=0x7feeaec9d700) at pthread_create.c:308
            		 
            #37 0x00007feeacf673fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
            		 
            #38 0x0000000000000000 in ?? ()

            elenst Elena Stepanova added a comment - - edited Thank you for the report and the test case. #3 <signal handler called> #4 0x000000000069260f in optimize_keyuse (join=0x7feea47f17e0, keyuse_array=0x7feea47f1ae8) at 10.0/sql/sql_select.cc:5350 #5 0x000000000068ebd2 in make_join_statistics (join=0x7feea47f17e0, tables_list=..., conds=0x7feea47efbc8, keyuse_array=0x7feea47f1ae8) at 10.0/sql/sql_select.cc:3985 #6 0x00000000006856db in JOIN::optimize_inner (this=0x7feea47f17e0) at 10.0/sql/sql_select.cc:1328 #7 0x000000000068460a in JOIN::optimize (this=0x7feea47f17e0) at 10.0/sql/sql_select.cc:1013 #8 0x0000000000643493 in st_select_lex::optimize_unflattened_subqueries (this=0x7feea4544828, const_only=false) at 10.0/sql/sql_lex.cc:3480 #9 0x00000000007bd24c in JOIN::optimize_unflattened_subqueries (this=0x7feea47f1278) at 10.0/sql/opt_subselect.cc:4951 #10 0x00000000006870fd in JOIN::optimize_inner (this=0x7feea47f1278) at 10.0/sql/sql_select.cc:1772 #11 0x000000000068460a in JOIN::optimize (this=0x7feea47f1278) at 10.0/sql/sql_select.cc:1013 #12 0x000000000068c473 in mysql_select (thd=0x7feea63ec070, rref_pointer_array=0x7feea4544a98, tables=0x7feea4545a70, wild_num=0, fields=..., conds=0x7feea454eca0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147749632, result=0x7feea47f10d8, unit=0x7feea4544148, select_lex=0x7feea4544828) at 10.0/sql/sql_select.cc:3280 #13 0x0000000000682960 in handle_select (thd=0x7feea63ec070, lex=0x7feea4544088, result=0x7feea47f10d8, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372 #14 0x00000000006566c0 in execute_sqlcom_select (thd=0x7feea63ec070, all_tables=0x7feea4545a70) at 10.0/sql/sql_parse.cc:5250 #15 0x000000000064e7cc in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:2571 #16 0x00000000009962b2 in mysql_open_cursor (thd=0x7feea63ec070, result=0x7feea474d510, pcursor=0x7feea474d540) at 10.0/sql/sql_cursor.cc:123 #17 0x0000000000986f0c in sp_cursor::open (this=0x7feea474d508, thd=0x7feea63ec070) at 10.0/sql/sp_rcontext.cc:456 #18 0x0000000000981cdf in sp_instr_copen::exec_core (this=0x7feea47c3f70, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8) at 10.0/sql/sp_head.cc:3773 #19 0x00000000009800bb in sp_lex_keeper::reset_lex_and_exec_core (this=0x7feea454f480, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8, open_tables=false, instr=0x7feea47c3f70) at 10.0/sql/sp_head.cc:2954 #20 0x0000000000981c44 in sp_instr_copen::execute (this=0x7feea47c3f70, thd=0x7feea63ec070, nextp=0x7feeaec9a6a8) at 10.0/sql/sp_head.cc:3758 #21 0x000000000097c252 in sp_head::execute (this=0x7feea474f088, thd=0x7feea63ec070, merge_da_on_success=true) at 10.0/sql/sp_head.cc:1360 #22 0x000000000097e060 in sp_head::execute_procedure (this=0x7feea474f088, thd=0x7feea63ec070, args=0x7feea45ecdd8) at 10.0/sql/sp_head.cc:2147 #23 0x00000000006548ef in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:4662 #24 0x0000000000980962 in sp_instr_stmt::exec_core (this=0x7feea45e2ed8, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8) at 10.0/sql/sp_head.cc:3184 #25 0x00000000009800bb in sp_lex_keeper::reset_lex_and_exec_core (this=0x7feea45e2f18, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8, open_tables=false, instr=0x7feea45e2ed8) at 10.0/sql/sp_head.cc:2954 #26 0x0000000000980674 in sp_instr_stmt::execute (this=0x7feea45e2ed8, thd=0x7feea63ec070, nextp=0x7feeaec9b6d8) at 10.0/sql/sp_head.cc:3103 #27 0x000000000097c252 in sp_head::execute (this=0x7feea45bc088, thd=0x7feea63ec070, merge_da_on_success=true) at 10.0/sql/sp_head.cc:1360 #28 0x000000000097e060 in sp_head::execute_procedure (this=0x7feea45bc088, thd=0x7feea63ec070, args=0x7feea63f09c8) at 10.0/sql/sp_head.cc:2147 #29 0x00000000006548ef in mysql_execute_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:4662 #30 0x0000000000658f26 in mysql_parse (thd=0x7feea63ec070, rawbuf=0x7feea4503088 "CALL p(1)", length=9, parser_state=0x7feeaec9c4b0) at 10.0/sql/sql_parse.cc:6394 #31 0x000000000064b8a8 in dispatch_command (command=COM_QUERY, thd=0x7feea63ec070, packet=0x7feea6f35071 "CALL p(1)", packet_length=9) at 10.0/sql/sql_parse.cc:1296 #32 0x000000000064abf6 in do_command (thd=0x7feea63ec070) at 10.0/sql/sql_parse.cc:993 #33 0x000000000076b705 in do_handle_one_connection (thd_arg=0x7feea63ec070) at 10.0/sql/sql_connect.cc:1379 #34 0x000000000076b458 in handle_one_connection (arg=0x7feea63ec070) at 10.0/sql/sql_connect.cc:1293 #35 0x0000000000a1219b in pfs_spawn_thread (arg=0x7feea625b850) at 10.0/storage/perfschema/pfs.cc:1853 #36 0x00007feeae073e9a in start_thread (arg=0x7feeaec9d700) at pthread_create.c:308 #37 0x00007feeacf673fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112 #38 0x0000000000000000 in ?? ()
            monty Michael Widenius added a comment -

            This was actually 2 bugs:

            • One structure element was not properly reset in make_join_statistics()
            • We cached in this case fields to temporary tables what was later deleted.
            monty Michael Widenius added a comment - This was actually 2 bugs: One structure element was not properly reset in make_join_statistics() We cached in this case fields to temporary tables what was later deleted.
            monty Michael Widenius added a comment -

            Fixed by removing caching for temporary tables that will be deleted for next exection

            monty Michael Widenius added a comment - Fixed by removing caching for temporary tables that will be deleted for next exection
            scotovod Pavel Tishkin added a comment -

            But, what about other versions?

            scotovod Pavel Tishkin added a comment - But, what about other versions?
            monty Michael Widenius added a comment -

            I have for the moment only pushed this in 5.5, as the fix was rather complex and could in theory have unwanted side effects.

            If prefer to have this first in 5.5 for a while and if things works out (it should, but one never knows) then I can consider back porting it to 5.3 if there is demand for it.

            I know that a crashing bug is bad, but this is still a bit unusual case so better to not cause other problems for normal cases

            monty Michael Widenius added a comment - I have for the moment only pushed this in 5.5, as the fix was rather complex and could in theory have unwanted side effects. If prefer to have this first in 5.5 for a while and if things works out (it should, but one never knows) then I can consider back porting it to 5.3 if there is demand for it. I know that a crashing bug is bad, but this is still a bit unusual case so better to not cause other problems for normal cases
            elenst Elena Stepanova added a comment -

            And for 10.0, the fix will be merged up to 10.0 tree as usual and will appear in the next release (10.0.8). Re-opening just to correct the 'Fix Version/s' field.

            elenst Elena Stepanova added a comment - And for 10.0, the fix will be merged up to 10.0 tree as usual and will appear in the next release (10.0.8). Re-opening just to correct the 'Fix Version/s' field.
            scotovod Pavel Tishkin added a comment -

            Thanks a lot

            scotovod Pavel Tishkin added a comment - Thanks a lot
            dbart Daniel Bartholomew added a comment -

            http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4030

            dbart Daniel Bartholomew added a comment - http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/4030

            People

              monty Michael Widenius
              scotovod Pavel Tishkin
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.