[MDEV-5531] double call procedure in one session - hard shutdown the server Created: 2014-01-15  Updated: 2014-04-09  Resolved: 2014-01-27

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.34, 10.0.7
Fix Version/s: 5.5.35, 10.0.8

Type: Bug Priority: Major
Reporter: Pavel Tishkin Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None
Environment:

windows 7, 64x
mysqld 64x

Issue Links:
Relates
relates to MDEV-6054 ER_BAD_FIELD_ERROR (Unknown column in... Stalled

 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

 Comments   
Comment by Elena Stepanova [ 2014-01-16 ]

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 ?? ()

Comment by Michael Widenius [ 2014-01-23 ]

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.
Comment by Michael Widenius [ 2014-01-25 ]

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

Comment by Pavel Tishkin [ 2014-01-27 ]

But, what about other versions?

Comment by Michael Widenius [ 2014-01-27 ]

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

Comment by Elena Stepanova [ 2014-01-27 ]

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.

Comment by Pavel Tishkin [ 2014-01-28 ]

Thanks a lot

Comment by Daniel Bartholomew [ 2014-01-29 ]

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

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