Bug #570 Crash using an UPDATE with ORDER BY
Submitted: 2 Jun 2003 14:25 Modified: 3 Jul 2003 6:46
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.13 OS:Microsoft Windows (Windows 2000 / Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[2 Jun 2003 14:25] [ name withheld ]
A statement like 
"UPDATE table SET column = GREATEST(column,<integer>) WHERE ... ORDER BY another_column LIMIT 1" kills the whole server with the Windows "Critical"-Error-Dialog and the message "The command in "0x00453048" links to ram in "0x00000000". The operation "read" couldn't be accomplished."
Sorry for this rather bad translation, but this great operating system only gives me german error messages..
This bug can be easily reproduced and occured with both 4.0.12 and 4.0.13.
I haven't checked other versions.

How to repeat:
Just use the following dump and test-data (Hey, most of this ugly table design and names is from SAP, your new friend. I'm innocent!) with

mysql test < this_statements.sql


  AUFNR varchar(12) NOT NULL default '',
  PLNFL varchar(6) NOT NULL default '',
  VORNR varchar(4) NOT NULL default '',
  UVORN varchar(4) NOT NULL default '',
  SPLIT char(3) NOT NULL default '',
  RUECK varchar(10) default NULL,
  STEUS varchar(4) default NULL,
  ARBPL varchar(8) default NULL,
  WERK varchar(4) default '',
  KTSCH varchar(7) default NULL,
  LTXA1 varchar(40) default NULL,
  LTXA2 varchar(40) default NULL,
  LAR01 varchar(6) default NULL,
  LAR02 varchar(6) default NULL,
  LAR03 varchar(6) default NULL,
  LAR04 varchar(6) default NULL,
  LAR05 varchar(6) default NULL,
  LAR06 varchar(6) default NULL,
  RFGRP varchar(10) default NULL,
  RFSCH varchar(10) default NULL,
  RASCH char(2) default NULL,
  AUFAK float(5,3) default NULL,
  MEINH char(3) default NULL,
  MGVRG float(13,3) default NULL,
  ASVRG float(13,3) default NULL,
  BMSCH float(10,3) default NULL,
  UMREN int(11) default '0',
  UMREZ int(11) default '0',
  RUEST float(9,3) default '0.000',
  RUEZE char(3) default '',
  BEA float(9,3) default '0.000',
  BEAZE char(3) default '',
  ABRUE float(9,3) default '0.000',
  ABRZE char(3) default '',
  VGE01 char(3) default NULL,
  VGW01 float(9,3) default NULL,
  VGE02 char(3) default NULL,
  VGW02 float(9,3) default NULL,
  VGE03 char(3) default NULL,
  VGW03 float(9,3) default NULL,
  VGE04 char(3) default NULL,
  VGW04 float(9,3) default NULL,
  VGE05 char(3) default NULL,
  VGW05 float(9,3) default NULL,
  VGE06 char(3) default NULL,
  VGW06 float(9,3) default NULL,
  FSAVD date default NULL,
  FSAVZ time default NULL,
  SSAVD date default NULL,
  SSAVZ time default NULL,
  FSEDD date default NULL,
  FSEDZ time default NULL,
  SSEDD date default NULL,
  SSEDZ time default NULL,
  FSSBD date default NULL,
  FSSBZ time default NULL,
  SSSBD date default NULL,
  SSSBZ time default NULL,
  FSSAD date default NULL,
  FSSAZ time default NULL,
  SSSAD date default NULL,
  SSSAZ time default NULL,
  FSSLD date default NULL,
  FSSLZ time default NULL,
  FSELD date default NULL,
  FSELZ time default NULL,
  SSSLD date default NULL,
  SSSLZ time default NULL,
  SSELD date default NULL,
  SSELZ time default NULL,
  SLWID varchar(7) default NULL,
  USR00 varchar(20) default NULL,
  USR01 varchar(20) default NULL,
  USR02 varchar(10) default NULL,
  USR03 varchar(10) default NULL,
  USR04 float(13,3) default NULL,
  USE04 char(3) default NULL,
  USR05 float(13,3) default NULL,
  USE05 char(3) default NULL,
  USR06 float(13,3) default NULL,
  USE06 varchar(5) default NULL,
  USR07 float(13,3) default NULL,
  USE07 varchar(5) default NULL,
  USR08 date default NULL,
  USR09 date default NULL,
  USR10 char(1) default NULL,
  USR11 char(1) default NULL,
  FAPOLTXT text,
  xtime datetime default '0000-00-00 00:00:00',
  xstatus char(1) NOT NULL default '',
  xstatus_vor smallint(5) unsigned NOT NULL default '0',
) TYPE=InnoDB;

-- Dumping data for table 'afv'

INSERT INTO afv VALUES ('40004712','000001','0010','','000','1243482621','XP01','XT103','XT',NULL,'MDE/BDE-Funktion','','Fert','Rüst',NULL,NULL,NULL,NULL,NULL,NULL,'',5.000,'ST',20.000,NULL,100.000,0,0,20.000,'Min',60.000,'Min',10.000,'Min','min',10.000,'min',5.000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-05-01','09:00:00','2003-05-22','16:15:00','2003-05-20','15:15:00','2003-05-17','08:00:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Vorgangslangtext 2. Auftrag 1. Vorgang','2003-05-31 18:00:00','0',9);
INSERT INTO afv VALUES ('40004712','000001','0020','','000','1243482622','XP01','XT103','XT',NULL,'MDE/BDE-Funktion','','Fert','Rüst',NULL,NULL,NULL,NULL,NULL,NULL,'',5.000,'ST',20.000,NULL,100.000,0,0,0.000,'Min',120.000,'Min',10.000,'Min','min',10.000,'min',5.000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-05-01','08:00:00','2003-05-22','16:15:00','2003-05-20','15:15:00','2003-05-17','08:00:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Vorgangslangtext 2. Auftrag 2. Vorgang','2003-06-02 22:34:05','0',0);

UPDATE afv SET afv.xstatus_vor = Greatest(afv.xstatus_vor,1) WHERE afv.aufnr = "40004712" AND afv.plnfl = "000001" AND afv.vornr > "0010" ORDER BY afv.vornr ASC LIMIT 1


Suggested fix:
No idea, sorry. It works without the "ORDER BY", so I guess that's the point where I'd start.
[2 Jun 2003 14:54] Heikki Tuuri

I was able to repeat this in Linux in 4.0.14. It is not a multi-table update, but an ordinary update. Looks like the db_name field in an 'Item' struct is NULL.

(gdb) print *item
$2 = {<Item_ident> = {<Item> = {str_value = {Ptr = 0x0, str_length = 0,
        Alloced_length = 0, alloced = false}, name = 0x887f728 "vornr",
      next = 0x887f6a8, max_length = 0, marker = 0 '\000',
      decimals = 0 '\000', maybe_null = 0 '\000', null_value = 0 '\000',
      binary = 0 '\000', unsigned_flag = 0 '\000', with_sum_func = 0 '\000',
      _vptr.Item = 0x834cc00}, db_name = 0x0, table_name = 0x887f720 "afv",
    field_name = 0x887f728 "vornr"}, field = 0x0, result_field = 0x0}
(gdb) bt
#0  strcmp (p1=0x0, p2=0x887f720 "afv") at ../sysdeps/generic/strcmp.c:38
#1  0x08093333 in find_field_in_tables (thd=0x8879e60, item=0x887f730,
    tables=0xbe7ff654) at sql_base.cc:1718
#2  0x0804af31 in Item_field::fix_fields (this=0x887f730, thd=0x8879e60,
    tables=0xbe7ff654) at item.cc:333
#3  0x080a5c12 in find_order_in_list (thd=0x8879e60, tables=0xbe7ff654,
    order=0x887f770, fields=@0xbe7ff644, all_fields=@0xbe7ff644)
    at sql_select.cc:6607
#4  0x080a5cbb in setup_order (thd=0x8879e60, tables=0xbe7ff654,
    fields=@0xbe7ff644, all_fields=@0xbe7ff644, order=0x887f770)
    at sql_select.cc:6626
#5  0x080ac3e8 in mysql_update (thd=0x8879e60, table_list=0x887f158,
    fields=@0x887a014, values=@0x887a158, conds=0x887f6a8, order=0x887f770,
    limit=1, handle_duplicates=DUP_ERROR) at sql_update.cc:93
#6  0x0807f477 in mysql_execute_command () at sql_parse.cc:1887
#7  0x08081d7c in mysql_parse (thd=0x8879e60,
    inBuf=0x887f078 "UPDATE afv SET afv.xstatus_vor = Greatest(afv.xstatus_vor,1
) WHERE\nafv.aufnr = \"40004712\" AND afv.plnfl = \"000001\" AND afv.vornr > \"0
010\"\nORDER BY afv.vornr ASC LIMIT 1", length=168) at sql_parse.cc:2899
#8  0x0807cf40 in dispatch_command (command=COM_QUERY, thd=0x8879e60,
    packet=0x88929d1 "UPDATE afv SET afv.xstatus_vor = Greatest(afv.xstatus_vor,
1) WHERE\nafv.aufnr = \"40004712\" AND afv.plnfl = \"000001\" AND afv.vornr > \"
0010\"\nORDER BY afv.vornr ASC LIMIT 1", packet_length=169)
    at sql_parse.cc:1049
#9  0x0807c9a5 in do_command (thd=0x8879e60) at sql_parse.cc:924
#10 0x0807c05e in handle_one_connection (arg=0x8879e60) at sql_parse.cc:714
#11 0x0820284a in pthread_start_thread (arg=0xbe7ffc00) at manager.c:291
#12 0x08202885 in pthread_start_thread_event () at manager.c:315


[2 Jun 2003 15:31] Miguel Solorzano
I verify on Windows 4.0.13, the crash occurs at:

for (; tables ; tables=tables->next)
      if (!strcmp(tables->alias,table_name) &&
	  (!db || !strcmp(db,tables->db)))

back trace on windows:

 	mysqld.exe!strcmp()  Line 77	Asm
>	mysqld.exe!find_field_in_tables(THD * thd=0x01138040, Item_field * item=0x01133078, st_table_list * tables=0x0641f76c)  Line 1718 + 0x10	C++
 	mysqld.exe!Item_field::fix_fields(THD * thd=0x01138040, st_table_list * tables=0x0641f76c)  Line 333 + 0x11	C++
 	mysqld.exe!find_order_in_list(THD * thd=0x01138040, st_table_list * tables=0x0641f76c, st_order * order=0x011330b8, List<Item> & fields={...}, List<Item> & all_fields={...})  Line 6607 + 0x1f	C++
 	mysqld.exe!setup_order(THD * thd=0x01138040, st_table_list * tables=0x0641f76c, List<Item> & fields={...}, List<Item> & all_fields={...}, st_order * order=0x011330b8)  Line 6626 + 0x19	C++
 	mysqld.exe!mysql_update(THD * thd=0x01138040, st_table_list * table_list=0x01132af8, List<Item> & fields={...}, List<Item> & values={...}, Item * conds=0x01133000, st_order * order=0x011330b8, unsigned long limit=1, enum_duplicates handle_duplicates=DUP_ERROR)  Line 93 + 0x4a	C++
 	mysqld.exe!mysql_execute_command()  Line 1875 + 0x3d	C++
 	mysqld.exe!mysql_parse(THD * thd=0x01138040, char * inBuf=0x01132a20, unsigned int length=168)  Line 2888	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x01138040, char * packet=0x01143051, unsigned int packet_length=168)  Line 1049 + 0x17	C++
 	mysqld.exe!do_command(THD * thd=0x01138040)  Line 924 + 0x37	C++
 	mysqld.exe!handle_one_connection(void * arg=0x01138040)  Line 714 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x01145c28)  Line 63 + 0x7	C
 	mysqld.exe!_threadstart(void * ptd=0x01145e50)  Line 173 + 0xd	C
[5 Jun 2003 10:26] [ name withheld ]
removed "multi-table" (Seems I was kind of absentminded while reporting this) and "Windows" from subject, since someone already verified this bug using Linux.
[3 Jul 2003 6:46] Alexey Botchkov
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at