Bug #22183 Loss of data after INSERT and ALTER with two interleaving transactions
Submitted: 9 Sep 2006 10:40 Modified: 5 Dec 2006 15:56
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:5.2 OS:Any (all)
Assigned to: Jim Starkey CPU Architecture:Any

[9 Sep 2006 10:40] Georg Richter
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.
[12 Sep 2006 16:40] Jim Starkey
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.
[9 Nov 2006 18:46] Jim Starkey
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.
[5 Dec 2006 15:56] Hakan Küçükyılmaz
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