MySQL Bugs: #26475: delete causing crash

Bug #26475	delete causing crash
Submitted:	19 Feb 2007 6:56	Modified:	20 Jul 2007 12:15
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0.0-alpha	OS:	Linux (suse9.3 x86)
Assigned to:		CPU Architecture:	Any
Tags:	crash

Description:
in a test which does this in many threads, a crash always happens, and the statement causing a crash is always the delete:

--------
start transaction;
insert ignore into t1(c1,c2,c3) values (12345,'aaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
delete from t1 limit 10;
---------

Version: '5.2.4-falcon-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
database open failed: can't open file "/home/sbester/server/5.2/mysql-5.2.4-falcon-alpha-linux-i686/data/test.fts": No such file or directory (2)
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=15
max_connections=151
threads_connected=15
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 336762 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8e79d78
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=0x445f3044, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81f5637 handle_segfault + 417
0x83cf921 _ZN6Record6addRefEv + 11
0x83772fc _ZN15StorageDatabase9deleteRowEP17StorageConnectionP5Tablei + 88
0x8379c70 _ZN12StorageTable9deleteRowEi + 44
0x8371d05 _ZN15NfsStorageTable10delete_rowEPKc + 171
0x82daa77 _ZN7handler13ha_delete_rowEPKc + 33
0x828353f _Z12mysql_deleteP3THDP13st_table_listP4ItemP11st_sql_listyyb + 3023
0x8212113 _Z21mysql_execute_commandP3THD + 11485
0x8218a91 _Z11mysql_parseP3THDPcj + 353
0x820df51 _Z16dispatch_command19enum_server_commandP3THDPcj + 2077
0x820d729 _Z10do_commandP3THD + 603
0x820c816 handle_one_connection + 900
0x40250aa7 _end + 933750551
0x401e6c2e _end + 933316766
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e7b410 = delete from t1 limit 10
thd->thread_id=10

How to repeat:
open many connections to mysql. (10 or 15 is ok).
in one connection, create the table t1:

-------
drop table if exists t1;
create table t1(c1 mediumint,c2 mediumblob,c3 mediumtext,key(c1))engine=falcon;
-------

in all connections, run this over and over and over:

-------
start transaction;
insert ignore into t1(c1,c2,c3) values (12345,'aaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
delete from t1 limit 10;
-------

Or use the uploaded testcase to do it quicker.

Suggested fix:
.

testcase

Attachment: bug26475.c (text/plain), 4.93 KiB.

i left out that after the delete statement there is a "commit" happening.

Can't repeat this anymore:

waiting for worker threads to finish...
hakan@lu0011:~/work/mysql/testing$

i also can no longer repeat a crash on mysql-6.0.0-falcon-alpha-linux-i686 using this testcase.  i had tried at 15 and 50 threads, with no crash.

I lied.  It still crashes at 50 threads.

0x81fbb65 handle_segfault + 541
0x83dda41 _ZN6Record6addRefEv + 11
0x8382230 _ZN15StorageDatabase9deleteRowEP17StorageConnectionP5Tablei + 88
0x838578c _ZN12StorageTable9deleteRowEi + 44
0x837c657 _ZN15NfsStorageTable10delete_rowEPKc + 171
0x82e541f _ZN7handler13ha_delete_rowEPKc + 33
0x828c580 _Z12mysql_deleteP3THDP13st_table_listP4ItemP11st_sql_listyyb + 3056
0x820ada4 _Z21mysql_execute_commandP3THD + 11894
0x821142b _Z11mysql_parseP3THDPcj + 299
0x8206871 _Z16dispatch_command19enum_server_commandP3THDPcj + 2073
0x820604c _Z10do_commandP3THD + 612
0x8204c15 handle_one_connection + 253
0x40250aa7 _end + 933645719
0x401e6c2e _end + 933211934
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x91064a0 = delete from t1 limit 10
thd->thread_id=51

Falcon transaction behavior has changed since this was posted.  Falcon
now hangs on second and subsequent connection pending the first connection
to commit or rollback.  The crash occurs neither running the blob updates
manually or by the supplied test program.

I confirm that falcon doesn't crash using the supplied testcase.
I even tried at 50 threads and it ran for an hour with no crash. Seems fixed indeed.

A note has been added to the 6.0.1 changelog: 

DELETE statements could cause a crash when many simultaneous threads are running.