Debian Codegraph @

mysql-test/suite/perfschema/r/table_name_myisam.result

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