MySQL Bugs: #8151: TRUNCATE TABLE wrongly starts a transaction

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0, 4.1, 5.0	OS:	Any (all)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
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.

Marko,

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

Regards,

Heikki

Heikki,

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);
+     }
       DBUG_RETURN(error);

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?

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.

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().

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
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fixed in 5.0.3

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?

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.

Hey!

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?

Gili,
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?

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:

  http://lists.mysql.com/internals/24023

Sorry.
Fixed in 4.0.25 and 4.1.12