From: Date: November 17 2008 12:24pm
Subject: bzr commit into mysql-5.1 branch (kristofer.pettersson:2704) Bug#37016
List-Archive: http://lists.mysql.com/commits/58942
X-Bug: 37016
Message-Id: <0KAH00AOX6D9H110@fe-emea-10.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

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

 2704 Kristofer Pettersson	2008-11-17
      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 avoiding the implicit commit so that a
      rollback can be performed instead.
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
    * Don't re-enable transactions if mysql_delete raised an error. If we do
    we will clear the transaction list and won't be able to perform a rollback.
=== 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-17 11:33:48 +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-17 11:33:48 +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-17 11:33:48 +0000
@@ -1065,7 +1065,10 @@ trunc_by_del:
   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);
+
+  if (!error)
+    ha_enable_transaction(thd, TRUE);
+ 
   /*
     Safety, in case the engine ignored ha_enable_transaction(FALSE)
     above. Also clears thd->transaction.*.