Bug #8151 TRUNCATE TABLE wrongly starts a transaction
Submitted: 26 Jan 2005 21:36 Modified: 14 Apr 2005 20:32
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0 OS:Any (all)
Assigned to: Sergei Golubchik CPU Architecture:Any

[26 Jan 2005 21:36] Marko Mäkelä
TRUNCATE TABLE is not a transactional command. That is, it can only be executed if there is no uncommitted transaction, and TRUNCATE TABLE cannot be rolled back.

However, TRUNCATE TABLE seems to start a transaction on the MySQL layer if the table handler supports transactions. This doesn't seem right: TRUNCATE TABLE should implicitly commit, so that all table handlers behave in the same way.

How to repeat:
create table m(a int)type=myisam;
create table i(a int)type=innodb;
set autocommit=0;
truncate table m;
truncate table m;
truncate i;
truncate i;

The following error message is issued for the last command:
ERROR 1192: Can't execute the given command because you have active locked tables or an active transaction

Suggested fix:
Make TRUNCATE TABLE implicitly commit afterwards.
[26 Jan 2005 23:06] Heikki Tuuri

maybe add a call of ha_commit... to the TRUNCATE implementation in sql_table.cc?


[27 Jan 2005 9:31] Marko Mäkelä

I tried that in 5.0, and it appeared to work.

       error= mysql_delete(thd, table_list, (COND*) 0, (SQL_LIST*) 0,
	 		  HA_POS_ERROR, 0);
       ha_enable_transaction(thd, TRUE);
+      /* Commit or rollback */ 
+      if (thd->active_transaction())
+      {
+	if (!error)
+	{
+	  if (ha_commit(thd))
+	    error= 1;
+	}
+	else
+	  (void) ha_rollback(thd);
+     }

I'd have used ha_autocommit_or_rollback(), but it only commits the current statement and not the whole transaction.

Which version should the change go to? 4.1?
[28 Jan 2005 15:37] Marko Mäkelä
Hmm, it looks like this can be fixed on the InnoDB side in 5.0. But that won't fix it for 4.0 or 4.1.
[31 Jan 2005 12:27] Marko Mäkelä
My recent patch to 5.0.3 partially masks this bug. Here is how it can be reproduced in 5.0:

set autocommit=0;
create table bug8151(a int primary key)engine=innodb;
create table bug8151c(a int primary key,constraint foreign key(a)references bug8151(a))engine=innodb;
truncate table bug8151;
truncate table bug8151;
-- you'll get ERROR 1192 here
commit; -- clear ERROR 1192
set foreign_key_checks=0;
truncate table bug8151;
truncate table bug8151;
-- no error, since ha_innobase::delete_all_rows() will commit

That is, the bug occurs if TRUNCATE cannot be done by handler::delete_all_rows(), but it has to resort to delete_row().
[2 Mar 2005 9:32] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at

Additional info:

fixed in 5.0.3
[2 Mar 2005 12:20] Marko Mäkelä
Sergei, I'm sorry, but it still seems to fail for me, using the current bk version (ChangeSet@1.1779, 2005-03-02 11:49:50+01:00, ingo@mysql.com). Did you push the change?
[2 Mar 2005 21:06] Marko Mäkelä
Hmm, I now see that the bug indeed has been fixed. I wonder what I did wrong last time I tested it. Thanks, Sergei, and sorry for the confusion on my part.
[14 Apr 2005 5:24] Gili

How come no fix for MySQL 4.x? I don't plan on upgrading to MySQL 5.x until it's stable and this bug is killing me for 4.x. Is there at least a workaround?
[14 Apr 2005 11:03] Marko Mäkelä
MySQL 4.0 is approaching its end of life, but this bug could be fixed in 4.1, but the fix would be different than in 5.0.3. However, the bug has low priority.
Does the workaround TRUNCATE t;COMMIT; work for you?
[14 Apr 2005 16:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[14 Apr 2005 20:32] Sergei Golubchik
Fixed in 4.0.25 and 4.1.12