List:Commits« Previous MessageNext Message »
From:Vladimir Shebordaev Date:January 3 2007 11:45am
Subject:bk commit into 5.0 tree (ted:1.2358) BUG#4291
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of ted. When ted 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@stripped, 2007-01-03 14:45:26+03:00, ted@stripped +7 -0
  BUG #4291: max_heap_table_size affects creation of disk-based temporary table
  
  fix: the new system variable memory_tmp_table_size is introduced; 
       it stands now for the exact purpose the Manual says 
       tmp_table_size used to do. 
  
  tmp_table_size retains to (give a hint about a) 
  limit of the on-disk temporary table size. The limit imposed upon 
  the disk-based temporary tables is still quite relative due to MyISAM
  current implementation restrictions.

  mysql-test/r/implicit_tmp_table.result@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +65 -0
    The results are just recorded out of t/implicit_tmp_table.test run

  mysql-test/r/implicit_tmp_table.result@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +0 -0

  mysql-test/t/implicit_tmp_table.test@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +77 -0
    The test cases to ensure the temporary tables are created correctly

  mysql-test/t/implicit_tmp_table.test@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +0 -0

  sql/ha_heap.cc@stripped, 2007-01-03 14:45:22+03:00, ted@stripped +3 -3
    Max size of a memory temporary table is now set to memory_tmp_table_size

  sql/mysqld.cc@stripped, 2007-01-03 14:45:22+03:00, ted@stripped +8 -3
    New system variable memory_tmp_table_size is introduced. 

  sql/set_var.cc@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +4 -0
    New system variable memory_tmp_table_size is introduced. 

  sql/sql_class.h@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +1 -0
    New system variable memory_tmp_table_size is introduced. 

  sql/sql_select.cc@stripped, 2007-01-03 14:45:23+03:00, ted@stripped +6 -9
    max_rows for the temporary table is now set to the value 
    calculated out of memory_tmp_table_size

# 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:	ted
# Host:	ted.mysql.internal
# Root:	/usr/local/src/mysql/bug4291/50

--- 1.82/sql/ha_heap.cc	2007-01-03 14:45:33 +03:00
+++ 1.83/sql/ha_heap.cc	2007-01-03 14:45:33 +03:00
@@ -629,8 +629,6 @@
     }
   }
   mem_per_row+= MY_ALIGN(share->reclength + 1, sizeof(char*));
-  max_rows = (ha_rows) (table->in_use->variables.max_heap_table_size /
-			(ulonglong) mem_per_row);
   if (table_arg->found_next_number_field)
   {
     keydef[share->next_number_index].flag|= HA_AUTO_KEY;
@@ -641,7 +639,9 @@
   hp_create_info.auto_key_type= auto_key_type;
   hp_create_info.auto_increment= (create_info->auto_increment_value ?
 				  create_info->auto_increment_value - 1 : 0);
-  hp_create_info.max_table_size=current_thd->variables.max_heap_table_size;
+  hp_create_info.max_table_size= (share->tmp_table == NO_TMP_TABLE) ?
+                             table_arg->in_use->variables.max_heap_table_size :
+                             table_arg->in_use->variables.memory_tmp_table_size;
   hp_create_info.with_auto_increment= found_real_auto_increment;
   max_rows = (ha_rows) (hp_create_info.max_table_size / mem_per_row);
   error= heap_create(fn_format(buff,name,"","",

--- 1.584/sql/mysqld.cc	2007-01-03 14:45:33 +03:00
+++ 1.585/sql/mysqld.cc	2007-01-03 14:45:33 +03:00
@@ -4625,7 +4625,7 @@
   OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_DEBUGGING,
   OPT_SORT_BUFFER, OPT_TABLE_CACHE,
   OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE,
-  OPT_TMP_TABLE_SIZE, OPT_THREAD_STACK,
+  OPT_MEMORY_TMP_TABLE_SIZE, OPT_TMP_TABLE_SIZE, OPT_THREAD_STACK,
   OPT_WAIT_TIMEOUT, OPT_MYISAM_REPAIR_THREADS,
   OPT_INNODB_MIRRORED_LOG_GROUPS,
   OPT_INNODB_LOG_FILES_IN_GROUP,
@@ -6013,11 +6013,16 @@
     (gptr*) &opt_date_time_formats[MYSQL_TIMESTAMP_TIME],
     (gptr*) &opt_date_time_formats[MYSQL_TIMESTAMP_TIME],
     0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
-  {"tmp_table_size", OPT_TMP_TABLE_SIZE,
+  {"memory_tmp_table_size", OPT_MEMORY_TMP_TABLE_SIZE,
    "If an in-memory temporary table exceeds this size, MySQL will automatically convert it to an on-disk MyISAM table.",
+   (gptr*) &global_system_variables.memory_tmp_table_size,
+   (gptr*) &max_system_variables.memory_tmp_table_size, 0, GET_ULL,
+   REQUIRED_ARG, 32*1024*1024L, 1024, MAX_MEM_TABLE_SIZE, 0, 1, 0},
+  {"tmp_table_size", OPT_TMP_TABLE_SIZE,
+   "A hit to limit on-disk temporary table size.",
    (gptr*) &global_system_variables.tmp_table_size,
    (gptr*) &max_system_variables.tmp_table_size, 0, GET_ULL,
-   REQUIRED_ARG, 32*1024*1024L, 1024, MAX_MEM_TABLE_SIZE, 0, 1, 0},
+   REQUIRED_ARG, 64*1024*1024L, 2048, ~0L, 0, 1, 0},
   {"transaction_alloc_block_size", OPT_TRANS_ALLOC_BLOCK_SIZE,
    "Allocation block size for transactions to be stored in binary log",
    (gptr*) &global_system_variables.trans_alloc_block_size,

--- 1.317/sql/sql_class.h	2007-01-03 14:45:33 +03:00
+++ 1.318/sql/sql_class.h	2007-01-03 14:45:33 +03:00
@@ -495,6 +495,7 @@
   ulonglong myisam_max_extra_sort_file_size;
   ulonglong myisam_max_sort_file_size;
   ulonglong max_heap_table_size;
+  ulonglong memory_tmp_table_size;
   ulonglong tmp_table_size;
   ha_rows select_limit;
   ha_rows max_join_size;

--- 1.480/sql/sql_select.cc	2007-01-03 14:45:33 +03:00
+++ 1.481/sql/sql_select.cc	2007-01-03 14:45:33 +03:00
@@ -9151,18 +9151,16 @@
       (select_options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) ==
       OPTION_BIG_TABLES || (select_options & TMP_TABLE_FORCE_MYISAM))
   {
-    table->file= get_new_handler(table, &table->mem_root,
-                                 table->s->db_type= DB_TYPE_MYISAM);
+    table->s->db_type= DB_TYPE_MYISAM;
     if (group &&
 	(param->group_parts > table->file->max_key_parts() ||
 	 param->group_length > table->file->max_key_length()))
       using_unique_constraint=1;
   }
   else
-  {
-    table->file= get_new_handler(table, &table->mem_root,
-                                 table->s->db_type= DB_TYPE_HEAP);
-  }
+    table->s->db_type= DB_TYPE_HEAP;
+
+  table->file= get_new_handler(table, &table->mem_root, table->s->db_type);
 
   if (!using_unique_constraint)
     reclength+= group_null_items;	// null flag is stored separately
@@ -9320,9 +9318,8 @@
     table->s->max_rows= ~(ha_rows) 0;
   else
     table->s->max_rows= (ha_rows) (((table->s->db_type == DB_TYPE_HEAP) ?
-                                    min(thd->variables.tmp_table_size,
-                                        thd->variables.max_heap_table_size) :
-                                    thd->variables.tmp_table_size)/
+                                   thd->variables.memory_tmp_table_size :
+                                   thd->variables.tmp_table_size)/
                                    table->s->reclength);
   set_if_bigger(table->s->max_rows,1);		// For dummy start options
   /*

--- 1.175/sql/set_var.cc	2007-01-03 14:45:33 +03:00
+++ 1.176/sql/set_var.cc	2007-01-03 14:45:33 +03:00
@@ -415,6 +415,8 @@
 					 fix_tx_isolation);
 sys_var_thd_ulonglong	sys_tmp_table_size("tmp_table_size",
 					   &SV::tmp_table_size);
+sys_var_thd_ulonglong	sys_memory_tmp_table_size("memory_tmp_table_size",
+					   &SV::memory_tmp_table_size);
 sys_var_bool_ptr  sys_timed_mutexes("timed_mutexes",
                                     &timed_mutexes);
 sys_var_const_str	sys_version("version", server_version);
@@ -756,6 +758,7 @@
   &sys_time_zone,
   &sys_tmpdir,
   &sys_tmp_table_size,
+  &sys_memory_tmp_table_size,
   &sys_trans_alloc_block_size,
   &sys_trans_prealloc_size,
   &sys_tx_isolation,
@@ -1077,6 +1080,7 @@
   {"time_zone",               (char*) &sys_time_zone,               SHOW_SYS},
   {sys_timed_mutexes.name,    (char*) &sys_timed_mutexes,       SHOW_SYS},
   {sys_tmp_table_size.name,   (char*) &sys_tmp_table_size,	    SHOW_SYS},
+  {sys_memory_tmp_table_size.name,   (char*) &sys_memory_tmp_table_size,	    SHOW_SYS},
   {sys_tmpdir.name,           (char*) &sys_tmpdir,	            SHOW_SYS},
   {sys_trans_alloc_block_size.name, (char*) &sys_trans_alloc_block_size,
    SHOW_SYS},
--- New file ---
+++ mysql-test/r/implicit_tmp_table.result	07/01/03 14:45:23
SET @@session.max_heap_table_size=0;
SET @@session.memory_tmp_table_size=0;
SET @@session.tmp_table_size=0;
SHOW VARIABLES LIKE 'max_heap_table_size';
Variable_name	Value
max_heap_table_size	16384
SHOW VARIABLES LIKE 'memory_tmp_table_size';
Variable_name	Value
memory_tmp_table_size	1024
SHOW VARIABLES LIKE 'tmp_table_size';
Variable_name	Value
tmp_table_size	2048
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
CREATE TABLE t1 (a VARCHAR(250)) ENGINE=MEMORY;
INSERT INTO t1 (a) VALUES (REPEAT('a', 250));
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
ERROR HY000: The table 't1' is full
DROP TABLE t1;
SET @@session.tmp_table_size=64*1024*1024;
SET @@session.memory_tmp_table_size=32*1024*1024;
CREATE TABLE t1 (a VARCHAR(250)) ENGINE=MEMORY;
INSERT INTO t1 (a) VALUES (REPEAT('a', 250));
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
ERROR HY000: The table 't1' is full
DROP TABLE t1;
CREATE TABLE t1 ( a VARCHAR(250) );
FLUSH STATUS;
INSERT INTO t1 SELECT RAND() FROM (SELECT 0 a UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) a, (SELECT 0 a UNION SELECT 10 UNION SELECT 20 UNION
SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION
SELECT 80 UNION SELECT 90) b, (SELECT 0 a UNION SELECT 100 UNION SELECT 200
UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT
700 UNION SELECT 800 UNION SELECT 900) c;
SHOW STATUS LIKE 'created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	0
FLUSH STATUS;
SET @@session.memory_tmp_table_size=0;
SET @@session.max_heap_table_size=64*1024*1024;
SELECT SQL_NO_CACHE a, COUNT(*) FROM t1 GROUP BY a ORDER BY NULL LIMIT 1;
SHOW STATUS LIKE 'created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
FLUSH STATUS;
SET @@session.memory_tmp_table_size=32*1024*1024;
SET @@session.max_heap_table_size=0;
SELECT SQL_NO_CACHE a, COUNT(*) FROM t1 GROUP BY a ORDER BY NULL LIMIT 1;
SHOW STATUS LIKE 'created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	0
FLUSH STATUS;
DROP TABLE t1;

--- New file ---
+++ mysql-test/t/implicit_tmp_table.test	07/01/03 14:45:23
#
# BUG #4291: max_heap_table_size affects creation of disk-based temporary table
#

SET @@session.max_heap_table_size=0;
SET @@session.memory_tmp_table_size=0;
SET @@session.tmp_table_size=0;
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'memory_tmp_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';

#
# Test if max_heap_table_size is working
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(250)) ENGINE=MEMORY;
INSERT INTO t1 (a) VALUES (REPEAT('a', 250));
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
--error ER_RECORD_FILE_FULL
INSERT INTO t1 SELECT a FROM t1;
DROP TABLE t1;
# this should depend on neither {memory_,}tmp_table_size
SET @@session.tmp_table_size=64*1024*1024;
SET @@session.memory_tmp_table_size=32*1024*1024;
CREATE TABLE t1 (a VARCHAR(250)) ENGINE=MEMORY;
INSERT INTO t1 (a) VALUES (REPEAT('a', 250));
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
INSERT INTO t1 SELECT a FROM t1;
--error ER_RECORD_FILE_FULL
INSERT INTO t1 SELECT a FROM t1;
DROP TABLE t1;

CREATE TABLE t1 ( a VARCHAR(250) );
FLUSH STATUS;
INSERT INTO t1 SELECT RAND() FROM (SELECT 0 a UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9) a, (SELECT 0 a UNION SELECT 10 UNION SELECT 20 UNION
SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION
SELECT 80 UNION SELECT 90) b, (SELECT 0 a UNION SELECT 100 UNION SELECT 200
UNION SELECT 300 UNION SELECT 400 UNION SELECT 500 UNION SELECT 600 UNION SELECT
700 UNION SELECT 800 UNION SELECT 900) c;
SHOW STATUS LIKE 'created_tmp_disk_tables';
FLUSH STATUS;

#
# Should create a tmp table on disk if memory_table_size is small and 
# tmp_table_size is large even when max_heap_table_size is large
#
SET @@session.memory_tmp_table_size=0;
SET @@session.max_heap_table_size=64*1024*1024;
--disable_result_log
SELECT SQL_NO_CACHE a, COUNT(*) FROM t1 GROUP BY a ORDER BY NULL LIMIT 1;
--enable_result_log
SHOW STATUS LIKE 'created_tmp_disk_tables';
FLUSH STATUS;

#
# Should not create tmp table on disk 
# if both {memory_,}tmp_table_size are large enough 
# even when max_heap_table_size is small
#
SET @@session.memory_tmp_table_size=32*1024*1024;
SET @@session.max_heap_table_size=0;
--disable_result_log
SELECT SQL_NO_CACHE a, COUNT(*) FROM t1 GROUP BY a ORDER BY NULL LIMIT 1;
--enable_result_log
SHOW STATUS LIKE 'created_tmp_disk_tables';
FLUSH STATUS;

DROP TABLE t1;

Thread
bk commit into 5.0 tree (ted:1.2358) BUG#4291Vladimir Shebordaev3 Jan