MySQL Bugs: #36671: OPTIMIZE TABLE causes implicit commit

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:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.24	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	commit, implicit, Optimize

Description:
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;
  begin;
  set autocommit = 0;
  insert into t1 values (1);

In second connection
  optimize table t1;

In first connection
  rollback;

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.

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.

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.

Thank you for the feedback.

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