MySQL Bugs: #25616: Unable to abort an insert query locked by another insert on a falcon table

Bug #25616	Unable to abort an insert query locked by another insert on a falcon table
Submitted:	14 Jan 2007 22:17	Modified:	1 Dec 2007 13:01
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.2.1-bk	OS:	Linux (Linux)
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any
Tags:	qc

Description:
Hi,

When trying to insert in a falcon table already locked by another insert, the CTRL+C doesn't stop the query.
When a MySQL storage engine is used, this is working properly.

Regards,
  Jocelyn Fournier
  http://www.mesdiscussions.net

How to repeat:
You can use the table from
ftp://ftp.mysql.com/pub/mysql/upload/i1.tar.gz to do
the test :

From the first client :

use test
CREATE TABLE inscrit (`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,   `pseudo` varchar(35) NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   UNIQUE KEY `pseudo` (`pseudo`)) ENGINE=falcon;

INSERT INTO inscrit (id,pseudo) SELECT id,pseudo FROM test.inscrit1;

From the second client :
INSERT INTO inscrit (id,pseudo) VALUES (1,'test');
CTRL+C =>
Query aborted by Ctrl+C
And when the first query finishes : 
ERROR 1062 (23000): Duplicate entry 'test' for key 'pseudo'

With a MyISAM table :

From the first client :

use test
CREATE TABLE inscrit (`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,   `pseudo` varchar(35) NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   UNIQUE KEY `pseudo` (`pseudo`)) ENGINE=MyISAM;

INSERT INTO inscrit (id,pseudo) SELECT id,pseudo FROM test.inscrit1;

From the second client :
INSERT INTO inscrit (id,pseudo) VALUES (1,'test');
CTRL+C =>
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted

Thank you for the bug report.

By default, Falcon a transaction blocked by another will wait until the locking transaction complete or the world ends.  Alternatively, setting the Falcon configuration parameter "falcon_lock_timeout" to a non-zero value will cause Falcon to time out after the specified number of seconds.

This is a relatively new feature, however, and was not implemented when this bug was submitted.

Hmmm...  the bug says that other engines respond to control-C,
not that they time out.

With latest mysql-6.0-falcon-team tree we see an assertion now:

(gdb) f 4
#4  0x00000000007f8d0d in Table::checkUniqueRecordVersion (this=0x2aaaaf35aa08,
    recordNumber=<value optimized out>, index=0x2aaaaf35aeb0, transaction=0x2aaaaf355720,
    record=<value optimized out>, sync=0x458cb8b0) at Table.cpp:2406
2406                            ASSERT(dup->state == recDeleted);
(gdb) p dup->state
$1 = 2 '\002'
(gdb) p recDeleted
$2 = 1

#0  0x00002b1763054fcb in raise () from /lib/libpthread.so.0
#1  0x000000000082a24e in Error::debugBreak () at Error.cpp:92
#2  0x000000000082a344 in Error::error (string=<value optimized out>) at Error.cpp:69
#3  0x000000000082a3a4 in Error::assertionFailed (fileName=0x4 <Address 0x4 out of bounds>, line=24615)
    at Error.cpp:76
#4  0x00000000007f8d0d in Table::checkUniqueRecordVersion (this=0x2aaaaf35aa08,
    recordNumber=<value optimized out>, index=0x2aaaaf35aeb0, transaction=0x2aaaaf355720,
    record=<value optimized out>, sync=0x458cb8b0) at Table.cpp:2406
#5  0x00000000007f913d in Table::checkUniqueIndex (this=0x2aaaaf35aa08, index=0x2aaaaf35aeb0,
    transaction=0x2aaaaf355720, record=0xff8bff8, sync=0x458cb8b0) at Table.cpp:2337
#6  0x00000000007f9218 in Table::checkUniqueIndexes (this=0x2aaaaf35aa08, transaction=0x2aaaaf355720,
    record=0xff8bff8, sync=0x458cb8b0) at Table.cpp:2311
#7  0x00000000007f9e4f in Table::insert (this=0x2aaaaf35aa08, transaction=0x2aaaaf355720,
    stream=<value optimized out>) at Table.cpp:2871
#8  0x00000000007ed621 in StorageDatabase::insert (this=<value optimized out>,
    connection=<value optimized out>, table=0x2aaaaf35aa08, stream=0x2aaaaf2ad7f0) at StorageDatabase.cpp:226
#9  0x00000000007f14e8 in StorageTable::insert (this=0x2aaaaf2a8260) at StorageTable.cpp:126
#10 0x00000000007ea68c in StorageInterface::write_row (this=0x12754f8, buff=0x1275790 "�\001")
    at ha_falcon.cpp:943
#11 0x0000000000732e42 in handler::ha_write_row (this=0x12754f8, buf=0x1275790 "�\001") at handler.cc:4612
#12 0x00000000006c7df3 in write_record (thd=0xea85108, table=0x1274018, info=0x458cbc60) at sql_insert.cc:1548
#13 0x00000000006cd5ab in mysql_insert (thd=0xea85108, table_list=0xea8f778, fields=@0xea86fe0,
    values_list=@0xea87028, update_fields=@0xea87010, update_values=@0xea86ff8, duplic=DUP_ERROR, ignore=false)
    at sql_insert.cc:803
#14 0x0000000000659b2e in mysql_execute_command (thd=0xea85108) at sql_parse.cc:2663
#15 0x000000000065a214 in mysql_parse (thd=0xea85108, inBuf=0xea8f688 "insert into t2 values (1, 'cdeas')",

I tried a slightly different way to reproduce the failure.

Connection 1:
create table t1 (a int auto_increment primary key, b varchar(5)) engine myisam;
insert into t1 values (DEFAULT, 'abefc');
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
...
...
...
insert into t1 (b) select b from t1;
select count(*) from t1;
[02:32] root@test>select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.00 sec)

create table t2 (a int auto_increment primary key, b varchar(5)) engine falcon;
insert into t2 select * from t1;

Connection 2:
insert into t2 values (1, 'cdeas');

Very interesting bug Hakan.  You have uncovered a bran new bug where checkUniqueRecordVersion() is not prepared for a chilled record.  It thinks the record is deleted!  So ASSERT(dup->state == recDeleted); fails.  

Please open a new bug, and allow this bug about 'unable to abort an insert query' to remain its own bug.  Does the falcon_lock_timeout solve this problem?

The bug has nothing to do with a timeout.

The first connection is inserting a lot of rows while the second connections inserts a duplicate record. While the first connection is running, the second connection aborts. The second connections query should not be executed (in theory).

However I tried this with InnoDB and the second connection get's a duplicate key error:

[03:17] root@test>insert into t2 values (1, 'cdeas');
Query aborted by Ctrl+C
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

I am setting this to "Not a Bug".

Hi Hakan,

From my point of view, this is still a bug but which seems to affect both InnoDB and Falcon storage engine :)

Jocelyn,

this is not a bug because Control-C is not guaranteed
to stop every situation.

Best regards,

Hakan