Debian Codegraph @

mysql-test/suite/perfschema/r/table_name.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 CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB;
40 INSERT INTO sql_temp2_innodb VALUES(1,'one');
41 #
42 # Confirm that the temporary tables are ignored.
43 #
44 SELECT object_type, object_schema, object_name
45 FROM performance_schema.objects_summary_global_by_type
46 WHERE object_schema="test"
47 ORDER BY object_name;
48 object_type object_schema object_name
49 #
50 # Drop the tables, verify that the table objects are not created.
51 #
52 DROP TABLE sql_temp2_myisam;
53 DROP TABLE sql_temp2_innodb;
54 #
55 SELECT object_type, object_schema, object_name
56 FROM performance_schema.objects_summary_global_by_type
57 WHERE object_schema="test"
58 ORDER BY object_name;
59 object_type object_schema object_name
60 #
61 #
62 # TEST 3: Temporary tables with the "#sql" prefix.
63 #
64 CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM;
65 CHECK TABLE `#sql_temp3_myisam`;
66 Table Op Msg_type Msg_text
67 test.#sql_temp3_myisam check status OK
68 INSERT INTO `#sql_temp3_myisam` VALUES(1,'one');
69 #
70 CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB;
71 CHECK TABLE `#sql_temp3_innodb`;
72 Table Op Msg_type Msg_text
73 test.#sql_temp3_innodb check status OK
74 INSERT INTO `#sql_temp3_innodb` VALUES(1,'one');
75 #
76 # Confirm that the temporary tables are ignored.
77 #
78 SELECT object_type, object_schema, object_name
79 FROM performance_schema.objects_summary_global_by_type
80 WHERE object_schema="test"
81 ORDER BY object_name;
82 object_type object_schema object_name
83 #
84 # Drop the temporary tables.
85 #
86 DROP TABLE `#sql_temp3_myisam`;
87 DROP TABLE `#sql_temp3_innodb`;
88 #
89 # Confirm that the temporary tables are still ignored.
90 #
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 object_type object_schema object_name
96 #
97 #
98 # TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary
99 # when they are truncated.
100 #
101 CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM;
102 INSERT INTO `sql_temp4_myisam` VALUES(1,'one');
103 #
104 CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM;
105 INSERT INTO `#sql_temp4_myisam` VALUES(1,'one');
106 #
107 # Confirm that the MyISAM temporary tables are ignored.
108 #
109 SELECT object_type, object_schema, object_name
110 FROM performance_schema.objects_summary_global_by_type
111 WHERE object_schema="test"
112 ORDER BY object_name;
113 object_type object_schema object_name
114 #
115 # Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary.
116 #
117 TRUNCATE TABLE `sql_temp4_myisam`;
118 TRUNCATE TABLE `#sql_temp4_myisam`;
119 #
120 # Confirm that the recreated MyISAM tables are still regarded as temporary and ignored.
121 #
122 SELECT object_type, object_schema, object_name
123 FROM performance_schema.objects_summary_global_by_type
124 WHERE object_schema="test"
125 ORDER BY object_name;
126 object_type object_schema object_name
127 #
128 # Drop the recreated MyISAM tables;
129 #
130 DROP TABLE `sql_temp4_myisam`;
131 DROP TABLE `#sql_temp4_myisam`;
132 #
133 # Confirm that the recreated temporary tables are still ignored.
134 #
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 object_type object_schema object_name
140 #
141 #
142 # TEST 5: Generate temporary tables with ALTER MyISAM table.
143 #
144 USE test;
145 CREATE TABLE t1 (a int) ENGINE=MYISAM;
146 INSERT INTO t1 VALUES (1), (2), (3);
147 ALTER TABLE t1 ADD COLUMN (b int);
148 #
149 # Confirm that the recreated temporary tables are still ignored.
150 #
151 SELECT object_type, object_schema, object_name
152 FROM performance_schema.objects_summary_global_by_type
153 WHERE object_schema="test"
154 ORDER BY object_name;
155 object_type object_schema object_name
156 #
157 # Drop the MyISAM table
158 #
159 DROP TABLE t1;
160 #
161 # Confirm that no tables remain;
162 #
163 SELECT object_type, object_schema, object_name
164 FROM performance_schema.objects_summary_global_by_type
165 WHERE object_schema="test"
166 ORDER BY object_name;
167 object_type object_schema object_name