bk commit into 5.0 tree (osku:1.1981) BUG#11946

[Osku Salerma]

Below is the list of changes that have just been committed into a local
5.0 repository of osku. When osku does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1981 05/08/17 11:00:20 osku@127.(none) +6 -0
  Fix bug #11946, truncate not always resetting the auto-increment counter
  in InnoDB tables.

  sql/sql_delete.cc
    1.157 05/08/17 11:00:16 osku@127.(none) +22 -0
    Call handler->reset_auto_increment when needed.

  sql/handler.h
    1.148 05/08/17 11:00:16 osku@127.(none) +9 -0
    Add reset_auto_increment.

  sql/ha_innodb.h
    1.101 05/08/17 11:00:16 osku@127.(none) +2 -0
    Add reset_auto_increment.

  sql/ha_innodb.cc
    1.245 05/08/17 11:00:16 osku@127.(none) +22 -0
    Add reset_auto_increment.

  mysql-test/t/innodb.test
    1.100 05/08/17 11:00:16 osku@127.(none) +28 -0
    New tests.

  mysql-test/r/innodb.result
    1.127 05/08/17 11:00:16 osku@127.(none) +23 -0
    New tests.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	osku
# Host:	127.(none)
# Root:	/home/osku/mysql-5.0

--- 1.147/sql/handler.h	2005-08-12 22:04:16 +03:00
+++ 1.148/sql/handler.h	2005-08-17 11:00:16 +03:00
@@ -650,6 +650,15 @@
   { return (my_errno=HA_ERR_WRONG_COMMAND); }
   virtual ulonglong get_auto_increment();
   virtual void restore_auto_increment();
+
+  /* This is called after TRUNCATE is emulated by doing a 'DELETE FROM t',
+     in which case we need a separate operation for resetting the table's
+     auto-increment counter. HA_ERR_WRONG_COMMAND is returned by storage
+     engines that have no need for this, i.e. those that can always do a
+     fast TRUNCATE. */
+  virtual int reset_auto_increment()
+  { return HA_ERR_WRONG_COMMAND; }
+
   virtual void update_create_info(HA_CREATE_INFO *create_info) {}
 
   /* admin commands - called from mysql_admin_table */

--- 1.156/sql/sql_delete.cc	2005-08-15 18:31:01 +03:00
+++ 1.157/sql/sql_delete.cc	2005-08-17 11:00:16 +03:00
@@ -103,6 +103,13 @@
     free_underlaid_joins(thd, select_lex);
     thd->row_count_func= 0;
     send_ok(thd,0L);
+
+    /*
+      We don't need to call reset_auto_increment in this case, because
+      mysql_truncate always gives a NULL conds argument, hence we never
+      get here.
+    */
+
     DBUG_RETURN(0);				// Nothing to delete
   }
 
@@ -225,6 +232,21 @@
   free_io_cache(table);				// Will not do any harm
   if (options & OPTION_QUICK)
     (void) table->file->extra(HA_EXTRA_NORMAL);
+
+  if ((error < 0) && (thd->lex->sql_command == SQLCOM_TRUNCATE))
+  {
+    /*
+      We're really doing a truncate and need to reset the table's
+      auto-increment counter.
+    */
+    int error2 = table->file->reset_auto_increment();
+
+    if (error2 && (error2 != HA_ERR_WRONG_COMMAND))
+    {
+      table->file->print_error(error2, MYF(0));
+      error = 1;
+    }
+  }
 
 cleanup:
   /*

--- 1.126/mysql-test/r/innodb.result	2005-08-12 18:04:47 +03:00
+++ 1.127/mysql-test/r/innodb.result	2005-08-17 11:00:16 +03:00
@@ -2483,3 +2483,26 @@
 select * from t1;
 f1	f2
 drop table t1,t2;
+CREATE TABLE t1 (
+id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INTEGER NOT NULL,
+FOREIGN KEY (id) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+id
+1
+TRUNCATE t1;
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+id
+1
+DELETE FROM t1;
+TRUNCATE t1;
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+id
+1
+DROP TABLE t2, t1;

--- 1.99/mysql-test/t/innodb.test	2005-08-12 18:04:48 +03:00
+++ 1.100/mysql-test/t/innodb.test	2005-08-17 11:00:16 +03:00
@@ -1404,3 +1404,31 @@
 delete t1 from t1,t2 where f1=f3 and f4='cc';
 select * from t1;
 drop table t1,t2;
+
+#
+# Test that the slow TRUNCATE implementation resets autoincrement columns
+# (bug #11946)
+#
+
+CREATE TABLE t1 (
+id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+id INTEGER NOT NULL,
+FOREIGN KEY (id) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+TRUNCATE t1;
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+
+# continued from above; test that doing a slow TRUNCATE on a table with 0
+# rows resets autoincrement columns
+DELETE FROM t1;
+TRUNCATE t1;
+INSERT INTO t1 (id) VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t2, t1;

--- 1.244/sql/ha_innodb.cc	2005-08-12 23:56:11 +03:00
+++ 1.245/sql/ha_innodb.cc	2005-08-17 11:00:16 +03:00
@@ -6782,6 +6782,28 @@
 	return((ulonglong) nr);
 }
 
+/* See comment in handler.h */
+int
+ha_innobase::reset_auto_increment()
+{
+	DBUG_ENTER("ha_innobase::reset_auto_increment");
+
+	row_prebuilt_t* prebuilt = (row_prebuilt_t*) innobase_prebuilt;
+  	int     	error;
+
+	error = row_lock_table_autoinc_for_mysql(prebuilt);
+
+	if (error != DB_SUCCESS) {
+		error = convert_error_code_to_mysql(error, user_thd);
+
+		DBUG_RETURN(error);
+	}	
+
+	dict_table_autoinc_initialize(prebuilt->table, 0);
+
+	DBUG_RETURN(0);
+}
+
 /***********************************************************************
 Compares two 'refs'. A 'ref' is the (internal) primary key value of the row.
 If there is no explicitly declared non-null unique key or a primary key, then

--- 1.100/sql/ha_innodb.h	2005-08-11 19:01:36 +03:00
+++ 1.101/sql/ha_innodb.h	2005-08-17 11:00:16 +03:00
@@ -173,6 +173,8 @@
 			     		enum thr_lock_type lock_type);
 	void init_table_handle_for_HANDLER(); 
 	ulonglong get_auto_increment();
+	int reset_auto_increment();
+	
         uint8 table_cache_type() { return HA_CACHE_TBL_ASKTRANSACT; }
         /*
           ask handler about permission to cache table during query registration