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
168
169
170
171
172
173
174
175
176
177
|
#
# Performance Schema
#
# Verify that the Performance Schema correctly identifies normal and temporary
# tables with non-standard names.
# The server uses the table name prefix "#sql" for temporary and intermediate
# tables, however user-defined tables having the "#sql" prefix are also permitted.
# Independent of the table name, temporary or intermediate tables always have the
# "#sql" prefix in the filename. (For non-temporary tables starting with "#",
# the "#" is encoded to @0023 in the filename.)
#
# Given the ambiguity with temporary table names, the Performance Schema identifies
# temporary tables tables either by the table category or by the filename.
#
--source include/have_perfschema.inc
--source include/have_innodb.inc
--source include/not_embedded.inc
--echo #
--echo #
--echo # TEST 1: Normal tables prefixed with "#sql" and "sql".
--echo #
USE test;
CREATE TABLE `#sql_1` (a int, b text);
# INSERT forces path through get_table_share()
INSERT INTO `#sql_1` VALUES(1,'one');
--echo #
CREATE TABLE `sql_1` (a int, b text);
INSERT INTO `sql_1` VALUES(1,'one');
--echo #
--echo # Verify that the tables are treated as normal tables .
--echo #
--sorted_result
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Drop the tables, verify that the table objects are removed.
--echo #
DROP TABLE `#sql_1`;
DROP TABLE `sql_1`;
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo #
--echo # TEST 2: Temporary tables, no special prefix.
--echo #
CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM;
INSERT INTO sql_temp2_myisam VALUES(1,'one');
--echo #
CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB;
INSERT INTO sql_temp2_innodb VALUES(1,'one');
--echo #
--echo # Confirm that the temporary tables are ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Drop the tables, verify that the table objects are not created.
--echo #
DROP TABLE sql_temp2_myisam;
DROP TABLE sql_temp2_innodb;
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo #
--echo # TEST 3: Temporary tables with the "#sql" prefix.
--echo #
CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM;
CHECK TABLE `#sql_temp3_myisam`;
INSERT INTO `#sql_temp3_myisam` VALUES(1,'one');
--echo #
CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB;
CHECK TABLE `#sql_temp3_innodb`;
INSERT INTO `#sql_temp3_innodb` VALUES(1,'one');
--echo #
--echo # Confirm that the temporary tables are ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Drop the temporary tables.
--echo #
DROP TABLE `#sql_temp3_myisam`;
DROP TABLE `#sql_temp3_innodb`;
--echo #
--echo # Confirm that the temporary tables are still ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo #
--echo # TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary
--echo # when they are truncated.
--echo #
CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM;
INSERT INTO `sql_temp4_myisam` VALUES(1,'one');
--echo #
CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM;
INSERT INTO `#sql_temp4_myisam` VALUES(1,'one');
--echo #
--echo # Confirm that the MyISAM temporary tables are ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary.
--echo #
TRUNCATE TABLE `sql_temp4_myisam`;
TRUNCATE TABLE `#sql_temp4_myisam`;
--echo #
--echo # Confirm that the recreated MyISAM tables are still regarded as temporary and ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Drop the recreated MyISAM tables;
--echo #
DROP TABLE `sql_temp4_myisam`;
DROP TABLE `#sql_temp4_myisam`;
--echo #
--echo # Confirm that the recreated temporary tables are still ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo #
--echo # TEST 5: Generate temporary tables with ALTER MyISAM table.
--echo #
USE test;
CREATE TABLE t1 (a int) ENGINE=MYISAM;
INSERT INTO t1 VALUES (1), (2), (3);
# Force a path throug mysql_alter_table() and ha_create_table().
ALTER TABLE t1 ADD COLUMN (b int);
--echo #
--echo # Confirm that the recreated temporary tables are still ignored.
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
--echo #
--echo # Drop the MyISAM table
--echo #
DROP TABLE t1;
--echo #
--echo # Confirm that no tables remain;
--echo #
SELECT object_type, object_schema, object_name
FROM performance_schema.objects_summary_global_by_type
WHERE object_schema="test"
ORDER BY object_name;
|
