From: Date: December 11 2008 11:59am Subject: bzr commit into mysql-5.1-bugteam branch (davi:2733) Bug#37016 List-Archive: http://lists.mysql.com/commits/61331 X-Bug: 37016 Message-Id: <20081211105945.A0572EC1C1@skynet.ctb.virtua.com.br> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit # 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); }