bzr commit into mysql-5.1 branch (kristofer.pettersson:2704) Bug#37016

[Kristofer Pettersson]

#At file:///home/thek/Development/cpp/mysqlbzr/mysql-5.1-bug37016/

 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.
modified:
  mysql-test/r/innodb_mysql.result
  mysql-test/t/innodb_mysql.test
  sql/sql_delete.cc

per-file messages:
  mysql-test/r/innodb_mysql.result
    * Added test case
  mysql-test/t/innodb_mysql.test
    * Added test case
  sql/sql_delete.cc
    * Changed the pattern of commits when a TRUNCATE TABLE results in row-by-row deletion. Instead of doing ha_commit_trans,ha_commit_trans,ha_commit_trans, ha_commit_trans we know settle with committing statements transactions once and normal transactions once.
    * Also moved the hint that it is ok to enable transactions to the end of the truncate_table function because formally speaking it is wrong to allow an "enable of transactions" if they are already on. By moving the hint to the end of the code, we assert that there are no unfinished transactions which can be interrupted by re-enabling the transaction support.
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2008-11-03 17:46:47 +0000
+++ b/mysql-test/r/innodb_mysql.result	2008-11-19 10:24:54 +0000
@@ -1677,3 +1677,24 @@ select @@session.sql_log_bin, @@session.
 CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
 INSERT INTO t1 VALUES(1);
 DROP TABLE t1;
+#
+# Bug#37016 TRUNCATE TABLE removes some rows but not all
+#
+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);
+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
+DROP TABLE t2;
+DROP TABLE t1;

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2008-11-03 17:46:47 +0000
+++ b/mysql-test/t/innodb_mysql.test	2008-11-19 10:24:54 +0000
@@ -6,6 +6,7 @@
 #
 
 -- source include/have_innodb.inc
+
 let $engine_type= InnoDB;
 let $other_engine_type= MEMORY;
 # InnoDB does support FOREIGN KEYFOREIGN KEYs
@@ -43,3 +44,21 @@ CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
 INSERT INTO t1 VALUES(1);
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#37016 TRUNCATE TABLE removes some rows but not all
+--echo #
+--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);
+INSERT INTO t1 VALUES (2);
+SELECT * FROM t1;
+INSERT INTO t2 VALUES (10, 2);
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t2;
+DROP TABLE t1;
+

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2008-11-03 13:08:42 +0000
+++ b/sql/sql_delete.cc	2008-11-19 10:24:54 +0000
@@ -1058,21 +1058,81 @@ trunc_by_del:
   /* Probably InnoDB table */
   ulonglong save_options= thd->options;
   table_list->lock_type= TL_WRITE;
+
+  /*
+    Try off auto commit and try to delete all rows at once.
+  */
   thd->options&= ~(OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT);
-  ha_enable_transaction(thd, FALSE);
+
+  /*
+    Send hint to SE that transactions may be turned off if needed.
+  */
+  thd->transaction.on= FALSE;
+
+  /*
+    Initialize the parser states.
+  */
   mysql_init_select(thd->lex);
+
+  /*
+    Don't involve binlogging. This is an atomic DDL in disguise.
+  */
   bool save_binlog_row_based= thd->current_stmt_binlog_row_based;
   thd->clear_current_stmt_binlog_row_based();
+
+  /*
+    Execute the delete command.
+  */
   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.*.
+    Set up binlogging so we don't log the commit or rollback and end up
+    confusing the slave.
+  */
+  thd->options&= ~(OPTION_BEGIN | OPTION_KEEP_LOG);
+
+
+  if (!error)
+  {
+    /*
+      Commit the delete command in the context of a "statement transaction"
+      which also works in auto-commit mode.
+    */
+    if (!ha_commit_trans(thd, FALSE))
+    {
+      error= 1;
+    }
+    else
+    {
+      /*
+        MySQL should always commit a normal transaction after a DDL statement,
+        just as it commits a statement transactions at the end of a statement.
+      */
+      if (!ha_commit_trans(thd, TRUE))
+        error= 1;
+    }
+  }
+
+  if (error)
+  {
+    /*
+      Something went wrong; rollback the transaction.
+    */
+    (void) ha_rollback_trans(thd, 0);
+    if (thd->transaction_rollback_request && !thd->in_sub_stmt)
+      (void) ha_rollback(thd);
+  }
+
+
+  /*
+    Restore previous options and binlog state as we leave the disguise.
   */
-  error= ha_autocommit_or_rollback(thd, error);
-  ha_commit(thd);
   thd->options= save_options;
   thd->current_stmt_binlog_row_based= save_binlog_row_based;
+  /*
+    Send hint to SE that transactions may be enabled.
+  */
+  thd->transaction.on= TRUE;
   DBUG_RETURN(error);
 }