From 72d23896d6590de7af3566e1f74f8dd1134f11f3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 26 Apr 2016 19:06:20 +0200 Subject: 5.6.30 --- mysql-test/suite/perfschema/r/sizing_low.result | 1 + mysql-test/suite/perfschema/r/table_name.result | 156 ++++++++++++++++++++++ mysql-test/suite/perfschema/t/sizing_low.test | 1 + mysql-test/suite/perfschema/t/table_name.test | 164 ++++++++++++++++++++++++ 4 files changed, 322 insertions(+) create mode 100644 mysql-test/suite/perfschema/r/table_name.result create mode 100644 mysql-test/suite/perfschema/t/table_name.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/perfschema/r/sizing_low.result b/mysql-test/suite/perfschema/r/sizing_low.result index dce5a994099..a698f55aa07 100644 --- a/mysql-test/suite/perfschema/r/sizing_low.result +++ b/mysql-test/suite/perfschema/r/sizing_low.result @@ -67,3 +67,4 @@ Performance_schema_table_instances_lost 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Performance_schema_users_lost 0 +CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); diff --git a/mysql-test/suite/perfschema/r/table_name.result b/mysql-test/suite/perfschema/r/table_name.result new file mode 100644 index 00000000000..d6369ffc79e --- /dev/null +++ b/mysql-test/suite/perfschema/r/table_name.result @@ -0,0 +1,156 @@ + +# +# TEST 1: Normal tables prefixed with "#sql" and "sql". +# +USE test; +CREATE TABLE `#sql_1` (a int, b text); +INSERT INTO `#sql_1` VALUES(1,'one'); + +CREATE TABLE `sql_1` (a int, b text); +INSERT INTO `sql_1` VALUES(1,'one'); + +# Verify that the tables are treated as normal tables . + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name +TABLE test #sql_1 +TABLE test sql_1 + +# Drop the tables, verify that the table objects are removed. + +DROP TABLE `#sql_1`; +DROP TABLE `sql_1`; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 2: Temporary tables, no special prefix. +# +CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM; +INSERT INTO sql_temp2_myisam VALUES(1,'one'); + +CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB; +INSERT INTO sql_temp2_innodb VALUES(1,'one'); + +# Confirm that the temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the tables, verify that the table objects are not created. + +DROP TABLE sql_temp2_myisam; +DROP TABLE sql_temp2_innodb; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 3: Temporary tables with the "#sql" prefix. +# +CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM; +CHECK TABLE `#sql_temp3_myisam`; +Table Op Msg_type Msg_text +test.#sql_temp3_myisam check status OK +INSERT INTO `#sql_temp3_myisam` VALUES(1,'one'); + +CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB; +CHECK TABLE `#sql_temp3_innodb`; +Table Op Msg_type Msg_text +test.#sql_temp3_innodb check status OK +INSERT INTO `#sql_temp3_innodb` VALUES(1,'one'); + +# Confirm that the temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the temporary tables. + +DROP TABLE `#sql_temp3_myisam`; +DROP TABLE `#sql_temp3_innodb`; + +# Confirm that the temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary +# when they are truncated. +# +CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `sql_temp4_myisam` VALUES(1,'one'); + +CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `#sql_temp4_myisam` VALUES(1,'one'); + +# Confirm that the MyISAM temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary. + +TRUNCATE TABLE `sql_temp4_myisam`; +TRUNCATE TABLE `#sql_temp4_myisam`; + +# Confirm that the recreated MyISAM tables are still regarded as temporary and ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the recreated MyISAM tables; + +DROP TABLE `sql_temp4_myisam`; +DROP TABLE `#sql_temp4_myisam`; + +# Confirm that the recreated temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 5: Generate temporary tables with ALTER MyISAM table. +# +USE test; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1), (2), (3); +ALTER TABLE t1 ADD COLUMN (b int); + +# Confirm that the recreated temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the MyISAM table + +DROP TABLE t1; + +# Confirm that no tables remain; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name diff --git a/mysql-test/suite/perfschema/t/sizing_low.test b/mysql-test/suite/perfschema/t/sizing_low.test index 4d02d41aecd..56446fd6973 100644 --- a/mysql-test/suite/perfschema/t/sizing_low.test +++ b/mysql-test/suite/perfschema/t/sizing_low.test @@ -7,3 +7,4 @@ --source ../include/sizing_auto.inc +CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); diff --git a/mysql-test/suite/perfschema/t/table_name.test b/mysql-test/suite/perfschema/t/table_name.test new file mode 100644 index 00000000000..00df678a392 --- /dev/null +++ b/mysql-test/suite/perfschema/t/table_name.test @@ -0,0 +1,164 @@ +# +# Performance Schema +# +# Verify that the Performance Schema correctly identifies normal and temporary +# tables with non-standard names. + +# The server uses the table name prefix "#sql" for temporary and intermediate +# tables, however user-defined tables having the "#sql" prefix are also permitted. +# Independent of the table name, temporary or intermediate tables always have the +# "#sql" prefix in the filename. (For non-temporary tables starting with "#", +# the "#" is encoded to @0023 in the filename.) +# +# Given the ambiguity with temporary table names, the Performance Schema identifies +# temporary tables tables either by the table category or by the filename. +# +--source include/have_perfschema.inc +--source include/not_embedded.inc + +--echo +--echo # +--echo # TEST 1: Normal tables prefixed with "#sql" and "sql". +--echo # +USE test; +CREATE TABLE `#sql_1` (a int, b text); +# INSERT forces path through get_table_share() +INSERT INTO `#sql_1` VALUES(1,'one'); +--echo +CREATE TABLE `sql_1` (a int, b text); +INSERT INTO `sql_1` VALUES(1,'one'); +--echo +--echo # Verify that the tables are treated as normal tables . +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the tables, verify that the table objects are removed. +--echo +DROP TABLE `#sql_1`; +DROP TABLE `sql_1`; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 2: Temporary tables, no special prefix. +--echo # +CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM; +INSERT INTO sql_temp2_myisam VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB; +INSERT INTO sql_temp2_innodb VALUES(1,'one'); +--echo +--echo # Confirm that the temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the tables, verify that the table objects are not created. +--echo +DROP TABLE sql_temp2_myisam; +DROP TABLE sql_temp2_innodb; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 3: Temporary tables with the "#sql" prefix. +--echo # +CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM; +CHECK TABLE `#sql_temp3_myisam`; +INSERT INTO `#sql_temp3_myisam` VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB; +CHECK TABLE `#sql_temp3_innodb`; +INSERT INTO `#sql_temp3_innodb` VALUES(1,'one'); +--echo +--echo # Confirm that the temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the temporary tables. +--echo +DROP TABLE `#sql_temp3_myisam`; +DROP TABLE `#sql_temp3_innodb`; +--echo +--echo # Confirm that the temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary +--echo # when they are truncated. +--echo # +CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `sql_temp4_myisam` VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `#sql_temp4_myisam` VALUES(1,'one'); +--echo +--echo # Confirm that the MyISAM temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary. +--echo +TRUNCATE TABLE `sql_temp4_myisam`; +TRUNCATE TABLE `#sql_temp4_myisam`; +--echo +--echo # Confirm that the recreated MyISAM tables are still regarded as temporary and ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the recreated MyISAM tables; +--echo +DROP TABLE `sql_temp4_myisam`; +DROP TABLE `#sql_temp4_myisam`; +--echo +--echo # Confirm that the recreated temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 5: Generate temporary tables with ALTER MyISAM table. +--echo # +USE test; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1), (2), (3); +# Force a path throug mysql_alter_table() and ha_create_table(). +ALTER TABLE t1 ADD COLUMN (b int); +--echo +--echo # Confirm that the recreated temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the MyISAM table +--echo +DROP TABLE t1; + +--echo +--echo # Confirm that no tables remain; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; -- cgit v1.2.1