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