MySQL Bugs: #22183: Loss of data after INSERT and ALTER with two interleaving transactions

Bug #22183	Loss of data after INSERT and ALTER with two interleaving transactions
Submitted:	9 Sep 2006 12:40	Modified:	5 Dec 2006 16:56
Reporter:	Georg Richter
Status:	Closed
Category:	Server: Falcon	Severity:	S2 (Serious)
Version:	5.2	OS:	Any (all)
Assigned to:	Bugs System	Target Version:

Description:
I have two transactions.
Both of them do INSERTs and both of them COMMIT successfully.
But when I do a final SELECT, there are no rows.
The evident cause is that I also ALTER to add and drop the primary key.

How to repeat:
/*
  Start two instances of mysql client.
  Call them T1 and T2.
  Execute the instructions here precisely as described.
*/

/* With T1, do: */
create database falcon6;
use falcon6;
set @@autocommit=0;
create table t (s1 int primary key, s2 varchar(10000), s3 blob) engine=falcon;
insert into t values (3,'a','a');

/* With T2, do: */
use falcon6;
alter table t drop primary key;
set @@autocommit=0;
start transaction;
insert into t values (3,'b','b');

/* With T1, do: */
alter table t add primary key (s1);
commit;

/* With T2, do: */
commit;
alter table t drop primary key; /* causes an error message */

/* With T1, do: */
select * from t;

The result of the final SELECT on T1 looks like this:
"
mysql> select * from t;
Empty set (0.00 sec)
"

Incidentally, after this I tried to shutdown with mysqladmin,
and mysqld crashed. But that detail is not repeatable.

I don't see anyway to detect or reject an "alter table" from within a storage engine.  The
first hint that a table rebuild is underway is the rename call, which is both ambiguous
and way too late to reject the "alter".

If we had a hint that a table rebuild was underway, we could detect a concurrent
transaction with uncommitted updates, but I don't see any way this can be done.

This is fundamentally a server bug.  The server should not attempt to rebuild a table
with uncommitted updates.

Falcon now gives an error when attempting an "alter table" (or variation)
with outstanding transactions.  A better solution is to block, but that
can wait until after alpha.

The message is less that ideal, but the server insists on using its
own text rather than getting more explicit text from the storage
engine.

Adjusted falcon_bug_233.test to reflect Falcon behaviour. The test passes now on Linux
32-bit, change set 1.2387, 2006-12-04.

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_233                 [ pass ]           2096
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 2.096 seconds actually executing testcases

Regards, Hakan