Bug #31345 "optimize table" implicitly commits a transaction

• Submitted: 2 Oct 2007 14:33
• Modified: 2 Oct 2007 15:15
• Reporter: Andrejs Dubovskis
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.48
• OS: Linux
• CPU Architecture: Any

[2 Oct 2007 14:33] Andrejs Dubovskis

Description:
Mysql commits the transcation if optimize table called.

In version 4.1 it was not.

How to repeat:
mysql> create table a (b int not null) engine=innodb;
Query OK, 0 rows affected (0.38 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (1);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (2);       
Query OK, 1 row affected (0.00 sec)

mysql> optimize table a;
+-----------+----------+----------+----------+
| Table     | Op       | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| andrejs.a | optimize | status   | OK       | 
+-----------+----------+----------+----------+
1 row in set (0.42 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a;
+---+
| b |
+---+
| 2 | 
+---+
1 row in set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.48-enterprise-gpl-log | 
+---------------------------+
1 row in set (0.00 sec)

[2 Oct 2007 15:15] MySQL Verification Team

Thank you for the bug report. Please see:

http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

"For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds
the table to update index statistics and free unused space in the clustered index."

The reason to be duplicate is the below bug report:
http://bugs.mysql.com/bug.php?id=28727