MySQL Bugs: #48792: crash when 'delete from .. order by' from a large table

Bug #48792	crash when 'delete from .. order by' from a large table
Submitted:	16 Nov 2009 8:10	Modified:	1 Dec 2009 12:23
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S1 (Critical)
Version:	5.1.40	OS:	Any (xp,linux)
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
1800E4A24    ha_innodb_plugin.dll!memset()[memset.asm:144]
1800B984B    ha_innodb_plugin.dll!row_sel_field_store_in_mysql_format()[row0sel.c:2611]
1800B9BE0    ha_innodb_plugin.dll!row_sel_store_mysql_rec()[row0sel.c:2767]
1800BBCB5    ha_innodb_plugin.dll!row_search_for_mysql()[row0sel.c:4338]
18009252B    ha_innodb_plugin.dll!ha_innodb::index_read()[ha_innodb.cc:5058]
180092EB8    ha_innodb_plugin.dll!ha_innodb::index_first()[ha_innodb.cc:5348]
180093032    ha_innodb_plugin.dll!ha_innodb::rnd_next()[ha_innodb.cc:5438]
1401BB434    mysqld.exe!find_all_keys()[filesort.cc:583]
1401BBA24    mysqld.exe!filesort()[filesort.cc:245]
1401A3FAB    mysqld.exe!mysql_delete()[sql_delete.cc:242]
14006BC06    mysqld.exe!mysql_execute_command()[sql_parse.cc:3301]
14006E966    mysqld.exe!mysql_parse()[sql_parse.cc:5967]
14006F4FA    mysqld.exe!dispatch_command()[sql_parse.cc:1226]
140070167    mysqld.exe!do_command()[sql_parse.cc:865]
1400968B7    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
1403169D5    mysqld.exe!pthread_start()[my_winthread.c:85]
1402E0B07    mysqld.exe!_callthreadstart()[thread.c:295]
1402E0BD5    mysqld.exe!_threadstart()[thread.c:275]
077D6B6CA    kernel32.dll!BaseThreadStart()
to get some variables.
inters may be invalid and cause the dump to abort...
ery at 0000000003360610=delete from t1 order by rand()
read_id=1

How to repeat:
large table, will upload testcase in a file.

i confirmed that the plugin innodb crashed, but the normal builtin innodb didn't.

Now I got a very odd situation trying to repeat this again. The alter table errors out with a duplicate key error, but alas there is not a duplicate key!

mysql> alter table t1 add primary key(so_id);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> 

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  7000000 |
+----------+
1 row in set (26.40 sec)

mysql> select count(distinct so_id) from t1;
+-----------------------+
| count(distinct so_id) |
+-----------------------+
|               7000000 |
+-----------------------+
1 row in set (31.40 sec)

mysql> select count(*) from t1 where so_id=0;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (19.44 sec)

mysql> select min(so_id),max(so_id) from t1;
+------------+------------+
| min(so_id) | max(so_id) |
+------------+------------+
|          4 |   28000000 |
+------------+------------+
1 row in set (19.63 sec)

So there is something extremely broken with alter table in the plugin.

mysql> alter table t1 add unique key(so_id);
ERROR 1062 (23000): Duplicate entry '0' for key 'so_id'

mysql> select so_id from t1 group by so_id having count(so_id)>1;
Empty set (4 min 36.68 sec)

So, I fail to see why the alter table isn't working now. I guess that might be part of the bug to start with ?

Thank you for the report.

I can not repeat described behavior. Which exact package do you use? How do you compile the server?

was not repeatable in 5.1.41 which contains 1.0.5 of the plugin.

This looks like a duplicate of Bug #48935, which was fixed in InnoDB Plugin 1.0.5.