From: Jorgen Loland Date: February 10 2012 3:07pm Subject: bzr push into mysql-trunk branch (jorgen.loland:3877 to 3878) Bug#13627632 List-Archive: http://lists.mysql.com/commits/142828 X-Bug: 13627632 Message-Id: <20120210150728.E4F57839@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3878 Jorgen Loland 2012-02-10 Bug#13627632 - INFORMATION SCHEMA TABLES DO NOT PACK LONG VARCHAR COLUMNS db_create_option needs HA_OPTION_PACK_RECORD to be set in order to create MyISAM tables with packed record format. When processing queries that access "normal" tables, temporary table fields are created in create_tmp_field_from_field(). On the other hand, queries against information_schema tables create fields for the temporary table in create_tmp_field_for_schema(). This patch aligns the two functions by setting db_create_options-flag HA_OPTION_PACK_RECORD when a VARCHAR column is added for schema temp tables just as is done for "normal" temp tables. The result is that temp tables for information schema queries use the packed MyISAM format if they contain sufficiently large VARCHAR columns. @ mysql-test/suite/opt_trace/r/temp_table.result Add test that I_S temporary tables use the packed MyISAM format @ mysql-test/suite/opt_trace/t/temp_table.test Add test that I_S temporary tables use the packed MyISAM format @ sql/sql_tmp_table.cc Set HA_OPTION_PACK_RECORD in create_tmp_field_for_schema() if a VARCHAR column is added. modified: mysql-test/suite/opt_trace/r/temp_table.result mysql-test/suite/opt_trace/t/temp_table.test sql/sql_tmp_table.cc 3877 Sunny Bains 2012-02-10 BUG#12739098 - 62401: ASSERTION TRX->ERROR_STATE == DB_SUCCESS, QUE0QUE.C LINE 1264 ON TRUNCATE Move a test in innodb-index.test, that was added as part of this bug fix, to innodb-index-debug.test. This test uses a debug feature to simulate errors. added: mysql-test/suite/innodb/r/innodb-index-debug.result mysql-test/suite/innodb/t/innodb-index-debug.test modified: mysql-test/suite/innodb/r/innodb-index.result mysql-test/suite/innodb/t/innodb-index.test === modified file 'mysql-test/suite/opt_trace/r/temp_table.result' --- a/mysql-test/suite/opt_trace/r/temp_table.result 2012-01-04 09:51:39 +0000 +++ b/mysql-test/suite/opt_trace/r/temp_table.result 2012-02-10 09:06:59 +0000 @@ -516,4 +516,132 @@ SELECT uniq, col1, col2 FROM t1 GROUP BY ] /* steps */ } 0 0 SET GLOBAL tmp_table_size= @old_size; +SELECT pool_id FROM information_schema.innodb_buffer_page LIMIT 1; +pool_id +0 +SELECT * FROM information_schema.OPTIMIZER_TRACE; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +SELECT pool_id FROM information_schema.innodb_buffer_page LIMIT 1 { + "steps": [ + { + "creating_tmp_table": { + "tmp_table_info": { + "database": "information_schema", + "table": "innodb_buffer_page", + "row_length": 6852, + "key_length": 0, + "unique_constraint": false, + "location": "memory (heap)", + "row_limit_estimate": 1 + } /* tmp_table_info */ + } /* creating_tmp_table */ + }, + { + "join_preparation": { + "select#": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ select `information_schema`.`innodb_buffer_page`.`POOL_ID` AS `pool_id` from `information_schema`.`innodb_buffer_page` limit 1" + } + ] /* steps */ + } /* join_preparation */ + }, + { + "join_optimization": { + "select#": 1, + "steps": [ + { + "table_dependencies": [ + { + "database": "information_schema", + "table": "innodb_buffer_page", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [ + ] /* depends_on_map_bits */ + } + ] /* table_dependencies */ + }, + { + "rows_estimation": [ + { + "database": "information_schema", + "table": "innodb_buffer_page", + "table_scan": { + "rows": 2, + "cost": 10 + } /* table_scan */ + } + ] /* rows_estimation */ + }, + { + "considered_execution_plans": [ + { + "database": "information_schema", + "table": "innodb_buffer_page", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 2, + "cost": 10.1, + "chosen": true + } + ] /* considered_access_paths */ + } /* best_access_path */, + "cost_for_plan": 10.5, + "rows_for_plan": 2, + "chosen": true + } + ] /* considered_execution_plans */ + }, + { + "attaching_conditions_to_tables": { + "original_condition": null, + "attached_conditions_computation": [ + ] /* attached_conditions_computation */, + "attached_conditions_summary": [ + { + "database": "information_schema", + "table": "innodb_buffer_page", + "attached": null + } + ] /* attached_conditions_summary */ + } /* attaching_conditions_to_tables */ + }, + { + "refine_plan": [ + { + "database": "information_schema", + "table": "innodb_buffer_page", + "access_type": "table_scan" + } + ] /* refine_plan */ + } + ] /* steps */ + } /* join_optimization */ + }, + { + "join_execution": { + "select#": 1, + "steps": [ + { + "converting_tmp_table_to_myisam": { + "cause": "memory_table_size_exceeded", + "tmp_table_info": { + "database": "information_schema", + "table": "innodb_buffer_page", + "row_length": 6852, + "key_length": 0, + "unique_constraint": false, + "location": "disk (MyISAM)", + "record_format": "packed" + } /* tmp_table_info */ + } /* converting_tmp_table_to_myisam */ + } + ] /* steps */ + } /* join_execution */ + } + ] /* steps */ +} 0 0 DROP TABLE t1, tmp; === modified file 'mysql-test/suite/opt_trace/t/temp_table.test' --- a/mysql-test/suite/opt_trace/t/temp_table.test 2011-11-18 12:17:45 +0000 +++ b/mysql-test/suite/opt_trace/t/temp_table.test 2012-02-10 09:06:59 +0000 @@ -45,4 +45,9 @@ SELECT * FROM information_schema.OPTIMIZ SET GLOBAL tmp_table_size= @old_size; +# Temp tables for I_S tables. Uses HEAP temporary table. +# Converts it to MyISAM packed record format due to heap size limitation +SELECT pool_id FROM information_schema.innodb_buffer_page LIMIT 1; +SELECT * FROM information_schema.OPTIMIZER_TRACE; + DROP TABLE t1, tmp; === modified file 'sql/sql_tmp_table.cc' --- a/sql/sql_tmp_table.cc 2012-01-25 08:46:00 +0000 +++ b/sql/sql_tmp_table.cc 2012-02-10 09:06:59 +0000 @@ -210,9 +210,12 @@ static Field *create_tmp_field_for_schem field= new Field_blob(item->max_length, item->maybe_null, item->name, item->collation.collation); else + { field= new Field_varstring(item->max_length, item->maybe_null, item->name, table->s, item->collation.collation); + table->s->db_create_options|= HA_OPTION_PACK_RECORD; + } if (field) field->init(table); return field; No bundle (reason: useless for push emails).