From: Vladimir Shebordaev Date: December 7 2006 1:38pm Subject: bk commit into 5.0 tree (ted:1.2342) BUG#4291 List-Archive: http://lists.mysql.com/commits/16582 X-Bug: 4291 Message-Id: <200612071338.kB7DcsAZ030136@localhost.localdomain> 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, 2006-12-07 16:38:48+03:00, ted@stripped +7 -0 BUG #4291: max_heap_table_size affects creation of disk-based temporary table mysql-test/r/internal_tmp_table.result@stripped, 2006-12-07 16:38:46+03:00, ted@stripped +65 -0 The results are just recorded out of t/internal_tmp_table.test output mysql-test/r/internal_tmp_table.result@stripped, 2006-12-07 16:38:46+03:00, ted@stripped +0 -0 mysql-test/t/internal_tmp_table.test@stripped, 2006-12-07 16:38:46+03:00, ted@stripped +78 -0 The test cases to ensure the temporary tables are created correctly mysql-test/t/internal_tmp_table.test@stripped, 2006-12-07 16:38:46+03:00, ted@stripped +0 -0 sql/ha_heap.cc@stripped, 2006-12-07 16:38:45+03:00, ted@stripped +3 -3 Max size of a memory temporary table is now set to tmp_table_size sql/mysqld.cc@stripped, 2006-12-07 16:38:45+03:00, ted@stripped +6 -1 New variable disk_tmp_table_size is introduced sql/set_var.cc@stripped, 2006-12-07 16:38:45+03:00, ted@stripped +4 -0 New variable disk_tmp_table_size is introduced sql/sql_class.h@stripped, 2006-12-07 16:38:45+03:00, ted@stripped +1 -0 New variable disk_tmp_table_size is introduced sql/sql_select.cc@stripped, 2006-12-07 16:38:45+03:00, ted@stripped +8 -13 Maximum data file size is now set to tmp_table_size for memory temporary tables and disk_tmp_table_size for on-disk ones # 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.80/sql/ha_heap.cc 2006-12-07 16:38:54 +03:00 +++ 1.81/sql/ha_heap.cc 2006-12-07 16:38:54 +03:00 @@ -630,8 +630,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; @@ -642,7 +640,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.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.582/sql/mysqld.cc 2006-12-07 16:38:54 +03:00 +++ 1.583/sql/mysqld.cc 2006-12-07 16:38:54 +03:00 @@ -4626,7 +4626,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_TMP_TABLE_SIZE, OPT_DISK_TMP_TABLE_SIZE, OPT_THREAD_STACK, OPT_WAIT_TIMEOUT, OPT_MYISAM_REPAIR_THREADS, OPT_INNODB_MIRRORED_LOG_GROUPS, OPT_INNODB_LOG_FILES_IN_GROUP, @@ -6014,6 +6014,11 @@ (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}, + {"disk_tmp_table_size", OPT_DISK_TMP_TABLE_SIZE, + "The temporary table cannot (much) exceed this size on disk.", + (gptr*) &global_system_variables.disk_tmp_table_size, + (gptr*) &max_system_variables.disk_tmp_table_size, 0, GET_ULONG, + REQUIRED_ARG, ~0L, 1024, ~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.313/sql/sql_class.h 2006-12-07 16:38:54 +03:00 +++ 1.314/sql/sql_class.h 2006-12-07 16:38:54 +03:00 @@ -497,6 +497,7 @@ ulonglong myisam_max_sort_file_size; ulonglong max_heap_table_size; ulonglong tmp_table_size; + ulong disk_tmp_table_size; ha_rows select_limit; ha_rows max_join_size; ulong auto_increment_increment, auto_increment_offset; --- 1.478/sql/sql_select.cc 2006-12-07 16:38:54 +03:00 +++ 1.479/sql/sql_select.cc 2006-12-07 16:38:54 +03:00 @@ -9152,19 +9152,17 @@ (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 @@ -9321,9 +9319,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.tmp_table_size : + thd->variables.disk_tmp_table_size)/ table->s->reclength); set_if_bigger(table->s->max_rows,1); // For dummy start options /* @@ -9711,10 +9708,8 @@ } MI_CREATE_INFO create_info; bzero((char*) &create_info,sizeof(create_info)); - - if ((options & (OPTION_BIG_TABLES | SELECT_SMALL_RESULT)) == - OPTION_BIG_TABLES) - create_info.data_file_length= ~(ulonglong) 0; + + create_info.data_file_length= table->in_use->variables.disk_tmp_table_size; if ((error=mi_create(table->s->table_name,table->s->keys,&keydef, (uint) (param->recinfo-param->start_recinfo), --- 1.173/sql/set_var.cc 2006-12-07 16:38:54 +03:00 +++ 1.174/sql/set_var.cc 2006-12-07 16:38:54 +03:00 @@ -416,6 +416,8 @@ fix_tx_isolation); sys_var_thd_ulonglong sys_tmp_table_size("tmp_table_size", &SV::tmp_table_size); +sys_var_thd_ulong sys_disk_tmp_table_size("disk_tmp_table_size", + &SV::disk_tmp_table_size); sys_var_bool_ptr sys_timed_mutexes("timed_mutexes", &timed_mutexes); sys_var_const_str sys_version("version", server_version); @@ -757,6 +759,7 @@ &sys_time_zone, &sys_tmpdir, &sys_tmp_table_size, + &sys_disk_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_disk_tmp_table_size.name, (char*) &sys_disk_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/internal_tmp_table.result 06/12/07 16:38:46 SET @@session.tmp_table_size=0; SET @@session.disk_tmp_table_size=0; SET @@session.max_heap_table_size=0; SHOW VARIABLES LIKE 'tmp_table_size'; Variable_name Value tmp_table_size 1024 SHOW VARIABLES LIKE 'disk_tmp_table_size'; Variable_name Value disk_tmp_table_size 1024 SHOW VARIABLES LIKE 'max_heap_table_size'; Variable_name Value max_heap_table_size 16384 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.disk_tmp_table_size=64*1024*1024; SET @@session.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.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.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/internal_tmp_table.test 06/12/07 16:38:46 # # BUG #4291: max_heap_table_size affects creation of disk-based temporary table # SET @@session.tmp_table_size=0; SET @@session.disk_tmp_table_size=0; SET @@session.max_heap_table_size=0; SHOW VARIABLES LIKE 'tmp_table_size'; SHOW VARIABLES LIKE 'disk_tmp_table_size'; SHOW VARIABLES LIKE 'max_heap_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 {disk_,}tmp_table_size SET @@session.disk_tmp_table_size=64*1024*1024; SET @@session.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 tmp_table_size is small and # disk_tmp_table_size is large even when max_heap_table_size is large # SET @@session.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 {disk,}tmp_table_size are large enough # even when max_heap_table_size is small # SET @@session.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;