Bug #37016 TRUNCATE TABLE removes some rows but not all
Submitted: 27 May 2008 19:25 Modified: 29 Jan 2009 20:17
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1, 6.0-bk OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious) / R2 (Low) / E3 (Medium)

[27 May 2008 19:25] Guilhem Bichot
Description:
Please test older versions of MySQL.

We expect TRUNCATE TABLE to delete all rows or none, but this is not the case.
For example:
-- source include/have_innodb.inc
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
insert into t1 values(1);
insert into t1 values(2);
select * from t1;
insert into t2 values(10, 2);
error 1451;
truncate table t1;
select * from t1;
rollback;
select * from t1;

TRUNCATE does row-by-row delete here, deletes "1" ok, fails to delete "2" (referenced by t2), then commits the transaction (see ha_enable_transaction() at end of mysql_delete()), so the deletion of "1" is committed, hence the result:
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
insert into t1 values(1);
insert into t1 values(2);
select * from t1;
id
1
2
insert into t2 values(10, 2);
truncate table t1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
select * from t1;
id
2
rollback;
select * from t1;
id
2

See, above: t1 now contains "2. Truncate deleted only half of the rows.
The documentation says:
http://dev.mysql.com/doc/refman/5.1/en/truncate.html
regarding InnoDB:
"If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error"
But it does not say that the half-deletion will be committed. One would rather expect it to be rolled-back (atomicity: all or nothing). Or one would expect it to behave like DROP+CREATE: in the above tescase, if one uses DROP instead  of TRUNCATE, the DROP fails with error, and table is intact (looking at InnoDB's code, DROP fails as soon as it finds that the table is referenced by a foreign key).
The workaround is to SET FOREIGN_KEY_CHECKS=0 before truncating, but it's not a fix.
Some very well-known RDBMS just prevents TRUNCATE if the table is referenced by a foreign key (except if it is a self-reference).

I am not marking it an InnoDB bug because the deficiency is in my opinion the implicit commit done by ha_enable_transaction() in mysql_truncate(), which is a MySQL problem.

How to repeat:
see description.
[27 May 2008 20:44] Sveta Smirnova
Thank you for the report.

TRUNCATE TABLE listed in "Statements That Cause an Implicit Commit" at http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

So I close this report as "Not a Bug".
[28 May 2008 19:33] Guilhem Bichot
Hello Sveta,
Thank you for looking at my bug report.
Of course, TRUNCATE causes an implicit commit i.e, as the manual page
says, "implicitly end a transaction, as if you had done a COMMIT
before executing the statement." But the bug report is about something
different: the table has 2 rows when TRUNCATE starts, there is no open
transaction at this moment, and the table has 1 row when TRUNCATE
ends. That is the bug. That TRUNCATE deletes only half of the rows.
Could you please reconsider your decision and, I hope, mark this as
verified?
[31 May 2008 13:25] Sveta Smirnova
Thank you for the feedback.

Set to "Verified". Really MySQL behaves as described in the report since version 5.1.
[7 Oct 2008 20:56] Konstantin Osipov
Don't see how this can be fixed reliably - truncate deletes records outside the context of transactions (we call ha_disable_transactions() at start). 
Perhaps it worked before because InnoDB ignored ha_disable_transactions().
In new foreign keys TRUNCATE will fail if there is a child table.
[7 Oct 2008 20:56] Konstantin Osipov
Why is this tagged with "regression"?
Is it 5.1 specific?
[8 Oct 2008 6:20] Sveta Smirnova
Yes, there are different results in 5.0 and 5.1

Test case:

$cat src/tests/t/bug37016.test 
-- source include/have_innodb.inc
set autocommit=0;
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
insert into t1 values(1);
insert into t1 values(2);
select * from t1;
insert into t2 values(10, 2);
error 1451;
truncate table t1;
select * from t1;
rollback;
select * from t1;

Result:
=====mysql-5.0=====
=====bug37016=====
set autocommit=0;
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
insert into t1 values(1);
insert into t1 values(2);
select * from t1;
id
1
2
insert into t2 values(10, 2);
truncate table t1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
select * from t1;
id
1
2
rollback;
select * from t1;
id
1
2

=====mysql-5.1=====
=====bug37016=====
set autocommit=0;
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
insert into t1 values(1);
insert into t1 values(2);
select * from t1;
id
1
2
insert into t2 values(10, 2);
truncate table t1;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
select * from t1;
id
2
rollback;
select * from t1;
id
2
[12 Nov 2008 16:21] Kristofer Pettersson
Progress report: Serg claims the implicit commit is not a deficiency in the current design and that the bug is a result of an API violation.

InnoDB uses foreign keys that the MySQL server knows nothing about and that InnoDB internal restrictions then causes the delete to fail.

A proposal to Innobase would be to change 
ha_innobase::delete_all_rows(..) {
<snip>
	/* Truncate the table in InnoDB */

	error = row_truncate_table_for_mysql(prebuilt->table, prebuilt->trx);
	if (error == DB_ERROR) {
		// DONT RESORT TO ha_innobase::delete_row() after all
                // but return an error code which makes sense.
        }
	error = convert_error_code_to_mysql(error, NULL);

	DBUG_RETURN(error);
}

As a side note, I think that the MySQL server can patch around the bug by not doing the implicit commit in ha_enable_transaction(thd, TRUE) in case of an error when doing the delete:

mysql_truncate(..)
{
<snip>

  ha_enable_transaction(thd, FALSE);
  mysql_init_select(thd->lex);
  bool save_binlog_row_based= thd->current_stmt_binlog_row_based;
  thd->clear_current_stmt_binlog_row_based();
  error= mysql_delete(thd, table_list, (COND*) 0, (SQL_LIST*) 0,
                      HA_POS_ERROR, LL(0), TRUE);

- ha_enable_transaction(thd, TRUE);
- if (!error)
    ha_enable_transaction(thd, TRUE);

  /*
    Safety, in case the engine ignored ha_enable_transaction(FALSE)
    above. Also clears thd->transaction.*.
  */
  error= ha_autocommit_or_rollback(thd, error);
  ha_commit(thd);
<snip>
[12 Nov 2008 16:27] Sergei Golubchik
Another solution in ha_innobase.cc don't "disable transactions" for TRUNCATE if truncate is done the row-by-row way and the table has foreign keys.
[14 Nov 2008 17:04] Heikki Tuuri
row0mysql.c in 5.1-zip:

If the table is referenced by a FOREIGN KEY, InnoDB refuses to use the fast TRUNCATE operation which would simply remove the .ibd file. I think then MySQL falls back to the delete row-by-method for TRUNCATE.

        if (foreign && trx->check_foreigns) {
                FILE*   ef      = dict_foreign_err_file;

                /* We only allow truncating a referenced table if
                FOREIGN_KEY_CHECKS is set to 0 */

                mutex_enter(&dict_foreign_err_mutex);
                rewind(ef);
                ut_print_timestamp(ef);

                fputs("  Cannot truncate table ", ef);
                ut_print_name(ef, trx, TRUE, table->name);
                fputs(" by DROP+CREATE\n"
                      "InnoDB: because it is referenced by ", ef);
                ut_print_name(ef, trx, TRUE, foreign->foreign_table_name);
                putc('\n', ef);
                mutex_exit(&dict_foreign_err_mutex);

                err = DB_ERROR;
                goto funct_exit;
        }

TRUNCATE by deleting row-by-row:

1) MySQL should first commit the current transaction. InnoDB starts a new one when the delete starts.

2) MySQL should roll back the current transaction if the delete returns an error. MySQL should then return an error to the client.

The requirement that 'TRUNCATE does an implicit commit' is satisfied by this algorithm. And the TRUNCATE either succeeds, or does nothing. Just like DELETE FROM t.
[15 Nov 2008 10:54] Sergei Golubchik
Indeed, it's MySQL that falls back to row-by-row deletion. Then, I suppose, as a fix we should "enable transactions" when falling back.
[15 Nov 2008 17:31] Kristofer Pettersson
Sergei: Isn't this what we do but it causes an implicit commit which trash the ha_list? Not doing the implicit commit in case of an error is of course an easy work around which seems to work.
[17 Nov 2008 11:25] 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:

  http://lists.mysql.com/commits/58942

2704 Kristofer Pettersson	2008-11-17
      Bug#37016 TRUNCATE TABLE removes some rows but not all
      
      TRUNCATE TABLE caused some rows to be deleted but not all if an error
      occurred during row-by-row deletion.
      
      The error was caused by a foreign key restriction imposed by InnoDB SE
      but handled incorrectly by the server code. In stead of doing a rollback
      in case of an error, an implicit commit was issued which caused the list
      of on-going transactions to be cleared.
      
      This patch fixes the problem by avoiding the implicit commit so that a
      rollback can be performed instead.
[19 Nov 2008 10:16] 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:

  http://lists.mysql.com/commits/59183

2704 Kristofer Pettersson	2008-11-19
      Bug#37016 TRUNCATE TABLE removes some rows but not all
            
      TRUNCATE TABLE caused some rows to be deleted but not all if an error
      occurred during row-by-row deletion.
            
      The error was caused by a foreign key restriction imposed by InnoDB SE
      but handled incorrectly by the server code. In stead of doing a rollback
      in case of an error, an implicit commit was issued which caused the list
      of on-going transactions to be cleared.
            
      This patch fixes the problem by changing the order of the commits.
[25 Nov 2008 10:27] Kristofer Pettersson
In 5.0 version of mysql_delete there is a rollback initiated which prevent this bug for occurring there:

sql_delete.cc, line 344:
if (transactional_table)
{
  if (ha_autocommit_or_rollback(thd,error >= 0))
    error=1;
}

It originates from a patch since 2002.
[25 Nov 2008 12:47] Kristofer Pettersson
...rollback was removed as part of refactoring job here: http://lists.mysql.com/commits/41868
[11 Dec 2008 11:00] 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:

  http://lists.mysql.com/commits/61331

2733 Davi Arnaut	2008-12-11
      Bug#37016: TRUNCATE TABLE removes some rows but not all
      
      The TRUNCATE TABLE transaction wasn't being properly rolled back
      if a error occurred during row by row deletion. The error can be
      caused by a foreign key restriction imposed by InnoDB SE and the
      server would erroneously issue a implicit commit.
      
      The solution is to rollback the transaction if a truncation via
      row by row deletion fails, otherwise commit. If the truncation
      is emulated by a table recreation, always issue a implicit commit.
[13 Dec 2008 10:58] 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:

  http://lists.mysql.com/commits/61577

2741 Davi Arnaut	2008-12-13
      Bug#37016: TRUNCATE TABLE removes some rows but not all
      
      The special TRUNCATE TABLE (DDL) transaction wasn't being properly
      rolled back if a error occurred during row by row deletion. The
      error can be caused by a foreign key restriction imposed by InnoDB
      SE and woudl cause the server to erroneously issue a implicit
      commit.
      
      The solution is to rollback the transaction if a truncation via row
      by row deletion fails, otherwise commit. If the truncation is
      emulated by a table recreation, always issue a implicit commit.
      
      This makes TRUNCATE TABLE a special case of DDL because a implicit
      commit is NOT issued regardless of the outcome of the operation --
      a implicit rollback is issued if row by row deletion fails.
[8 Jan 2009 22:10] 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:

  http://lists.mysql.com/commits/62753

2730 Davi Arnaut	2009-01-08
      Bug#37016: TRUNCATE TABLE removes some rows but not all
      
      The special TRUNCATE TABLE (DDL) transaction wasn't being properly
      rolled back if a error occurred during row by row deletion. The
      error can be caused by a foreign key restriction imposed by InnoDB
      SE and would cause the server to erroneously issue a implicit
      commit.
      
      The solution is to rollback the transaction if a truncation via row
      by row deletion fails, otherwise commit. If the truncation is
      emulated by a table recreation, always issue a implicit commit.
      All effects of a TRUNCATE TABLE operation are rolled back if a
      row by row deletion fails.
[9 Jan 2009 10:21] 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:

  http://lists.mysql.com/commits/62785

2735 Davi Arnaut	2009-01-09
      Bug#37016: TRUNCATE TABLE removes some rows but not all
      
      The special TRUNCATE TABLE (DDL) transaction wasn't being properly
      rolled back if a error occurred during row by row deletion. The
      error can be caused by a foreign key restriction imposed by InnoDB
      SE and would cause the server to erroneously issue a implicit
      commit.
      
      The solution is to rollback the transaction if a truncation via row
      by row deletion fails, otherwise commit. All effects of a TRUNCATE 
      ABLE operation are rolled back if a row by row deletion fails.
[9 Jan 2009 10:23] Davi Arnaut
Queued to 5.1-bugteam
[9 Jan 2009 10:46] 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:

  http://lists.mysql.com/commits/62793

2954 Davi Arnaut	2009-01-09 [merge]
      Merge of Bug#37016 to 6.0-bugteam
[9 Jan 2009 11:01] 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:

  http://lists.mysql.com/commits/62794

2945 Davi Arnaut	2009-01-09 [merge]
      Merge of Bug#37016 to 6.0-bugteam
[13 Jan 2009 12:23] Mattias Jonsson
For reference:
Affects the falcon engine, it that does not currently support transactional ::delete_all_rows(), see bug#42079.
[14 Jan 2009 9:33] 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:

  http://lists.mysql.com/commits/63197

2748 He Zhenxing	2009-01-14 [merge]
      Auto merge
[15 Jan 2009 6:39] Bugs System
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)
[19 Jan 2009 11:29] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:12] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:58] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:mats@sun.com-20090109123108-m97xwc0c1048bxik) (merge vers: 6.0.10-alpha) (pib:6)
[29 Jan 2009 20:17] Paul Dubois
Noted in 5.1.31, 6.0.10 changelogs.

For an InnoDB table with a FOREIGN KEY constraint, TRUNCATE TABLE may
be performed using row by row deletion. If an error occurred during
this deletion, the table would be only partially emptied. Now if an
error occurs, the truncation operation is rolled back and the table
is left unchanged.