bzr commit into mysql-6.0 branch (timothy.smith:2794) Bug#36941 Bug#36942 Bug#37531 Bug#38185

[Timothy Smith]

#At file:///home/tsmith/m/bzr/19aug/60/

 2794 Timothy Smith	2008-08-21 [merge]
      Merge up from 5.1-bugteam.  Among other things, this applies some changes from
      the innodb-5.1-ss2545 snapshot into 6.0, fixing Bug#37531, Bug#36941,
      Bug#36942, and Bug#38185.
modified:
  mysql-test/r/innodb-autoinc-optimize.result
  mysql-test/r/innodb-autoinc.result
  mysql-test/r/join.result
  mysql-test/t/innodb-autoinc.test
  mysql-test/t/join.test
  sql/sql_show.cc
  storage/innobase/ha/ha0ha.c
  storage/innobase/handler/ha_innodb.cc
  storage/innobase/lock/lock0lock.c

=== modified file 'mysql-test/r/innodb-autoinc-optimize.result'
--- a/mysql-test/r/innodb-autoinc-optimize.result	2008-08-20 10:29:58 +0000
+++ b/mysql-test/r/innodb-autoinc-optimize.result	2008-08-21 23:38:19 +0000
@@ -3,4 +3,5 @@ create table t1(a int not null auto_incr
 insert into t1 set a = -1;
 optimize table t1;
 Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
 test.t1	optimize	status	OK

=== modified file 'mysql-test/r/innodb-autoinc.result'
--- a/mysql-test/r/innodb-autoinc.result	2008-06-12 00:08:07 +0000
+++ b/mysql-test/r/innodb-autoinc.result	2008-08-21 23:38:19 +0000
@@ -87,3 +87,85 @@ SELECT * FROM t1;
 c1	c2
 18446744073709551615	NULL
 DROP TABLE t1;
+CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+c1
+1
+2
+3
+4
+5
+6
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+TRUNCATE TABLE t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+c1
+1
+2
+3
+4
+5
+6
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+c1
+1
+2
+3
+4
+5
+6
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+c1
+1
+2
+3
+7
+8
+9
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
+DROP TABLE t1;

=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2007-12-13 12:55:04 +0000
+++ b/mysql-test/r/join.result	2008-08-21 23:38:19 +0000
@@ -758,7 +758,7 @@ INDEX_SCHEMA	mysql
 INDEX_NAME	PRIMARY
 SEQ_IN_INDEX	1
 COLLATION	A
-CARDINALITY	NULL
+CARDINALITY	#
 SUB_PART	NULL
 PACKED	NULL
 NULLABLE	
@@ -793,7 +793,7 @@ INDEX_SCHEMA	mysql
 INDEX_NAME	PRIMARY
 SEQ_IN_INDEX	2
 COLLATION	A
-CARDINALITY	3
+CARDINALITY	#
 SUB_PART	NULL
 PACKED	NULL
 NULLABLE	

=== modified file 'mysql-test/t/innodb-autoinc.test'
--- a/mysql-test/t/innodb-autoinc.test	2008-06-26 15:52:25 +0000
+++ b/mysql-test/t/innodb-autoinc.test	2008-08-21 23:38:19 +0000
@@ -105,3 +105,37 @@ INSERT INTO t1 VALUES (18446744073709551
 INSERT INTO t1 (c2) VALUES ('innodb');
 SELECT * FROM t1;
 DROP TABLE t1;
+
+#
+# Bug 37531
+# After truncate, auto_increment behaves incorrectly for InnoDB
+#
+CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+SHOW CREATE TABLE t1;
+TRUNCATE TABLE t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+#
+# Deleting all records should not reset the AUTOINC counter.
+#
+CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+SHOW CREATE TABLE t1;
+DELETE FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SELECT c1 FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+

=== modified file 'mysql-test/t/join.test'
--- a/mysql-test/t/join.test	2007-11-16 21:34:02 +0000
+++ b/mysql-test/t/join.test	2008-08-21 23:38:19 +0000
@@ -547,7 +547,7 @@ select * from v1a join v1b on t1.b = t2.
 # Bug #17523 natural join and information_schema
 #
 # We mask out the Privileges column because it differs with embedded server
---replace_column 32 #
+--replace_column 32 # 10 #
 query_vertical 
 select * from information_schema.statistics join information_schema.columns
               using(table_name,column_name) where table_name='user';

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2008-08-18 16:17:48 +0000
+++ b/sql/sql_show.cc	2008-08-21 23:38:19 +0000
@@ -3643,8 +3643,7 @@ static int get_schema_tables_record(THD 
 
     if(file)
     {
-      file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_AUTO |
-                 HA_STATUS_NO_LOCK);
+      file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_AUTO);
       enum row_type row_type = file->get_row_type();
       switch (row_type) {
       case ROW_TYPE_NOT_USED:

=== modified file 'storage/innobase/ha/ha0ha.c'
--- a/storage/innobase/ha/ha0ha.c	2007-03-22 21:59:35 +0000
+++ b/storage/innobase/ha/ha0ha.c	2008-08-20 22:18:33 +0000
@@ -331,11 +331,20 @@ ha_print_info(
 	FILE*		file,	/* in: file where to print */
 	hash_table_t*	table)	/* in: hash table */
 {
+#ifdef UNIV_DEBUG
+/* Some of the code here is disabled for performance reasons in production
+builds, see http://bugs.mysql.com/36941 */
+#define PRINT_USED_CELLS
+#endif /* UNIV_DEBUG */
+
+#ifdef PRINT_USED_CELLS
 	hash_cell_t*	cell;
 	ulint		cells	= 0;
-	ulint		n_bufs;
 	ulint		i;
+#endif /* PRINT_USED_CELLS */
+	ulint		n_bufs;
 
+#ifdef PRINT_USED_CELLS
 	for (i = 0; i < hash_get_n_cells(table); i++) {
 
 		cell = hash_get_nth_cell(table, i);
@@ -345,10 +354,14 @@ ha_print_info(
 			cells++;
 		}
 	}
+#endif /* PRINT_USED_CELLS */
+
+	fprintf(file, "Hash table size %lu",
+		(ulong) hash_get_n_cells(table));
 
-	fprintf(file,
-		"Hash table size %lu, used cells %lu",
-		(ulong) hash_get_n_cells(table), (ulong) cells);
+#ifdef PRINT_USED_CELLS
+	fprintf(file, ", used cells %lu", (ulong) cells);
+#endif /* PRINT_USED_CELLS */
 
 	if (table->heaps == NULL && table->heap != NULL) {
 

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2008-08-19 14:14:18 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2008-08-21 23:38:19 +0000
@@ -5861,6 +5861,14 @@ ha_innobase::info(
 		if (thd_sql_command(user_thd) == SQLCOM_TRUNCATE) {
 
 			n_rows = 0;
+
+			/* We need to reset the prebuilt value too, otherwise
+			checks for values greater than the last value written
+			to the table will fail and the autoinc counter will
+			not be updated. This will force write_row() into
+			attempting an update of the table's AUTOINC counter. */
+
+			prebuilt->last_value = 0;
 		}
 
 		stats.records = (ha_rows)n_rows;
@@ -5871,9 +5879,21 @@ ha_innobase::info(
 		stats.index_file_length = ((ulonglong)
 				ib_table->stat_sum_of_other_index_sizes)
 					* UNIV_PAGE_SIZE;
-		stats.delete_length =
-			fsp_get_available_space_in_free_extents(
-				ib_table->space) * 1024;
+
+		/* Since fsp_get_available_space_in_free_extents() is
+		acquiring latches inside InnoDB, we do not call it if we
+		are asked by MySQL to avoid locking. Another reason to
+		avoid the call is that it uses quite a lot of CPU.
+		See Bug#38185.
+		We do not update delete_length if no locking is requested
+		so the "old" value can remain. delete_length is initialized
+		to 0 in the ha_statistics' constructor. */
+		if (!(flag & HA_STATUS_NO_LOCK)) {
+			stats.delete_length =
+				fsp_get_available_space_in_free_extents(
+					ib_table->space) * 1024;
+		}
+
 		stats.check_time = 0;
 
 		if (stats.records == 0) {

=== modified file 'storage/innobase/lock/lock0lock.c'
--- a/storage/innobase/lock/lock0lock.c	2007-08-25 09:55:38 +0000
+++ b/storage/innobase/lock/lock0lock.c	2008-08-20 22:18:33 +0000
@@ -4138,6 +4138,15 @@ lock_rec_print(
 }
 
 #ifndef UNIV_HOTBACKUP
+
+#ifdef UNIV_DEBUG
+/* Print the number of lock structs from lock_print_info_summary() only
+in non-production builds for performance reasons, see
+http://bugs.mysql.com/36942 */
+#define PRINT_NUM_OF_LOCK_STRUCTS
+#endif /* UNIV_DEBUG */
+
+#ifdef PRINT_NUM_OF_LOCK_STRUCTS
 /*************************************************************************
 Calculates the number of record lock structs in the record lock hash table. */
 static
@@ -4164,6 +4173,7 @@ lock_get_n_rec_locks(void)
 
 	return(n_locks);
 }
+#endif /* PRINT_NUM_OF_LOCK_STRUCTS */
 
 /*************************************************************************
 Prints info of locks for all transactions. */
@@ -4207,9 +4217,11 @@ lock_print_info_summary(
 		"History list length %lu\n",
 		(ulong) trx_sys->rseg_history_len);
 
+#ifdef PRINT_NUM_OF_LOCK_STRUCTS
 	fprintf(file,
 		"Total number of lock structs in row lock hash table %lu\n",
 		(ulong) lock_get_n_rec_locks());
+#endif /* PRINT_NUM_OF_LOCK_STRUCTS */
 }
 
 /*************************************************************************