Bug #14965 transaction

• Submitted: 16 Nov 2005 0:13
• Modified: 18 Nov 2005 14:59
• Reporter: ch gq
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S1 (Critical)
• Version: 5.013/5.0.17 BK/4.1
• OS: Windows (winxp/Linux)
• Assigned to: Stefan Hinz
• CPU Architecture: Any

[16 Nov 2005 0:13] ch gq

Description:
when use transaction, truncate table command can't rollback transaction.
data lost.

How to repeat:
start transaction;
truncate table table_name;
rollback;

Suggested fix:
rollback the transaction.

[16 Nov 2005 0:14] ch gq

my email :[e-mail]

[16 Nov 2005 2:17] MySQL Verification Team

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.15-nt

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

mysql> create table tb1 (id int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb1 set id =1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> delete from tb1;
Query OK, 1 row affected (0.00 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)

mysql> alter table tb1 engine=innodb;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into tb1 set id =1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> delete  from tb1;
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> truncate table tb1;
Query OK, 1 row affected (0.03 sec)

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

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

mysql>

miguel@hegel:~/dbs> cd 5.0
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db7 
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug

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

mysql> create table tb1 (id int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tb1 set id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> truncate table tb1;
Query OK, 1 row affected (0.02 sec)

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

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

mysql>

[16 Nov 2005 2:51] MySQL Verification Team

miguel@hegel:~/dbs/4.1> bin/mysqladmin -uroot create db7
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-debug-log

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

mysql> create table tb1 (id int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into tb1 set id=1;
Query OK, 1 row affected (0.04 sec)

mysql> select * from tb1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> truncate table tb1;
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from tb1;
Empty set (0.01 sec)

mysql>

[16 Nov 2005 6:42] Jan Lindström

You cannot roll back a truncate statement. This is not directly documented at the moment. 
Truncate table is an Oracle SQL extension adopted in MySQL and you cannot roll back a truncate statement in Oracle. Thus, this behaviour is expected and not a bug.

[17 Nov 2005 8:49] Jan Lindström

Please add information to documentation that truncate table can't be rolled back.

[18 Nov 2005 14:59] Stefan Hinz

The Manual says that TRUNCATE TABLE causes an implicit commit:
http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html