bzr commit into mysql-5.1-bugteam branch (davi:2741) Bug#37016

[Davi Arnaut]

# At a local mysql-5.1-bugteam repository of davi

 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.
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-12-12 12:52:20 +0000
+++ b/mysql-test/include/commit.inc	2008-12-13 10:58: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	2008-12-13 10:58: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	2008-12-08 10:23:33 +0000
+++ b/mysql-test/r/innodb_mysql.result	2008-12-13 10:58:32 +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-13 10:58:32 +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-13 10:58: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
 
@@ -978,7 +998,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 +1034,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 +1072,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);
 }