bzr push into mysql-5.1-bugteam branch (davi:2734 to 2735) Bug#37016

[Davi Arnaut]

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

 2734 Horst Hunger	2009-01-09 [merge]
      due to merge.
modified:
  mysql-test/r/innodb_mysql.result
  mysql-test/r/temp_table.result
  mysql-test/t/innodb_mysql.test
  mysql-test/t/temp_table.test
  sql/sql_base.cc

=== modified file 'mysql-test/include/commit.inc'
--- a/mysql-test/include/commit.inc	2008-12-12 12:52:20 +0000
+++ b/mysql-test/include/commit.inc	2009-01-09 10:20:32 +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);
@@ -733,7 +733,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-12-12 12:52:20 +0000
+++ b/mysql-test/r/commit_1innodb.result	2009-01-09 10:20:32 +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;
@@ -853,7 +853,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	2009-01-07 12:11:37 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-01-09 10:20:32 +0000
@@ -1788,4 +1788,62 @@ DEALLOCATE PREPARE stmt1;
 DEALLOCATE PREPARE stmt3;
 DROP TABLE t1,t3,t2;
 DROP FUNCTION f1;
+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	2009-01-07 12:11:37 +0000
+++ b/mysql-test/t/innodb_mysql.test	2009-01-09 10:20:32 +0000
@@ -132,4 +132,56 @@ DEALLOCATE PREPARE stmt3;
 DROP TABLE t1,t3,t2;
 DROP FUNCTION f1;
 
+#
+# 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	2009-01-09 10:20:32 +0000
@@ -951,6 +951,26 @@ bool multi_delete::send_eof()
 ****************************************************************************/
 
 /*
+  Row-by-row truncation if the engine does not support table recreation.
+  Probably a InnoDB table.
+*/
+
+static bool mysql_truncate_by_delete(THD *thd, TABLE_LIST *table_list)
+{
+  bool error, save_binlog_row_based= thd->current_stmt_binlog_row_based;
+  DBUG_ENTER("mysql_truncate_by_delete");
+  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
 
@@ -1055,24 +1075,6 @@ end:
   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;
+  error= mysql_truncate_by_delete(thd, table_list);
   DBUG_RETURN(error);
 }