Debian Codegraph @

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