Hi Sergey! As discussed on the mdev page, I've implemented the fix for incrementing the created_tmp_tables variable. I've attached the patch for review. The changes in the patch are explained within the mdev page. commit 53d78644b793f7b667e71863ccd0b1ba54c894f3 Author: Vicențiu Ciorbaru <cvicentiu@gmail.com> Date: Sun Feb 22 03:40:17 2015 +0200 MDEV-7586: Merged derived tables/VIEWs increment created_tmp_tables Temporary table count fix. The number of temporary tables was increased when the table is not actually created. (when do_not_open was passed as TRUE to create_tmp_table). Another issue fixed is that create_tmp_table was called incorrectly in create_result_table, having keep_row_order passed for the do_not_open parameter and keep_row_order always set to false. diff --git a/mysql-test/r/tmp_table_count-7586.result b/mysql-test/r/tmp_table_count-7586.result new file mode 100644 index 0000000..0c526e0 --- /dev/null +++ b/mysql-test/r/tmp_table_count-7586.result @@ -0,0 +1,83 @@ +create table t2 (a int); +insert into t2 values (1),(2),(3); +create view v2 as select a from t2; +flush status; +select * from v2; +a +1 +2 +3 +show status like '%Created_tmp%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_files 0 +Created_tmp_tables 0 +explain select * from v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +select * from (select * from t2) T1; +a +1 +2 +3 +show status like '%Created_tmp%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_files 0 +Created_tmp_tables 0 +explain select * from (select * from t2) T1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +drop view v2; +drop table t2; +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +# Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +sum(created_tmp_tables) +2 +show status like '%Created_tmp%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_files 0 +Created_tmp_tables 2 +drop table t3; +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +# Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +sum(created_tmp_tables) +1 +show status like '%Created_tmp%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_files 0 +Created_tmp_tables 1 +drop table t1,t2,t3; +truncate table performance_schema.events_statements_history_long; +flush status; +# Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +sum(created_tmp_tables) +0 +show status like '%Created_tmp%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_files 0 +Created_tmp_tables 0 diff --git a/mysql-test/t/tmp_table_count-7586.test b/mysql-test/t/tmp_table_count-7586.test new file mode 100644 index 0000000..756913e --- /dev/null +++ b/mysql-test/t/tmp_table_count-7586.test @@ -0,0 +1,53 @@ +# MDEV-7586 regression test. +# Test if created_tmp_tables status variable is correctly incremented. +--source include/have_perfschema.inc + +create table t2 (a int); +insert into t2 values (1),(2),(3); +create view v2 as select a from t2; + +flush status; +select * from v2; +show status like '%Created_tmp%'; + +explain select * from v2; + +select * from (select * from t2) T1; +show status like '%Created_tmp%'; + +explain select * from (select * from t2) T1; + + +drop view v2; +drop table t2; + + +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); + +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; +drop table t3; + +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; + +drop table t1,t2,t3; + +truncate table performance_schema.events_statements_history_long; +flush status; +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index d5e3334..2122349 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3890,7 +3890,6 @@ SJ_TMP_TABLE::create_sj_weedout_tmp_table(THD *thd) /* STEP 1: Get temporary table name */ - thd->inc_status_created_tmp_tables(); if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES)) temp_pool_slot = bitmap_lock_set_next(&temp_pool); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 9cdf5ce..4463eb7 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -3676,7 +3676,7 @@ create_result_table(THD *thd_arg, List<Item> *column_types, if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types, (ORDER*) 0, is_union_distinct, 1, options, HA_POS_ERROR, (char*) table_alias, - keep_row_order))) + !create_table, keep_row_order))) return TRUE; col_stat= (Column_statistics*) table->in_use->alloc(table->s->fields * diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4393f02..bb381ff 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15802,7 +15802,6 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, (int) distinct, (int) save_sum_fields, (ulong) rows_limit, MY_TEST(group))); - thd->inc_status_created_tmp_tables(); thd->query_plan_flags|= QPLAN_TMP_TABLE; if (use_temp_pool && !(test_flags & TEST_KEEP_TMP_TABLES)) @@ -16756,14 +16755,19 @@ bool open_tmp_table(TABLE *table) HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { - table->file->print_error(error,MYF(0)); /* purecov: inspected */ - table->db_stat=0; - return(1); + table->file->print_error(error, MYF(0)); /* purecov: inspected */ + table->db_stat= 0; + return 1; } table->db_stat= HA_OPEN_KEYFILE+HA_OPEN_RNDFILE; - (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - table->created= TRUE; - return(0); + (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ + if (!table->created) + { + table->created= TRUE; + table->in_use->inc_status_created_tmp_tables(); + } + + return 0; } @@ -16780,7 +16784,7 @@ bool open_tmp_table(TABLE *table) options Option bits DESCRIPTION - Create an internal emporary table according to passed description. The is + Create an internal temporary table according to passed description. The is assumed to have one unique index or constraint. The passed array or TMP_ENGINE_COLUMNDEF structures must have this form: @@ -16799,7 +16803,7 @@ bool open_tmp_table(TABLE *table) */ -bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, +bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, TMP_ENGINE_COLUMNDEF *start_recinfo, TMP_ENGINE_COLUMNDEF **recinfo, ulonglong options) @@ -16930,8 +16934,10 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, goto err; } table->in_use->inc_status_created_tmp_disk_tables(); + table->in_use->inc_status_created_tmp_tables(); table->in_use->query_plan_flags|= QPLAN_TMP_DISK; share->db_record_offset= 1; + table->created= TRUE; DBUG_RETURN(0); err: DBUG_RETURN(1); @@ -16971,7 +16977,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, /* Create internal MyISAM temporary table */ -bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, +bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, TMP_ENGINE_COLUMNDEF *start_recinfo, TMP_ENGINE_COLUMNDEF **recinfo, ulonglong options) @@ -17076,6 +17082,7 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, goto err; } table->in_use->inc_status_created_tmp_disk_tables(); + table->in_use->inc_status_created_tmp_tables(); table->in_use->query_plan_flags|= QPLAN_TMP_DISK; share->db_record_offset= 1; table->created= TRUE; @@ -17109,7 +17116,7 @@ create_internal_tmp_table_from_heap(THD *thd, TABLE *table, if (is_duplicate) *is_duplicate= FALSE; - if (table->s->db_type() != heap_hton || + if (table->s->db_type() != heap_hton || error != HA_ERR_RECORD_FILE_FULL) { /* @@ -17139,8 +17146,8 @@ create_internal_tmp_table_from_heap(THD *thd, TABLE *table, new_table.no_rows= table->no_rows; if (create_internal_tmp_table(&new_table, table->key_info, start_recinfo, recinfo, - thd->lex->select_lex.options | - thd->variables.option_bits)) + thd->lex->select_lex.options | + thd->variables.option_bits)) goto err2; if (open_tmp_table(&new_table)) goto err1; @@ -17203,7 +17210,7 @@ create_internal_tmp_table_from_heap(THD *thd, TABLE *table, new_table.s= table->s; // Keep old share *table= new_table; *table->s= share; - + table->file->change_table_ptr(table, table->s); table->use_all_columns(); if (save_proc_info) diff --git a/sql/sql_select.h b/sql/sql_select.h index 7d53731..9cad225 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1788,10 +1788,6 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); -bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, - TMP_ENGINE_COLUMNDEF *start_recinfo, - TMP_ENGINE_COLUMNDEF **recinfo, - ulonglong options, my_bool big_tables); /* General routine to change field->ptr of a NULL-terminated array of Field @@ -1879,20 +1875,20 @@ void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line); #define RATIO_TO_PACK_ROWS 2 #define MIN_STRING_LENGTH_TO_PACK_ROWS 10 -TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, - ORDER *group, bool distinct, bool save_sum_fields, - ulonglong select_options, ha_rows rows_limit, - const char* alias, bool do_not_open=FALSE, +TABLE *create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields, + ORDER *group, bool distinct, bool save_sum_fields, + ulonglong select_options, ha_rows rows_limit, + const char* alias, bool do_not_open=FALSE, bool keep_row_order= FALSE); void free_tmp_table(THD *thd, TABLE *entry); bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table, TMP_ENGINE_COLUMNDEF *start_recinfo, - TMP_ENGINE_COLUMNDEF **recinfo, + TMP_ENGINE_COLUMNDEF **recinfo, int error, bool ignore_last_dupp_key_error, bool *is_duplicate); -bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, +bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, TMP_ENGINE_COLUMNDEF *start_recinfo, - TMP_ENGINE_COLUMNDEF **recinfo, + TMP_ENGINE_COLUMNDEF **recinfo, ulonglong options); bool open_tmp_table(TABLE *table); void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps); Regards, Vicențiu