MySQL Bugs: #2108: Server crash when subquery uses 'limit' clause on InnoDB tables

Bug #2108	Server crash when subquery uses 'limit' clause on InnoDB tables
Submitted:	13 Dec 2003 12:39	Modified:	14 Dec 2003 12:52
Reporter:	Igor Blagodetelev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Linux (Slackware 9.1/Suse 8.2/Windows X)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When nested subquery uses 'limit' clause the MySQL 4.1.1 server crashed with signal 11
crashes mysqld following information in hostname.err:
Version: '4.1.1-alpha-max-log'  socket: '/tmp/mysql.sock'  port: 3306
mysqld got signal 11;
...
key_buffer_size=2097152
read_buffer_size=1044480
max_used_connections=0
max_connections=32
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 67455 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8685490
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...
Cannot determine thread, fp=0xbe5fe848, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8106af3
0x40048c45
0x832b873
0x82774fc
0x81783cb
0x817860b
0x8168a39
0x813e57e
0x813e226
0x8134b36
0x8135068
0x8131fde
0x81142b7
0x8118d49
0x8112f3f
0x81128d1
0x8112047
0x40042ca3
0x401e2c97
New value of fp=(nil) failed sanity check, terminating stack trace!
...
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x868a770 = select (select test1 from test limit 1) from test
thd->thread_id=1

How to repeat:
mysql> create table test (test1 int, test2 char(255)) type=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values (1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> select (select test1 from test limit 1) from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the bug report I was able to repeat:

Local of the crash:

/innobase/include/read0read.ic
~~39~~

/*************************************************************************
Checks if a read view sees the specified transaction. */
UNIV_INLINE
ibool
read_view_sees_trx_id(
/*==================*/
				/* out: TRUE if sees */
	read_view_t*	view,	/* in: read view */
	dulint		trx_id)	/* in: trx id */
{
	ulint	n_ids;
	int	cmp;
	ulint	i;
	
	if (ut_dulint_cmp(trx_id, view->up_limit_id) < 0) {
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
CRASH HAPPENS HERE

		return(TRUE);
	}

Below call stack on Windows:

mysqld.exe!read_view_sees_trx_id(read_view_struct * view=0x00000000,
 dulint_struct trx_id={...})  Line 53 + 0x3	C

mysqld.exe!lock_clust_rec_cons_read_sees(unsigned char * rec=0x0256c1b3,
 dict_index_struct * index=0x024370a0, read_view_struct * view=0x00000000)
 Line 584 + 0x11	C

mysqld.exe!row_search_for_mysql(unsigned char * buf=0x0163f740, unsigned
 long mode=1, row_prebuilt_struct * prebuilt=0x024350a0, unsigned long
 match_mode=0, unsigned long direction=1)  Line 3298 + 0x23	C

mysqld.exe!ha_innobase::general_fetch(unsigned char * buf=0x0163f740,
 unsigned int direction=1, unsigned int match_mode=0)  Line 2918 + 0x17	C++

mysqld.exe!ha_innobase::rnd_next(unsigned char * buf=0x0163f740)  Line
 3117 + 0x10	C++

mysqld.exe!rr_sequential(st_read_record * info=0x036fba20)  
 Line 173 + 0x18	C++

mysqld.exe!sub_select(JOIN * join=0x036fa2d0, st_join_table * join_tab=0x036fb9f8,
 int end_of_records=0)  Line 5704 + 0xa	C++

mysqld.exe!do_select(JOIN * join=0x036fa2d0, List<Item> * fields=0x036ef41c,
 st_table * table=0x00000000, Procedure * procedure=0x00000000)  Line 5575 + 0xf	C++

mysqld.exe!JOIN::exec()  Line 1477 + 0x2d	C++

mysqld.exe!mysql_select(THD * thd=0x036ef040, Item * * * rref_pointer_array=
 0x036ef498, st_table_list * tables=0x036fa180, unsigned int wild_num=0,
 List<Item> & fields={...}, Item * conds=0x00000000, unsigned int og_num=0,
 st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000,
 st_order * proc_param=0x00000000, unsigned long select_options=8669696,
 select_result * result=0x036fa2c0, st_select_lex_unit * unit=0x036ef2c4,
 st_select_lex * select_lex=0x036ef3b0)  Line 1603	C++

mysqld.exe!handle_select(THD * thd=0x036ef040, st_lex * lex=0x036ef2b8,
 select_result * result=0x036fa2c0)  Line 193 + 0x89	C++

mysqld.exe!mysql_execute_command(THD * thd=0x036ef040)  Line 1916
 + 0x11	C++

mysqld.exe!mysql_parse(THD * thd=0x036ef040, char * inBuf=0x036f9cf8,
 unsigned int length=49)  Line 3927 + 0x9	C++

mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY,
 THD * thd=0x036ef040, char * packet=0x036f5c81, unsigned int packet_length=50)
 Line 1387 + 0x1d	C++

mysqld.exe!do_command(THD * thd=0x036ef040)  Line 1217 + 0x31	C++

mysqld.exe!handle_one_connection(void * arg=0x036ef040)  Line 979 + 0x9	C++

mysqld.exe!pthread_start(void * param=0x036f2758)  Line 63 + 0x7	C

mysqld.exe!_threadstart(void * ptd=0x036fced0)  Line 173 + 0xd	C

kernel32.dll!77e6d33b()

Hi!

Looks like yet another bug where MySQL releases the table lock too early and the transaction gets committed inside InnoDB too early.

Sanja knows how to fix these.

Regards,

Heikki

Thank you for bug report! 
 
This bug has same cause as bug#2048, you can track progress of fixing of this 
bug loking on http://bugs.mysql.com/bug.php?id=2048. I have checked your test 
case, it work good with fix of bug#2048.