Bug #1708 InnoDB crash on a subquery
Submitted: 30 Oct 2003 0:56 Modified: 2 Nov 2003 7:33
Reporter: Indrek Siitan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1.1 (2003-10-30) OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[30 Oct 2003 0:56] Indrek Siitan 
Description:
InnoDB crash on a subquery.

Backtrace: 

SIGSEGV, Segmentation fault.
read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 620308})
    at ../../innobase/read/../include/read0read.ic:53
53              if (ut_dulint_cmp(trx_id, view->up_limit_id) < 0) {
Current language:  auto; currently c
(gdb) bt
#0  read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 620308})
    at ../../innobase/read/../include/read0read.ic:53
#1  0x8252295 in lock_clust_rec_cons_read_sees (rec=0x9f500a0 "\200", 
    index=0x9634d60, view=0x0) at lock0lock.c:584
#2  0x81fbfd9 in row_search_for_mysql (buf=0xa99bd90 "ÿ\001", mode=1, 
    prebuilt=0x9635c60, match_mode=0, direction=1) at row0sel.c:3291
#3  0x810d6e2 in ha_innobase::general_fetch (this=0xa99bca0, 
    buf=0xa99bd90 "ÿ\001", direction=1, match_mode=0) at ha_innodb.cc:2916
#4  0x810d79d in ha_innobase::index_next (this=0xa99bca0, 
    buf=0xa99bd90 "ÿ\001") at ha_innodb.cc:2952
#5  0x80cb95c in join_read_next (info=0xaa25b18) at sql_select.cc:5879
#6  0x80cac9c in sub_select (join=0xaa24da0, join_tab=0xaa25af0, 
    end_of_records=false) at sql_select.cc:5473
#7  0x80ca818 in do_select (join=0xaa24da0, fields=0x0, table=0x8520818, 
    procedure=0x0) at sql_select.cc:5345
#8  0x80bfbc9 in JOIN::exec (this=0xaa24da0) at sql_select.cc:1136
#9  0x80c10c7 in mysql_select (thd=0xa99a018, rref_pointer_array=0xa99a460, 
    tables=0xaa24c20, wild_num=0, fields=@0xa99a3c8, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=17339393, 
    result=0xaa24d90, unit=0xa99a29c, select_lex=0xa99a364, 
    tables_and_fields_initied=false) at sql_select.cc:1553
#10 0x80bd763 in handle_select (thd=0xa99a018, lex=0xa99a290, result=0xaa24d90)
    at sql_select.cc:191
#11 0x809e7f0 in mysql_execute_command (thd=0xa99a018) at sql_parse.cc:1802
#12 0x80a3515 in mysql_parse (thd=0xa99a018, 
    inBuf=0xaa24028 "select distinct\n   p1.processor_id,\n   (select y.yod_id\n   from processor 
p2, yod_allocation y\n   where p2.processor_id = p1.processor_id and\n         p2.processor_id = 
y.processor_id)\nfrom processor "..., 
    length=202) at sql_parse.cc:3768
#13 0x809d57b in dispatch_command (command=COM_QUERY, thd=0xa99a018, 
    packet=0xa9ea019 "select distinct\n   p1.processor_id,\n   (select y.yod_id\n   from processor 
p2, yod_allocation y\n   where p2.processor_id = p1.processor_id and\n         p2.processor_id = 
y.processor_id)\nfrom processor "..., 
    packet_length=203) at sql_parse.cc:1295
#14 0x809cf83 in do_command (thd=0xa99a018) at sql_parse.cc:1129
#15 0x809c638 in handle_one_connection (arg=0xa99a018) at sql_parse.cc:899
#16 0x2852241f in _thread_start () from /usr/lib/libc_r.so.4
#17 0x0 in ?? ()

How to repeat:
CREATE TABLE processor (
   processor_id INTEGER NOT NULL,
   PRIMARY KEY (processor_id)
) TYPE=InnoDB;

CREATE TABLE yod (
   yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   login_processor INTEGER UNSIGNED ,
   PRIMARY KEY (yod_id)
) TYPE=InnoDB;

CREATE TABLE yod_allocation (
   processor_id INTEGER NOT NULL,
   yod_id BIGINT UNSIGNED NOT NULL,
   PRIMARY KEY (processor_id, yod_id),
   INDEX (processor_id),
   INDEX (yod_id),
   FOREIGN KEY (processor_id) REFERENCES processor(processor_id),
   FOREIGN KEY (yod_id) REFERENCES yod(yod_id) 
) TYPE=InnoDB;

INSERT INTO processor VALUES (1),(2),(3);
INSERT INTO yod VALUES (1,1),(2,2),(3,3);
INSERT INTO yod_allocation VALUES (1,1),(2,2),(3,3);

SELECT distinct
   p1.processor_id,
   (SELECT y.yod_id
   FROM processor p2, yod_allocation y
   WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id)
FROM processor p1;
[30 Oct 2003 1:44] Heikki Tuuri 
Hi!

The crash probably happens because MySQL releases the table locks too early, before the query has ended.

(gdb) bt
#0  read_view_sees_trx_id (view=0x0, trx_id={high = 0, low = 620308})
    at ../../innobase/read/../include/read0read.ic:53
#1  0x8252295 in lock_clust_rec_cons_read_sees (rec=0x9f500a0 "\200", 
    index=0x9634d60, view=0x0) at lock0lock.c:584

Above we see that the consistent read 'view' is NULL. InnoDB should have opened it at the start of the SELECT query, and probably it has been closed too early.

Regards,

Heikki
[2 Nov 2003 3:58] Oleksandr Byelkin 
ChangeSet 
  1.1602 03/11/02 13:53:38 bell@sanja.is.com.ua +3 -0 
  fixed locking problem with innodb & subqueries (BUG#1708) 
  tests with innodb moved to separate file
[2 Nov 2003 7:33] Oleksandr Byelkin 
Thank you for bugreport. Bugfix is alredy pushed in source tree