MySQL Bugs: #34892: Transaction handling in select

Bug #34892	Transaction handling in select_create::abort let's Falcon fail
Submitted:	27 Feb 2008 16:34	Modified:	9 Jan 2009 14:11
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0-runtime	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	pushbuild, test failure

Description:
Some Falcon tests fails randomly:
  - falcon_bug_23962
  - falcon_bug_28095_II
  - falcon.falcon_select
  - falcon.falcon_bug_23818_A

I'm going to disable them until the huge merge from Falcon tree.

How to repeat:
https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=mysql-6.0-runtime&order=175

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43091

ChangeSet@1.2577, 2008-02-27 20:40:21+03:00, anozdrin@quad. +1 -0
  Disable failing tests (Bug#34892).

Failure of falcon_bug_28095_II  confirmed on 32-bit Linux.

Pushed into 6.0.5-alpha

This does not happen in the current mysql-6.0-falcon tree. However, in the latest mysql-6.0 tree we get this error:

SET @@storage_engine = 'Falcon';
DROP TABLE IF EXISTS t1;
SET @@autocommit = 0;
CREATE TABLE t1 (PRIMARY KEY (a)) SELECT 1 AS a UNION ALL SELECT 1;
-- ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
CREATE TABLE t1 (a int);
DROP TABLE t1;
-- CREATE TABLE t1 (a int)' failed: 1005: Can't create table 'test.t1' (errno: 156)

After merging mysql-6.0 into mysql-6.0-falcon tree, the above sequence also fails in mysql-6.0-falcon tree.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43405

ChangeSet@1.2583, 2008-03-04 17:59:46+01:00, hakank@lu0011.(none) +2 -0
  Test case to demonstrate failure in Bug #34892.

See also Bug#35055

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43569

ChangeSet@1.2587, 2008-03-07 10:52:41+01:00, hakank@lu0011.(none) +1 -0
  Disabling falcon_bug_23945.test due to Bug# 34892.

Pushed into 6.0.5-alpha

Changed synopsis to reflect actual problem.

Pushed into 6.0.4-alpha

This bug is still open and in progress.

The problem is that the server isn't calling savepoint_rollback after the failed write_row.  This leaves uncommitted record in the transaction which, in turn, causes the drop table to fail.

The server comments suggests that the intention was to rollback the savepoint; however, the code doesn't do it.

This is a particularly egregious because the table is deleted in the server but not Falcon, making it impossible to either delete or re-create the table.

Sergey,  Can you take a look at this to see how hard it would be to do in the server?  The category is Server::General, but it prevents falcon_bug_23945 from working and it is a 6.0 bug.  I am changing the category back to Falcon so we can track it better.  Let me know if you need help from a server guru.

Bug#39157 was opened and marked as a duplicate of this one.  Please see Bug#39157 for another easy testcase.  When this is fixed, please try that testcase also.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54523

2832 Sergey Vojtovich	2008-09-25
      BUG#34892 - Transaction handling in select_create::abort let's Falcon fail
      
      CREATE ... SELECT that fails (e.g. due to duplicate key) with
      Falcon storage engine prevents to create or drop a table with
      the same name in future.
      
      Added missing call to trans_rollback_stmt(), which was likely
      lost during merge.

Patch reviewed, OK to push

Back to In progress, as the patch introduces extra binlog event for failed CREATE ... SELECT, which should never happen.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54690

2842 Sergey Vojtovich	2008-09-29
      BUG#34892 - Transaction handling in select_create::abort let's Falcon fail
      
      CREATE ... SELECT that fails (e.g. due to duplicate key) with
      Falcon storage engine prevents to create or drop a table with
      the same name in future.
      
      Added missing call to trans_rollback_stmt(), which was likely
      lost during merge.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55044

2842 Sergey Vojtovich	2008-10-02
      BUG#34892 - Transaction handling in select_create::abort let's Falcon fail
      
      CREATE ... SELECT that fails (e.g. due to duplicate key) with
      Falcon storage engine prevents to create or drop a table with
      the same name in future.
      
      Added missing trans_rollback_stmt(), which was removed by accident by
      patch for BUG#12713.

A noter has been added to the 6.0.8 changelog: 

Creating a new Falcon table using CREATE TABLE ... SELECT where the table uses a primary key, and rows contain duplicate keys, could lead to a table being created but not populated. Because the pending (bad) record was not committed, the table remains in a state that means it cannot be dropped or recreated.