MySQL Bugs: #39132: Falcon reports an update conflict on an unrelated table when doing a SELECT

Bug #39132	Falcon reports an update conflict on an unrelated table when doing a SELECT
Submitted:	29 Aug 2008 18:51	Modified:	26 May 2010 17:50
Reporter:	Philip Stoev	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0-falcon-team	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER, pb2, pushbuild, test failure

Description:
When executing the falcon_online_alter random query generation test, the following message was seen:

# 2008-08-29 20:00:41 [1487] Query: SELECT int_nokey FROM BBB WHERE date_nokey < 7 failed: 1296 Got error 124 'update conflict in table TEST.D' from Falcon

There are several concerns with it:
* This is a SELECT statement, how can it fail due to an update conflict?
* Falcon reports update conflict in table D, whereas we are selecting from table BBB;
* The error code 1296 returned, ER_GET_ERRMSG is not specific. We should have a more specific error code than that.

See

http://clustra.norway.sun.com/~bteam/pb2/web.py?action=archive_download&archive_id=14965&p...

How to repeat:
Clone the mysql-test-extra-6.0 tree and run:

$ cd mysql-test/gentest
$ runall.pl \ 
  --basedir=/path/to/mysql-6.0-falcon \ 
  --engine=Falcon \ 
  --grammar=conf/falcon_online_alter.yy \ 
  --threads=10 \ 
  --queries=100000

Here is another weird error:

Query: ALTER TABLE AA DROP KEY w
Error: 1296 Got error 124 'duplicate values for key BB..PRIMARY_KEY in table TEST.BB' from Falcon

For this query INSERT INTO CC ( datetime_key , time_nokey ) VALUES ( 4 , 5 );

MySQL returned this error: 1296 Got error 124 'duplicate values for key #SQL-1FC7_8$Z in table TEST.#SQL-1FC7_8'

When this happens, transaction->connection->getCurrentStatement() returns:

ALTER TABLE A ADD KEY y ( time_nokey , datetime_nokey ) , ADD UNIQUE z ( datetime_nokey , date_key )

The original INSERT and this ALTER were issued by the same client thread:

THIS IS THE ALTER   8 Query     ALTER TABLE A ADD KEY y ( time_nokey , datetime_nokey ) , ADD UNIQUE z ( datetime_nokey , date_key )
                    8 Query     ALTER TABLE AA DROP KEY l
                    8 Query     SELECT time_key FROM A WHERE datetime_nokey < 1
                    8 Query     ALTER TABLE DD DROP KEY j
                    8 Query     SELECT datetime_nokey FROM CCC WHERE time_nokey < 6
                    8 Query     INSERT INTO BBB ( date_nokey , datetime_nokey ) VALUES ( 1 , 5 )
                    8 Query     SELECT pk FROM D WHERE int_nokey < 3 LIMIT 8
                    8 Query     COMMIT
                    8 Query     ALTER TABLE BBB DROP KEY j
                    8 Query     SELECT int_key FROM CC WHERE time_key < 1
                    8 Query     INSERT INTO BB ( int_nokey , varchar_key ) VALUES ( 5 , 8 )
                    8 Query     INSERT INTO D ( int_key , time_key ) VALUES ( 1 , 5 )
                    8 Query     INSERT INTO E ( time_nokey , time_nokey ) VALUES ( 6 , 8 )
                    8 Query     SELECT int_key FROM BB WHERE time_nokey < 8
THIS IS THE INSERT  8 Query     INSERT INTO CC ( datetime_key , time_nokey ) VALUES ( 4 , 5 )

>Chris wrote;  
>Vlad, Kevin, Just spoke to Philip about this one
>I think it should be assigned to me.

Possibly related: Bug#28178, Bug#39670

Similar errors (though the SELECTed table is the table mentioned in the error message) have occurred lately in the falcon_limit test on Pushbuild 2, e.g.:

# 02:49:40 Query: SELECT * FROM D AS X LEFT JOIN E AS Y ON ( X . `int_key` = Y . `date_key` ) WHERE X . `date_key` = 9  LIMIT 6 failed: 1296 Got error 124 'update conflict in table TEST.D' from Falcon
# 02:49:48 Query: SELECT * FROM E AS X LEFT JOIN D AS Y ON ( X . `varchar_key` = Y . `int_key` )   LIMIT 9 failed: 1296 Got error 124 'update conflict in table TEST.D' from Falcon
# 02:51:32 Query: SELECT * FROM D AS X WHERE X . `date_key` > '2002-07-13'  LIMIT 3 failed: 1296 Got error 124 'table has uncommitted updates' from Falcon
# 02:51:32 Error: 'Got error 124 'table has uncommitted updates' from Falcon' returned on a SELECT query.

Marking this bug as a pb2 test failure.

Recent occurrence, falcon_online_alter on windows:

http://clustra.norway.sun.com/~bteam/pb2/web.py?action=archive_download&archive_id=345110&...

SELECT `pk` FROM `e` WHERE `int_key` < 5 GROUP BY `int_key` LIMIT 2 failed: 1296 Got error 124 'duplicate values for key CCC$B in table TEST.CCC' from Falcon

This exception occurs in one place only: 

Table::checkUniqueRecordVersion(recordNumber, index, transaction, record...)
{
...
for (Record *dup = rec; dup; dup = dup->getPriorVersion())
   {
   ...
   if (isDuplicate(index, record, dup))
      {
      ...
      // Found a duplicate conflict or a deadlock.

      const char *text = "duplicate values for key %s in table %s.%s";

      SQLEXCEPTION exception(code, text, index->name, schemaName, name);
      exception.setObject(schemaName, index->name);

      throw exception;
      }

The message text is assembled from Table::name and Table::schemaName, and was therefore consistent at the time of the exception. checkUniqueRecordVersion() is only called for inserts and updates. What happened after the exception was thrown?

To explore:
1) What happens if an index is deleted during checkUniqueRecordVersion()?
2) Why did the SELECT fail?
3) Was the SELECT issued by the same client as the duplicate exception?

To answer your question #3, yes the same thread that issued the SELECT issued the ALTER. The ALTER was a few statements before the SELECT