Debian Codegraph @

mysql-test/suite/perfschema/t/table_name.test

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