1# 2# Performance Schema 3# 4# Verify that the Performance Schema correctly identifies normal and temporary 5# tables with non-standard names. 6 7# The server uses the table name prefix "#sql" for temporary and intermediate 8# tables, however user-defined tables having the "#sql" prefix are also permitted. 9# Independent of the table name, temporary or intermediate tables always have the 10# "#sql" prefix in the filename. (For non-temporary tables starting with "#", 11# the "#" is encoded to @0023 in the filename.) 12# 13# Given the ambiguity with temporary table names, the Performance Schema identifies 14# temporary tables tables either by the table category or by the filename. 15# 16--source include/have_perfschema.inc 17--source include/not_embedded.inc 18 19--echo 20--echo # 21--echo # TEST 1: Normal tables prefixed with "#sql" and "sql". 22--echo # 23USE test; 24CREATE TABLE `#sql_1` (a int, b text); 25# INSERT forces path through get_table_share() 26INSERT INTO `#sql_1` VALUES(1,'one'); 27--echo 28CREATE TABLE `sql_1` (a int, b text); 29INSERT INTO `sql_1` VALUES(1,'one'); 30--echo 31--echo # Verify that the tables are treated as normal tables . 32--echo 33SELECT object_type, object_schema, object_name 34FROM performance_schema.objects_summary_global_by_type 35WHERE object_schema="test" 36ORDER BY object_name; 37--echo 38--echo # Drop the tables, verify that the table objects are removed. 39--echo 40DROP TABLE `#sql_1`; 41DROP TABLE `sql_1`; 42--echo 43SELECT object_type, object_schema, object_name 44FROM performance_schema.objects_summary_global_by_type 45WHERE object_schema="test" 46ORDER BY object_name; 47 48--echo 49--echo # 50--echo # TEST 2: Temporary tables, no special prefix. 51--echo # 52CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM; 53INSERT INTO sql_temp2_myisam VALUES(1,'one'); 54--echo 55CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB; 56INSERT INTO sql_temp2_innodb VALUES(1,'one'); 57--echo 58--echo # Confirm that the temporary tables are ignored. 59--echo 60SELECT object_type, object_schema, object_name 61FROM performance_schema.objects_summary_global_by_type 62WHERE object_schema="test" 63ORDER BY object_name; 64--echo 65--echo # Drop the tables, verify that the table objects are not created. 66--echo 67DROP TABLE sql_temp2_myisam; 68DROP TABLE sql_temp2_innodb; 69--echo 70SELECT object_type, object_schema, object_name 71FROM performance_schema.objects_summary_global_by_type 72WHERE object_schema="test" 73ORDER BY object_name; 74 75--echo 76--echo # 77--echo # TEST 3: Temporary tables with the "#sql" prefix. 78--echo # 79CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM; 80CHECK TABLE `#sql_temp3_myisam`; 81INSERT INTO `#sql_temp3_myisam` VALUES(1,'one'); 82--echo 83CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB; 84CHECK TABLE `#sql_temp3_innodb`; 85INSERT INTO `#sql_temp3_innodb` VALUES(1,'one'); 86--echo 87--echo # Confirm that the temporary tables are ignored. 88--echo 89SELECT object_type, object_schema, object_name 90FROM performance_schema.objects_summary_global_by_type 91WHERE object_schema="test" 92ORDER BY object_name; 93--echo 94--echo # Drop the temporary tables. 95--echo 96DROP TABLE `#sql_temp3_myisam`; 97DROP TABLE `#sql_temp3_innodb`; 98--echo 99--echo # Confirm that the temporary tables are still ignored. 100--echo 101SELECT object_type, object_schema, object_name 102FROM performance_schema.objects_summary_global_by_type 103WHERE object_schema="test" 104ORDER BY object_name; 105 106--echo 107--echo # 108--echo # TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary 109--echo # when they are truncated. 110--echo # 111CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; 112INSERT INTO `sql_temp4_myisam` VALUES(1,'one'); 113--echo 114CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; 115INSERT INTO `#sql_temp4_myisam` VALUES(1,'one'); 116--echo 117--echo # Confirm that the MyISAM temporary tables are ignored. 118--echo 119SELECT object_type, object_schema, object_name 120FROM performance_schema.objects_summary_global_by_type 121WHERE object_schema="test" 122ORDER BY object_name; 123--echo 124--echo # Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary. 125--echo 126TRUNCATE TABLE `sql_temp4_myisam`; 127TRUNCATE TABLE `#sql_temp4_myisam`; 128--echo 129--echo # Confirm that the recreated MyISAM tables are still regarded as temporary and ignored. 130--echo 131SELECT object_type, object_schema, object_name 132FROM performance_schema.objects_summary_global_by_type 133WHERE object_schema="test" 134ORDER BY object_name; 135--echo 136--echo # Drop the recreated MyISAM tables; 137--echo 138DROP TABLE `sql_temp4_myisam`; 139DROP TABLE `#sql_temp4_myisam`; 140--echo 141--echo # Confirm that the recreated temporary tables are still ignored. 142--echo 143SELECT object_type, object_schema, object_name 144FROM performance_schema.objects_summary_global_by_type 145WHERE object_schema="test" 146ORDER BY object_name; 147 148--echo 149--echo # 150--echo # TEST 5: Generate temporary tables with ALTER MyISAM table. 151--echo # 152USE test; 153CREATE TABLE t1 (a int) ENGINE=MYISAM; 154INSERT INTO t1 VALUES (1), (2), (3); 155# Force a path throug mysql_alter_table() and ha_create_table(). 156ALTER TABLE t1 ADD COLUMN (b int); 157--echo 158--echo # Confirm that the recreated temporary tables are still ignored. 159--echo 160SELECT object_type, object_schema, object_name 161FROM performance_schema.objects_summary_global_by_type 162WHERE object_schema="test" 163ORDER BY object_name; 164--echo 165--echo # Drop the MyISAM table 166--echo 167DROP TABLE t1; 168 169--echo 170--echo # Confirm that no tables remain; 171--echo 172SELECT object_type, object_schema, object_name 173FROM performance_schema.objects_summary_global_by_type 174WHERE object_schema="test" 175ORDER BY object_name; 176