| 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: | |
| 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 | ||
[27 May 2008 19:25]
Guilhem Bichot
[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.
