# At a local mysql-5.1-bugteam repository of davi
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.
modified:
mysql-test/include/commit.inc
mysql-test/r/commit_1innodb.result
mysql-test/r/innodb_mysql.result
mysql-test/t/innodb_mysql.test
sql/sql_delete.cc
per-file messages:
mysql-test/include/commit.inc
Truncate always starts a transaction and commits at the end.
mysql-test/r/commit_1innodb.result
Update test case results.
mysql-test/r/innodb_mysql.result
Add test case result for Bug#37016
mysql-test/t/innodb_mysql.test
Add test case for Bug#37016
sql/sql_delete.cc
Move truncation using row by row deletion to its own function.
If row by row deletion fails, rollback the transaction.
Truncate is considered a DDL, hence it should have it's own special
transaction. Regardless of the outcome of the truncation operation,
always commit the transaction. If the truncation was emulated via
a row by row deletion, the transaction is already finished.
=== modified file 'mysql-test/include/commit.inc'
--- a/mysql-test/include/commit.inc 2008-03-31 09:29:03 +0000
+++ b/mysql-test/include/commit.inc 2008-12-11 10:59:33 +0000
@@ -617,10 +617,10 @@ call p_verify_status_increment(0, 0, 0,
--echo
--echo # No test because of Bug#8729 "rename table fails on temporary table"
---echo # 24. DDL: TRUNCATE TEMPORARY TABLE, does not start a transaction
+--echo # 24. DDL: TRUNCATE TEMPORARY TABLE
--echo
truncate table t2;
-call p_verify_status_increment(2, 0, 2, 0);
+call p_verify_status_increment(4, 0, 4, 0);
commit;
--echo # There is nothing left to commit
call p_verify_status_increment(0, 0, 0, 0);
@@ -730,7 +730,7 @@ call p_verify_status_increment(1, 0, 1,
rename table t4 to t3;
call p_verify_status_increment(1, 0, 1, 0);
truncate table t3;
-call p_verify_status_increment(2, 2, 2, 2);
+call p_verify_status_increment(4, 4, 2, 2);
create view v1 as select * from t2;
call p_verify_status_increment(1, 0, 1, 0);
check table t1;
=== modified file 'mysql-test/r/commit_1innodb.result'
--- a/mysql-test/r/commit_1innodb.result 2008-03-31 09:29:03 +0000
+++ b/mysql-test/r/commit_1innodb.result 2008-12-11 10:59:33 +0000
@@ -683,10 +683,10 @@ SUCCESS
# 23. DDL: RENAME TEMPORARY TABLE, does not start a transaction
# No test because of Bug#8729 "rename table fails on temporary table"
-# 24. DDL: TRUNCATE TEMPORARY TABLE, does not start a transaction
+# 24. DDL: TRUNCATE TEMPORARY TABLE
truncate table t2;
-call p_verify_status_increment(2, 0, 2, 0);
+call p_verify_status_increment(4, 0, 4, 0);
SUCCESS
commit;
@@ -850,7 +850,7 @@ call p_verify_status_increment(1, 0, 1,
SUCCESS
truncate table t3;
-call p_verify_status_increment(2, 2, 2, 2);
+call p_verify_status_increment(4, 4, 2, 2);
SUCCESS
create view v1 as select * from t2;
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2008-12-08 10:23:33 +0000
+++ b/mysql-test/r/innodb_mysql.result 2008-12-11 10:59:33 +0000
@@ -1767,4 +1767,62 @@ ref NULL
rows 6
Extra Using where; Using index
DROP TABLE foo, bar, foo2;
+DROP TABLE IF EXISTS t1,t2;
+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),(2);
+INSERT INTO t2 VALUES (3,2);
+SET AUTOCOMMIT = 0;
+START TRANSACTION;
+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
+COMMIT;
+SELECT * FROM t1;
+id
+1
+2
+START TRANSACTION;
+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
+SET AUTOCOMMIT = 1;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+1
+2
+COMMIT;
+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
+DELETE FROM t2 WHERE id = 3;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+1
+2
+TRUNCATE TABLE t1;
+ROLLBACK;
+SELECT * FROM t1;
+id
+TRUNCATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test 2008-12-08 10:23:33 +0000
+++ b/mysql-test/t/innodb_mysql.test 2008-12-11 10:59:33 +0000
@@ -91,4 +91,56 @@ INSERT INTO foo2 SELECT * FROM foo;
DROP TABLE foo, bar, foo2;
+#
+# Bug#37016: TRUNCATE TABLE removes some rows but not all
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+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),(2);
+INSERT INTO t2 VALUES (3,2);
+
+SET AUTOCOMMIT = 0;
+
+START TRANSACTION;
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+COMMIT;
+SELECT * FROM t1;
+
+START TRANSACTION;
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+ROLLBACK;
+SELECT * FROM t1;
+
+SET AUTOCOMMIT = 1;
+
+START TRANSACTION;
+SELECT * FROM t1;
+COMMIT;
+
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+DELETE FROM t2 WHERE id = 3;
+
+START TRANSACTION;
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+ROLLBACK;
+SELECT * FROM t1;
+TRUNCATE TABLE t2;
+
+DROP TABLE t2;
+DROP TABLE t1;
+
--echo End of 5.1 tests
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2008-11-03 13:08:42 +0000
+++ b/sql/sql_delete.cc 2008-12-11 10:59:33 +0000
@@ -951,6 +951,28 @@ bool multi_delete::send_eof()
****************************************************************************/
/*
+ Row-by-row truncation if the engine does not support table recreation.
+ Probably a InnoDB table.
+*/
+
+bool mysql_truncate_by_delete(THD *thd, TABLE_LIST *table_list)
+{
+ bool error;
+ DBUG_ENTER("mysql_truncate_by_delete");
+ bool save_binlog_row_based= thd->current_stmt_binlog_row_based;
+
+ table_list->lock_type= TL_WRITE;
+ mysql_init_select(thd->lex);
+ thd->clear_current_stmt_binlog_row_based();
+ error= mysql_delete(thd, table_list, NULL, NULL, HA_POS_ERROR, LL(0), TRUE);
+ ha_autocommit_or_rollback(thd, error);
+ end_trans(thd, error ? ROLLBACK : COMMIT);
+ thd->current_stmt_binlog_row_based= save_binlog_row_based;
+ DBUG_RETURN(error);
+}
+
+
+/*
Optimize delete of all rows by doing a full generate of the table
This will work even if the .ISM and .ISD tables are destroyed
@@ -978,7 +1000,7 @@ bool mysql_truncate(THD *thd, TABLE_LIST
handlerton *table_type= table->s->db_type();
TABLE_SHARE *share= table->s;
if (!ha_check_storage_engine_flag(table_type, HTON_CAN_RECREATE))
- goto trunc_by_del;
+ DBUG_RETURN(mysql_truncate_by_delete(thd, table_list));
table->file->info(HA_STATUS_AUTO | HA_STATUS_NO_LOCK);
@@ -1014,7 +1036,7 @@ bool mysql_truncate(THD *thd, TABLE_LIST
}
if (!ha_check_storage_engine_flag(ha_resolve_by_legacy_type(thd, table_type),
HTON_CAN_RECREATE))
- goto trunc_by_del;
+ DBUG_RETURN(mysql_truncate_by_delete(thd, table_list));
if (lock_and_wait_for_table_name(thd, table_list))
DBUG_RETURN(TRUE);
@@ -1052,27 +1074,9 @@ end:
unlock_table_name(thd, table_list);
VOID(pthread_mutex_unlock(&LOCK_open));
}
- DBUG_RETURN(error);
-trunc_by_del:
- /* Probably InnoDB table */
- ulonglong save_options= thd->options;
- table_list->lock_type= TL_WRITE;
- thd->options&= ~(OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT);
- 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);
- /*
- 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);
- thd->options= save_options;
- thd->current_stmt_binlog_row_based= save_binlog_row_based;
+ ha_autocommit_or_rollback(thd, 0);
+ end_active_trans(thd);
+
DBUG_RETURN(error);
}
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (davi:2733) Bug#37016 | Davi Arnaut | 11 Dec |
