MySQL Bugs: #29376: Falcon: two-way deadlock and crash with unique index and trigger

Bug #29376	Falcon: two-way deadlock and crash with unique index and trigger
Submitted:	27 Jun 2007 1:19	Modified:	29 Jun 2007 21:15
Reporter:	Peter Gulutzan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.1-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Jim Starkey	CPU Architecture:	Any

Description:
I create a Falcon table with a single column.
I create a unique index on the column.
I create a trigger that inserts into a MyISAM table.
I insert in a way that causes a deadlock.
I get a deadlock.

So far, this is a variant of
Bug#29206 Falcon: Two-way deadlock with unique index
which is fixed. The main difference is the trigger.
But I go on and cause a crash by saying
mysqladmin shutdown.

ChangeSet@1.2566, 2007-06-26 13:36:30-04:00

How to repeat:
Start two connections. Call them T1 and T2.
Default storage engine is MyISAM.

On T1, say:
create table i4 (i varchar(5)) engine=falcon;
create unique index i on i4 (i);
create table i5 (s1 int);
create trigger i4_bi before insert on i4 for each row insert into i5 values (1);
set @@autocommit=0;
insert into i4 values ('I');

On T2, say:
set @@autocommit=0;
insert into i4 values ('j');

On T1, say:
insert into i4 values ('j'); /* waits for #2 */

On T2, say:
insert into i4 values ('I'); /* waits for #1 */

Wait a while to make sure there's a deadlock.

On T2, say:
^C i.e. control-C
quit
/usr/local/mysql/bin/mysqladmin --user=root shutdown

Observe that mysqld crashes.

Verified as described

On T2:
[07:51] root@test>insert into i4 values ('I');
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted

[07:52] root@test>Bye
hakan@lu0011:~$ mysqladmin --user=root shutdown

[LWP 16903 exited]
Bugcheck: assertion failed at line 163 in file Transaction.cpp

[New LWP 16919]
Exception: assertion failed at line 163 in file Transaction.cpp

ptrace: No such process.
(gdb) terminate called after throwing an instance of 'SQLError'

Continuing.
Couldn't get registers: No such process.

Can't repeat

[12:58] root@test>insert into i4 values ('I'); /* waits for #1 */

Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted

[12:58] root@test>Aborted

hakan@lu0011:~$ mysqladmin -uroot  shutdown

Program received signal SIGUSR1, User defined signal 1.
0x00002ba8e7e41f2b in ?? ()
(gdb) c
Continuing.

Program received signal SIGUSR1, User defined signal 1.
0x00002ba8e7e41f2b in ?? ()
(gdb)
Continuing.
[LWP 9120 exited]
070629 12:59:24 [Note] /usr/local/mysql-5.1-debug/libexec/mysqld: Shutdown complete

Program exited normally.

I confirm that the crash is no longer repeatable.
But the deadlock is repeatable.
The deadlock, even without the crash, is a bug
because Falcon won't timeout.

I reported the deadlock as a new bug:
Bug#29452 Falcon: two-way deadlock with unique index and trigger

Since the crash is no longer repeatable,
I'll set this to "Can't repeat" again.