Bug #36671 OPTIMIZE TABLE causes implicit commit
Submitted: 12 May 2008 16:32 Modified: 12 May 2008 19:30
Reporter: Tim Clark Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.24 OS:Any
Assigned to: CPU Architecture:Any
Tags: commit, implicit, Optimize

[12 May 2008 16:32] Tim Clark
OPTIMIZE TABLE should be added to the list of statements that cause an implicit transaction commit (Section 12.4.3 of the 5.1 manual).

InnoDB appears to turn the OPTIMIZE TABLE into an ALTER TABLE, which is documented as implicitly committing. However, this is not obvious unless one looks at the source code.

How to repeat:
In first connection
  drop table t1;
  create table t1 (c1 int) engine=innodb;
  set autocommit = 0;
  insert into t1 values (1);

In second connection
  optimize table t1;

In first connection

mysql> show warnings;
| Level   | Code | Message                                                       |
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

Suggested fix:
Add OPTIMIZE TABLE to the list of implicitly committing statements.
[12 May 2008 17:39] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please add SHOW CREATE TABLE t1; to your test to be sure table is InnoDB.
[12 May 2008 19:16] Tim Clark
Ack! You are correct. InnoDB plugin was not installed when I ran my test, and the actual table is MyISAM. This bug can be closed.
[12 May 2008 19:30] Sveta Smirnova
Thank you for the feedback.

Report closed as "Not a Bug" according to last comment.