MySQL Bugs: #11060: Server crashe on calling stored procedure with INSERT SELECT UNION SELECT

Bug #11060	Server crashe on calling stored procedure with INSERT SELECT UNION SELECT
Submitted:	3 Jun 2005 6:04	Modified:	21 Jun 2005 16:33
Reporter:	Dmitry L	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.6	OS:	Windows (win2000)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
When calling procedure with (insert.. select union select) server crashes.
I don't know what is it and i wasn't able to reproduce this with less compex testcase, but the thing is that when i replace UNION with 2 different inserts everything goes well;

Here is the procedure text, i commented the workaround

How to repeat:
/*-----------------*/
CREATE TABLE `objects_hier` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `map` smallint(6) unsigned NOT NULL default '0',
  `mapped_from` smallint(6) unsigned default NULL,
  `lnk4` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`,`map`),
  KEY `pid` (`pid`),
  KEY `id` (`id`,`mapped_from`),
  KEY `lnk4` (`lnk4`,`map`),
  CONSTRAINT `objects_hier_mf` FOREIGN KEY (`id`, `mapped_from`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE,
  CONSTRAINT `objects_hier_lnk` FOREIGN KEY (`lnk4`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `objects_path` (
  `id` int(10) unsigned NOT NULL default '0',
  `pid` int(10) unsigned NOT NULL default '0',
  `map` smallint(6) unsigned NOT NULL default '0',
  `level` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`,`map`,`pid`),
  CONSTRAINT `objects_path_map` FOREIGN KEY (`id`, `map`) REFERENCES `objects_hier` (`id`, `map`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tmp` (
  `id` int(10) NOT NULL,
  `pid` int(10) NOT NULL,
  `map` smallint(6) NOT NULL,
  `ex` int(1) NOT NULL,
  `level` smallint(6) NOT NULL,
  PRIMARY KEY  (`id`,`map`),
  KEY `pid` (`pid`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
/*-----------------*/
DELIMITER $$

DROP PROCEDURE IF EXISTS `parallax_kernel`.`regenPath`$$
CREATE PROCEDURE `parallax_kernel`.`regenPath`(root INTEGER(11))
    SQL SECURITY INVOKER
BEGIN
  DECLARE iter, rc INT DEFAULT 0;
  DELETE FROM tmp;
  INSERT INTO tmp
  SELECT id, pid, map, 0, IFNULL((SELECT level FROM objects_path WHERE id = pid AND id = oh.id AND map = oh.map), 0)
    FROM objects_hier oh
   WHERE id = root;

  DELETE op FROM objects_path op, tmp WHERE op.id = tmp.id AND op.map = tmp.map;

  st: LOOP
    SET iter = iter + 1;
/*    INSERT INTO objects_path(id, pid, map, level)
    SELECT tmp.id, p.pid, p.map, p.level
      FROM objects_path p,
           tmp
     WHERE p.id = tmp.pid AND p.map = tmp.map;
    SET rc = ROW_COUNT();
    INSERT INTO objects_path(id, pid, map, level)
    SELECT p.id, p.id, p.map, tmp.level
      FROM objects_hier p,
           tmp
     WHERE p.id = tmp.id AND p.map = tmp.map;
    SET rc = rc + ROW_COUNT();
    INSERT INTO debug VALUES(rc);*/
    INSERT INTO objects_path(id, pid, map, level)
    SELECT tmp.id, p.pid, p.map, p.level
      FROM objects_path p,
           tmp
     WHERE p.id = tmp.pid AND p.map = tmp.map
    UNION
    SELECT p.id, p.id, p.map, tmp.level
      FROM objects_hier p,
           tmp
     WHERE p.id = tmp.id AND p.map = tmp.map;

    INSERT INTO debug VALUES(ROW_COUNT());

    IF ROW_COUNT() = 0 OR iter > 12 THEN
      LEAVE st;
    END IF;

    UPDATE tmp SET ex = 1;
    INSERT INTO tmp
    SELECT oh.id, oh.pid, oh.map, 0, tmp.level + 1
      FROM objects_hier oh,
           tmp
     WHERE oh.pid = tmp.id AND oh.map = tmp.map;

    DELETE FROM tmp WHERE ex = 1;
    DELETE op FROM objects_path op, tmp WHERE op.id = tmp.id AND op.map = tmp.map;
  END LOOP;
END$$

DELIMITER ;

Hi,

I wasn't able to get the server to crash using 5.0.7 from bk, but I did receive the following results:

5.0.4 - Win32:

mysql> call regenPath(1234);
Query OK, 1 row affected (0.02 sec)
mysql>

5.0.7 bk - Linux:
mysql> call regenPath(15);
ERROR 1099 (HY000): Table 'op' was locked with a READ lock and can't be updated
mysql>

Hi!

Did mysqld print anything to the .err log?

Regards,

Heikki

Crash occures when data present in objects_hier table;
please use 1 as param for calling;
here is data dump that is  enough for reproducing;

INSERT INTO `objects_hier` (`id`,`pid`,`map`,`mapped_from`,`lnk4`) VALUES
 (1,0,0,NULL,NULL),
 (1,0,1001,0,NULL),
 (1,0,1002,0,NULL),
 (1,0,1003,0,NULL),
 (1,0,1004,0,NULL),
 (1,0,1006,0,NULL),
 (2,1,0,NULL,NULL),
 (2,1,1001,0,NULL),
 (2,1,1002,0,NULL),
 (2,1,1003,0,NULL),
 (2,1,1004,0,NULL),
 (2,1,1006,0,NULL),
 (3,1,0,NULL,NULL),
 (3,1,1001,0,NULL),
 (3,1,1002,0,NULL),
 (3,1,1003,0,NULL),
 (3,1,1004,0,NULL),
 (3,1,1006,0,NULL),
 (4,1,0,NULL,NULL),
 (4,1,1001,0,NULL),
 (4,1,1002,0,NULL),
 (4,1,1003,0,NULL),
 (4,1,1004,0,NULL),
 (4,1,1006,0,NULL),
 (5,1,0,NULL,NULL),
 (5,1,1001,0,NULL),
 (5,1,1002,0,NULL),
 (5,1,1003,0,NULL),
 (5,1,1004,0,NULL),
 (5,1,1006,0,NULL),
 (6,1,0,NULL,NULL),
 (6,1,1001,0,NULL),
 (6,1,1002,0,NULL),
 (6,1,1003,0,NULL),
 (6,1,1004,0,NULL),
 (6,1,1006,0,NULL),
 (7,1,0,NULL,NULL),
 (7,1,1001,0,NULL),
 (7,1,1002,0,NULL),
 (7,1,1003,0,NULL),
 (7,1,1004,0,NULL),
 (7,1,1006,0,NULL),
 (8,1,0,NULL,NULL),
 (8,1,1001,0,NULL),
 (8,1,1002,0,NULL),
 (8,1,1003,0,NULL),
 (8,1,1004,0,NULL),
 (8,1,1006,0,NULL);
INSERT INTO `x` (`id`,`pid`,`map`,`mapped_from`,`lnk4`) VALUES
 (9,1,0,NULL,NULL),
 (9,1,1001,0,NULL),
 (9,1,1002,0,NULL),
 (9,1,1003,0,NULL),
 (9,1,1004,0,NULL),
 (9,1,1006,0,NULL),
 (10,1,0,NULL,NULL),
 (10,1,1001,0,NULL),
 (10,1,1002,0,NULL),
 (10,1,1003,0,NULL),
 (10,1,1004,0,NULL),
 (10,1,1006,0,NULL),
 (11,1,0,NULL,NULL),
 (11,1,1001,0,NULL),
 (11,1,1002,0,NULL),
 (11,1,1003,0,NULL),
 (11,1,1004,0,NULL),
 (11,1,1006,0,NULL),
 (12,1,0,NULL,NULL),
 (12,1,1001,0,NULL),
 (12,1,1002,0,NULL),
 (12,1,1003,0,NULL),
 (12,1,1004,0,NULL),
 (12,1,1006,0,NULL),
 (13,1,0,NULL,NULL),
 (13,1,1001,0,NULL),
 (13,1,1002,0,NULL),
 (13,1,1003,0,NULL),
 (13,1,1004,0,NULL),
 (13,1,1006,0,NULL),
 (14,1,0,NULL,NULL),
 (14,1,1001,0,NULL),
 (14,1,1002,0,NULL),
 (14,1,1003,0,NULL),
 (14,1,1004,0,NULL),
 (14,1,1006,0,NULL),
 (15,1,0,NULL,NULL),
 (15,1,1001,0,NULL),
 (15,1,1002,0,NULL),
 (15,1,1003,0,NULL),
 (15,1,1004,0,NULL),
 (15,1,1006,0,NULL),
 (16,1,0,NULL,NULL),
 (16,1,1001,0,NULL),
 (16,1,1002,0,NULL),
 (16,1,1003,0,NULL);
INSERT INTO `x` (`id`,`pid`,`map`,`mapped_from`,`lnk4`) VALUES 
 (16,1,1004,0,NULL),
 (16,1,1006,0,NULL),
 (17,1,0,NULL,NULL),
 (17,1,1001,0,NULL),
 (17,1,1002,0,NULL),
 (17,1,1003,0,NULL),
 (17,1,1004,0,NULL),
 (17,1,1006,0,NULL),
 (18,1,0,NULL,NULL),
 (18,1,1001,0,NULL),
 (18,1,1002,0,NULL),
 (18,1,1003,0,NULL),
 (18,1,1004,0,NULL),
 (18,1,1006,0,NULL),
 (19,1,0,NULL,NULL),
 (19,1,1001,0,NULL),
 (19,1,1002,0,NULL),
 (19,1,1003,0,NULL),
 (19,1,1004,0,NULL),
 (19,1,1006,0,NULL),
 (20,1,0,NULL,NULL),
 (20,1,1001,0,NULL),
 (20,1,1002,0,NULL),
 (20,1,1003,0,NULL),
 (20,1,1004,0,NULL),
 (20,1,1006,0,NULL),
 (21,1,0,NULL,NULL),
 (21,1,1001,0,NULL),
 (21,1,1002,0,NULL),
 (21,1,1003,0,NULL),
 (21,1,1004,0,NULL),
 (21,1,1006,0,NULL),
 (22,1,0,NULL,NULL),
 (22,1,1001,0,NULL),
 (22,1,1002,0,NULL),
 (22,1,1003,0,NULL),
 (22,1,1004,0,NULL),
 (22,1,1006,0,NULL),
 (10031,10034,1001,NULL,NULL),
 (10032,10034,1001,NULL,NULL),
 (10033,16,1001,NULL,NULL),
 (10034,16,1001,NULL,NULL),
 (10037,16,1001,NULL,NULL),
 (10048,16,1001,NULL,NULL);
INSERT INTO `x` (`id`,`pid`,`map`,`mapped_from`,`lnk4`) VALUES 
 (10049,16,1001,NULL,NULL),
 (10050,16,1001,NULL,NULL),
 (10051,16,1001,NULL,NULL),
 (10052,16,1001,NULL,NULL),
 (10112,10034,1001,NULL,NULL),
 (10113,10034,1001,NULL,NULL),
 (10114,10034,1001,NULL,NULL),
 (10133,16,1001,NULL,NULL),
 (10134,10034,1001,NULL,NULL),
 (10135,10034,1001,NULL,NULL),
 (10136,10034,1001,NULL,NULL),
 (10143,16,1001,NULL,NULL),
 (10144,16,1001,NULL,NULL),
 (10145,16,1001,NULL,NULL),
 (10146,16,1001,NULL,NULL),
 (10147,16,1001,NULL,NULL),
 (10148,16,1001,NULL,NULL),
 (10154,16,1001,NULL,NULL),
 (10187,16,1001,NULL,NULL),
 (10188,16,1001,NULL,NULL),
 (10189,16,1001,NULL,NULL),
 (10190,16,1001,NULL,NULL),
 (10191,16,1001,NULL,NULL),
 (10192,16,1001,NULL,NULL),
 (10193,10187,1001,NULL,NULL),
 (10194,10187,1001,NULL,NULL),
 (10195,10187,1001,NULL,NULL),
 (10196,10187,1001,NULL,NULL),
 (10197,10187,1001,NULL,NULL),
 (10198,10187,1001,NULL,NULL),
 (10199,10187,1001,NULL,NULL),
 (10200,10187,1001,NULL,NULL),
 (10201,10187,1001,NULL,NULL);
INSERT INTO `x` (`id`,`pid`,`map`,`mapped_from`,`lnk4`) VALUES 
 (10202,10187,1001,NULL,NULL),
 (10203,10187,1001,NULL,NULL),
 (10204,10187,1001,NULL,NULL),
 (10205,10187,1001,NULL,NULL),
 (10230,10034,1001,NULL,NULL),
 (10231,10034,1001,NULL,NULL),
 (10232,10034,1001,NULL,NULL),
 (10233,10034,1001,NULL,NULL),
 (10234,10034,1001,NULL,NULL),
 (10235,10034,1001,NULL,NULL),
 (10236,10231,1001,NULL,NULL),
 (10237,10231,1001,NULL,NULL),
 (10238,10231,1001,NULL,NULL),
 (10239,10238,1001,NULL,NULL),
 (10240,10238,1001,NULL,NULL),
 (10241,10238,1001,NULL,NULL),
 (10242,10241,1001,NULL,NULL),
 (10243,10241,1001,NULL,NULL),
 (10244,10241,1001,NULL,NULL),
 (10245,10244,1001,NULL,NULL),
 (10246,10245,1001,NULL,NULL),
 (10247,10246,1001,NULL,NULL);

No. Nothing in logs.

-- server ready
050603 10:41:33 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.6-beta-nt'  socket: ''  port: 3307  Official MySQL binary
--here i call procedure
--starting server after crash
050603 10:42:00  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.

a little mistype
i'm sorry, please replace 'x' with 'objects_hier' in my dump :))

Jorge del Conde, maybe you mistyped cause "readlock" issue was a Bug #9486  i posted (and it was fixed in 5.0.5+). I think, your win32 5.0.4 results are linux bk5.0.7 and vise versa.
Please, try the dump i posted and "1" as param :) I think, the crash will occure.
Thank a lot!

I was able to repeat the crash using data which Dmitry has provided with 5.0.6 release:

Version: '5.0.6-beta-debug'  socket: '/home/dlenev/mysql_data/mysql.sock'  port: 3306  Source distribution
[New Thread 163851 (LWP 7329)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 163851 (LWP 7329)]
0x08103f16 in mark_select_range_as_dependent (thd=0x8c700d8, last_select=0x8d23b64, current_sel=0x8d232f0, found_field=0x8cfb270, found_item=0x8d224c0, 
    resolved_item=0x8d224c0) at item.cc:2520
2520        prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
(gdb) bt
#0  0x08103f16 in mark_select_range_as_dependent (thd=0x8c700d8, last_select=0x8d23b64, current_sel=0x8d232f0, found_field=0x8cfb270, 
    found_item=0x8d224c0, resolved_item=0x8d224c0) at item.cc:2520
#1  0x081d1063 in find_field_in_tables (thd=0x8c700d8, item=0x8d224c0, tables=0x8d22360, ref=0x8d22544, report_error=IGNORE_EXCEPT_NON_UNIQUE, 
    check_privileges=true) at sql_base.cc:2701
#2  0x081045bb in Item_field::fix_fields (this=0x8d224c0, thd=0x8c700d8, tables=0x8d22360, reference=0x8d22544) at item.cc:2807
#3  0x081d1fd0 in setup_fields (thd=0x8c700d8, ref_pointer_array=0x0, tables=0x8d22360, fields=@0x8d23d90, set_query_id=true, sum_func_list=0x0, 
    allow_sum_func=false) at sql_base.cc:3141
#4  0x081fe341 in check_insert_fields (thd=0x8c700d8, table_list=0x8d22360, fields=@0x8d23d90, values=@0x8d23b1c, check_unique=true)
    at sql_insert.cc:121
#5  0x08202c7c in select_insert::prepare (this=0x8db5728, values=@0x8d23b1c, u=0x8d23980) at sql_insert.cc:1903
#6  0x081db55e in JOIN::prepare (this=0x8db97f8, rref_pointer_array=0x8d23400, tables_init=0x8d239b0, wild_num=0, conds_init=0x0, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8d232f0, unit_arg=0x8d23980) at sql_select.cc:484
#7  0x081e04dd in mysql_select (thd=0x8c700d8, rref_pointer_array=0x8d23400, tables=0x8d239b0, wild_num=0, fields=@0x8d23b1c, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=268566528, result=0x8db5728, unit=0x8d23980, select_lex=0x8d232f0)
    at sql_select.cc:2013
#8  0x082b9f07 in st_select_lex_unit::exec (this=0x8d23980) at sql_union.cc:548
#9  0x082b81d4 in mysql_union (thd=0x8c700d8, lex=0x8d23970, result=0x8db5728, unit=0x8d23980, setup_tables_done_option=1073741824) at sql_union.cc:34
#10 0x081da9ea in handle_select (thd=0x8c700d8, lex=0x8d23970, result=0x8db5728, setup_tables_done_option=1073741824) at sql_select.cc:232
#11 0x081a5c05 in mysql_execute_command (thd=0x8c700d8) at sql_parse.cc:3190
#12 0x082d6011 in sp_instr_stmt::exec_core (this=0x8d2cd40, thd=0x8c700d8, nextp=0xbe5feff4) at sp_head.cc:1449
#13 0x082d5da4 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8d2cd5c, thd=0x8c700d8, nextp=0xbe5feff4, open_tables=false, instr=0x8d2cd40)
    at sp_head.cc:1368
#14 0x082d5f52 in sp_instr_stmt::execute (this=0x8d2cd40, thd=0x8c700d8, nextp=0xbe5feff4) at sp_head.cc:1426
#15 0x082d3bf3 in sp_head::execute (this=0x8d07bb8, thd=0x8c700d8) at sp_head.cc:619
#16 0x082d461e in sp_head::execute_procedure (this=0x8d07bb8, thd=0x8c700d8, args=0x8c70544) at sp_head.cc:852
#17 0x081a8b98 in mysql_execute_command (thd=0x8c700d8) at sql_parse.cc:4106
#18 0x081abe6e in mysql_parse (thd=0x8c700d8, inBuf=0x8ca5838 "call regenPath(1)", length=17) at sql_parse.cc:5252
#19 0x081a189a in dispatch_command (command=COM_QUERY, thd=0x8c700d8, packet=0x8c9d7d9 "call regenPath(1)", packet_length=18) at sql_parse.cc:1651
#20 0x081a1097 in do_command (thd=0x8c700d8) at sql_parse.cc:1454
#21 0x081a022d in handle_one_connection (arg=0x8c700d8) at sql_parse.cc:1114
#22 0x4004d0b1 in pthread_start_thread () from /lib/libpthread.so.0
#23 0x4004d12f in pthread_start_thread_event () from /lib/libpthread.so.0
#24 0x401b68fa in clone () from /lib/libc.so.6

But when I use current 5.0 snapshot from bk I get the same error as Jorge:

mysql> call regenPath(1)$$
ERROR 1099 (HY000): Table 'op' was locked with a READ lock and can't be updated

I think this problem with locking just masks the crash which will reappear once it will be solved.

Hi, Dmitry!

Further investigation showed that "ERROR 1099 (HY000): Table 'op' was locked with a READ lock and can't be updated" occurs during execution of multi-delete statement.
It turned out that multi-delete execution inside of stored routines was broken
somewhen between 5.0.6 and 5.0.7 releases. :( 

I have reported this problem separately, see http://bugs.mysql.com/bug.php?id=11158 

Meanwhile, I'll try to provide independent test case exposing bug that you have reported originally.

Once again, thanks for finding this!

Simplified test case for original problem looks like:

drop table if exists t1;
create table t1 (id int, val int);
drop procedure if exists sp;
create procedure sp() insert into t1 (id, val) select id, val from t1 union select id, val from t1;
call sp();
call sp(); # This will cause server crash !

Since I am using MyISAM tables here problem obviously is not storage
engine specific.

There was a problem with multi-update. It was solved (i hope :). But, intresting thing. Crash appears only if UNION is used, as i reported. Uncomment the workaround (e.g. replace single insert with union) - everything goes fine.

Anyway, thanks for great job you doing, guys! ))

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26029

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26194

Fixed in 5.0.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.8 change history; marked Closed.