MySQL Bugs: #11214: Issuing RollBack for a MyISAM table gives error when not in Transaction

Bug #11214	Issuing RollBack for a MyISAM table gives error when not in Transaction
Submitted:	9 Jun 2005 17:05	Modified:	30 Jun 2007 1:08
Reporter:	Andrew Schmidt	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.24	OS:	FreeBSD (FreeBSD 4.4)
Assigned to:		CPU Architecture:	Any

Description:
MySQL gives an error "ERROR 1196: Warning:  Some non-transactional changed tables couldn't be rolled back"  when issuing a RollBack for a MyISAM table even though a transaction has not been started and AUTOCOMMIT is set to 1

How to repeat:
gd> SET AUTOCOMMIT=1;
Query OK, 0 rows affected (0.00 sec)

gd> CREATE TABLE test1_innodb (f int) type = innodb;
Query OK, 0 rows affected (0.01 sec)

gd> INSERT INTO test1_innodb VALUES (1);
Query OK, 1 row affected (0.01 sec)

gd> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

gd> SELECT * FROM test1_innodb;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Ok everything looks good and no errors.  I didn't start a transaction so that record should exist.

Now for MyISAM:

gd> CREATE TABLE test2_mysql (f int) type = myisam;
Query OK, 0 rows affected (0.01 sec)

gd> INSERT INTO test2_mysql VALUES (1);
Query OK, 1 row affected (0.00 sec)

gd> ROLLBACK;
ERROR 1196: Warning:  Some non-transactional changed tables couldn't be rolled back

An error shouldn't be issued here as I was not inside a transaction.  AUTOCOMMIT should be finishing any transaction on the INSERT statement and I didn't start a transaction with "BEGIN" or "START TRANSACTION"

Tested in 4.1.12 and no error occured.  Appears to only be in the 4.0.24 line.

That warning for MyISAM engine is expected as documented at:

http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html

The other non-transactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called ``atomic operations.'' In transactional terms, MyISAM  tables effectively always operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable integrity with higher performance.

The same happens with 4.1.XX, just the warning message is handled
in other way:

Your MySQL connection id is 1 to server version: 4.1.13-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test2_mysql (f int) type = myisam;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO test2_mysql VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Is there any chance the change in 4.1.x will get backported to 4.0.x?  

I guess my main problem is that no transaction has been started.  so how can a rollback cause an error or warning?

still a bug, though a minor one