MySQL Bugs: #22164: ROLLBACK does not work under strange conditions

Bug #22164	ROLLBACK does not work under strange conditions
Submitted:	9 Sep 2006 9:27	Modified:	14 Dec 2006 10:04
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.2	OS:	Linux (Linux)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
ROLLBACK does not work under strange conditions

How to repeat:
 Test derived from the NIST testsuite

CREATE SCHEMA testdb_2;
# Establish connection session2 (root)
connect(localhost,root,,test,MYSQL_PORT,MYSQL_SOCK);
USE testdb_2;
#-------- Switch to connection default (root) --------
CREATE TABLE t2
(COL5 BIGINT) ENGINE = Falcon;
GRANT UPDATE (COL5) ON t2 TO testdb_2@localhost;
CREATE TABLE t1( f1 BIGINT) ENGINE = Falcon;
#-------- Switch to connection session2 (root) --------
CREATE TABLE t3
(COL5 DECIMAL(7,2)) ENGINE = Falcon;
GRANT ALL PRIVILEGES ON t3 TO root@localhost;
#------- Switch to connection default (root) -------
SET AUTOCOMMIT = 0;
INSERT INTO testdb_2.t3 SET COL5 = 100.3;
SELECT * FROM t1;
f1
INSERT INTO t1 VALUES( 1 );
SELECT * FROM t1;
f1
1
ROLLBACK WORK;
# !!! ------- Why do I get a result set after ROLLBACK ? -------- !!!
SELECT * FROM t1;
f1
1 <----------------

This test has strange properties.
1. Many statements before INSERT INTO t1 + ROLLBACK do not affect the
   table t1. But whenever I tried to remove one of these statements
   the bug disappeared.
2. Whenever I tried to use MyISAM for t2 or t3 the error disappeared.
3. I have the feeling that the data type DECIMAL(7,2) for t3.COL5
   is somehow "guilty".

I used BUILD/compile-pentium-debug-max.
Additional Information 	- Please use the attached testscript.
- I fear that the phenomenon disappears on other
  hardware (64 Bit or non x86) or when new sources are pushed
  or when other compile options are used though the error is most
  probably not fixed.
affects Tests 	-
Changeset 	ChangeSet@1.2230, 2006-06-21
Operating System 	SuSE 9.3
Platform 	x86-32

I haven't been able to reproduce this.  Could you verify that this is still outstanding?

I was able to reproduce this bug on fluffy (user mleich)
mysql-5.1-falcon last ChangeSet@1.2293, 2006-09-15
compile-pentium-debug-max

As long as the "standard" execution of this tests is disabled
(The testcase is listed in t/disabled.def) you have to run it with

./mysql-test-run.pl --skip-ndb  --enable-disabled falcon_bug_194

Fails for me too:
ChangeSet@1.2293, 2006-09-15 10:53:33-04:00

patg@fluffy:~/mysql-build/mysql-5.1-falcon/mysql-test> ./mysql-test-run.pl --enable-disabled falcon_bug_194

...

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

falcon_bug_194                 [ fail ]

Errors are (from /home/patg/mysql-build/mysql-5.1-falcon/mysql-test/var/log/mysqltest-time) :
mysqltest: Result length mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/falcon_bug_194.result     2006-07-19 15:07:48.000000000 +0300
--- r/falcon_bug_194.reject     2006-09-15 21:54:50.000000000 +0300
***************
*** 26,31 ****
--- 26,32 ----
  # !!! ------- Why do I get a result set after ROLLBACK ? -------- !!!
  SELECT * FROM t1;
  f1
+ 1
  DROP SCHEMA testdb_2;
  DROP TABLE t2;
  DROP TABLE t1;
-------------------------------------------------------

Why do I get a result set after ROLLBACK ?  Because the default connection has not been changed to use the Falcon engine.  It is still using the default storage engine MYISAM which is not transactional.  The INSERT into t1 is visible by everyone and cannot be rolled back.  In this test tables t1 and t2 are MYISAM and table t3 is Falcon.  

We need to add 
     SET STORAGE ENGINE = falcon;
to the default connection for this test to work.

Actually, I was wrong in my last comment where I indicated it was not a bug.
The three files are indeed created using Falcon, but they are created in two different databases.  The default connection does not USE testdb_2;  So it creates t1 and t2 in the test database (schema and database are currently synonimous in Falcon.

When the Rollback happens, it is only applied to testdb_2.t3.  The change in test.t1 is still pending.  That is why the default connection can see the change after the rollback.  The second connection cannot see it.  

This is a bug because a commit or rollback should apply to pending changes on all databases that a connection has open.

The problem with this bug is that the savepoint reported to the MySQL engine is only associated with one database, whereas the transaction is applied to changes in two databases.  In order to fix this Falcon needs a StorageSession object that can apply transactional statements to each database open by that session.

This bug has been fixed by recent changes by Jim in which each of the transaction related calls are distributed to each database opened by a mySqlThread.  

In addition, the StorageHandler class contains savepointSet() and savepointRelease() calls that create a linked list of each savepoint in each database.

So now, transactions can safely be used acrose multiple databases.

Tested on Linux 32-bit and Linux 64-bit with change set 1.2372, 2006-12-13.

Starting Tests in the 'main' suite

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

falcon_bug_194                 [ pass ]           1315
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 1.315 seconds actually executing testcases

Regards, Hakan