MySQL Bugs: #14342: Complex subquery is causing the server to hang/crash

Bug #14342	Complex subquery is causing the server to hang/crash
Submitted:	26 Oct 2005 18:09	Modified:	20 Nov 2005 3:29
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.15/5.0.16 BK source	OS:	Linux (Linux RH9/Gentoo r3/Windows)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Running an update statement which sets off an update trigger causes the server to either hang or crash.

How to repeat:
mysqladmin create tr_crash
mysql tr_crash < ldb_test.dump
mysql tr_crash < sql

Note that this seems to be an InnoDB issue because if all the tables are changed
to MyISAM then the trigger finishes successfully.

Verified also on Windows:

>	mysqld-debug.exe!free_io_cache(st_table * table=0xdddddddd)  Line 250 + 0x3	C++
 	mysqld-debug.exe!JOIN::cleanup(int full=0)  Line 5845 + 0x15	C++
 	mysqld-debug.exe!st_select_lex::cleanup_all_joins(int full=0)  Line 732	C++
 	mysqld-debug.exe!JOIN::join_free()  Line 5795	C++
 	mysqld-debug.exe!do_select(JOIN * join=0x03191d38, List<Item> * fields=0x03177b0c, st_table * table=0x00000000, Procedure * procedure=0x00000000)  Line 9078	C++
 	mysqld-debug.exe!JOIN::exec()  Line 1669 + 0x19	C++
 	mysqld-debug.exe!subselect_single_select_engine::exec()  Line 1578	C++
 	mysqld-debug.exe!Item_subselect::exec()  Line 198 + 0x13	C++
 	mysqld-debug.exe!Item_singlerow_subselect::val_int()  Line 463 + 0xe	C++
 	mysqld-debug.exe!Item::save_in_field(Field * field=0x0318f8e0, int no_conversions=0)  Line 3947 + 0x11	C++
 	mysqld-debug.exe!fill_record(THD * thd=0x03156f10, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0)  Line 4856 + 0x11	C++
 	mysqld-debug.exe!fill_record_n_invoke_before_triggers(THD * thd=0x03156f10, List<Item> & fields={...}, List<Item> & values={...}, int ignore_errors=0, Table_triggers_list * triggers=0x03183358, trg_event_type event=TRG_EVENT_UPDATE)  Line 4897 + 0x15	C++
 	mysqld-debug.exe!mysql_update(THD * thd=0x03156f10, st_table_list * table_list=0x031769a8, List<Item> & fields={...}, List<Item> & values={...}, Item * conds=0x00000000, unsigned int order_num=0, st_order * order=0x00000000, unsigned long limit=4294967295, enum_duplicates handle_duplicates=DUP_ERROR, int ignore=0)  Line 427 + 0x1c	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x03156f10)  Line 3192 + 0x5a	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x03156f10, char * inBuf=0x031760b8, unsigned int length=2170)  Line 5558 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x03156f10, char * packet=0x0316e009, unsigned int packet_length=2171)  Line 1697 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x03156f10)  Line 1498 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x03156f10)  Line 1143 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x03146ad0)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x031471b8)  Line 196 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()

Hi!

Since crash happens exactly in the same place if you drop both 'app_insrt' and 'app_updt' triggers I think this bug has nothing to do with triggers. Seems to be pure subquery-related issue. I have updated synopsis to reflect this.

minimal test suite for this bug:
CREATE TABLE `t1` (
  `t3_id` int NOT NULL,
  `t1_id` int NOT NULL,
  PRIMARY KEY  (`t1_id`)
);
CREATE TABLE `t2` (
  `t2_id` int NOT NULL,
  `t1_id` int NOT NULL,
  `b` int NOT NULL,
  PRIMARY KEY  (`t2_id`),
  UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`)
) ENGINE=InnoDB;
CREATE TABLE `t3` (
  `t3_id` int NOT NULL
);
INSERT INTO `t3` VALUES (3);
select
  (SELECT rs.t2_id
   FROM t2 rs
   WHERE rs.t1_id=
     (SELECT lt.t1_id
      FROM t1 lt
      WHERE lt.t3_id=a.t3_id)
   ORDER BY b DESC LIMIT 1)
from t3 AS a;
drop tables t1,t2,t3;

current 4.1 is not affected by this bug

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/31764

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/31933

I've checked other parts of the code that are executed with thd->mem_root == quick->mem_root and there seems to be no problems similar to one fixed by this bug fix.

Thank you for bugreport!
The bugfix is pushed to 5.0.17.

Noted in 5.0.17 changelog.