Debian Codegraph @

mysql-test/suite/innodb/t/tablespace_temp_table_1.test

raw
1 #
2 # WL#6560: InnoDB: separate tablespace for innodb-temp-tables.
3 #
4
5 # Avoid CrashReporter popup on Mac
6 --source include/not_crashrep.inc
7
8 call mtr.add_suppression("Please add another temp-data file or use 'autoextend' for the last data file");
9 call mtr.add_suppression("The table 't1' is full");
10
11 ################################################################################
12 #
13 # Will test following scenarios:
14
15 #
16 # 1. ddl + dml operation involving temp-tablespace.
17 # a) insert/delete/update/transacion/select
18 # b) create index/drop index/add column/drop column
19 # c) alter inplace/copy on temp table
20
21 # 2. ddl + dml operation on temp table from 2 sessions.
22 # a) Both session create 20 tables each of same name.
23 # b) No error expected across session as temp tables are not be
24 # visible across session.
25 # c) create drop same table multiple time
26
27 # 3. Temp table operation -
28 # a) can not create temp table with partition
29 # b) can not create/drop view on temp tables.
30 # c) select/join on temp tables.
31 # d) Lock/flush on tables
32 # e) create temp table as select
33
34
35 # 4. Temp table operation -
36 # a) restart on existing datadir and see ibtmp1 removed on clean shutdown
37 #
38
39 ################################################################################
40
41 #-----------------------------------------------------------------------------
42 #
43
44 set global innodb_file_per_table = off;
45 let $MYSQL_DATA_DIR = `select @@datadir`;
46 let $MYSQL_TMP_DIR = `select @@tmpdir`;
47
48 #-----------------------------------------------------------------------------
49 #
50 # 1. ddl + dml operation involving temp-tablespace.
51 # a) insert/delete/update/transacion/select
52 # b) create index/drop index/add column/drop column
53 # c) alter inplace/copy on temp table
54 SELECT @@global.innodb_file_per_table;
55 --replace_column 1 NUM
56 SELECT @mycount_SYS_DATAFILES:=COUNT(*) FROM information_schema.INNODB_DATAFILES;
57 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
58 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
59
60 --echo # creation of shared temp-tablespace in MYSQL_DATA_DIR
61 --list_files $MYSQL_DATA_DIR/ ibtmp1*
62
63 --echo # check no entries made INNODB_DATAFILES
64 SELECT @mycount_SYS_DATAFILES=COUNT(*) FROM information_schema.INNODB_DATAFILES;
65
66 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
67 REPEAT('a1',50));
68
69 # DML+TRANSACTION
70 START TRANSACTION;
71 INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
72 REPEAT('a2',50));
73 INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
74 REPEAT('a3',50));
75 UPDATE t1 SET e = REPEAT('ta',50);
76 DELETE FROM t1 WHERE a = 3;
77 SAVEPOINT A;
78 INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
79 REPEAT('a4',50));
80 UPDATE t1 SET e = REPEAT('yb',50);
81 DELETE FROM t1 WHERE a = 1;
82 SAVEPOINT B;
83 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
84 ROLLBACK TO SAVEPOINT A;
85 COMMIT;
86 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
87
88 # DDL
89 ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
90 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10),RIGHT(f,10) FROM t1;
91 --error 1845
92 ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=INPLACE;
93 ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
94 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
95 # drop table
96 DROP TABLE t1;
97
98
99 #-----------------------------------------------------------------------------
100 #
101 # 2. ddl + dml operation on temp table from 2 sessions.
102 # a) Both session create 20 tables each of same name.
103 # b) No error expected across session as temp tables are not be
104 # visible across session.
105 # c) create drop same table multiple time
106 #
107 --disable_warnings
108 DROP TABLE IF EXISTS t1;
109 DROP DATABASE IF EXISTS test_db;
110 --enable_warnings
111 # check by allowing file per table on
112 set global innodb_file_per_table = on;
113 SELECT @@global.innodb_file_per_table;
114 let $counter= 2;
115
116 CREATE DATABASE test_db;
117
118 --echo "Create con1 client connection"
119 connect (con1,localhost,root,,);
120
121 --disable_query_log
122 while ($counter<23) {
123 # create table from con1 connection
124 connection con1;
125 USE test_db;
126 EVAL CREATE TEMPORARY TABLE t$counter (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
127 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
128 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
129 REPEAT('a1',50));
130 START TRANSACTION;
131 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
132 REPEAT('a2',50));
133 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
134 REPEAT('a3',50));
135 EVAL UPDATE t$counter SET e = REPEAT('ta',50);
136 EVAL DELETE FROM t$counter WHERE a = 3;
137 SAVEPOINT A;
138 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
139 REPEAT('a4',50));
140 EVAL UPDATE t$counter SET e = REPEAT('yb',50);
141 EVAL DELETE FROM t$counter WHERE a = 1;
142 SAVEPOINT B;
143 ROLLBACK TO SAVEPOINT A;
144 COMMIT;
145 EVAL ALTER TABLE t$counter ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
146 EVAL ALTER TABLE t$counter DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
147
148 # create same table from default connection
149 connection default;
150 USE test_db;
151 EVAL CREATE TEMPORARY TABLE t$counter (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
152 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
153 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
154 REPEAT('a1',50));
155 START TRANSACTION;
156 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
157 REPEAT('a2',50));
158 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
159 REPEAT('a3',50));
160 EVAL UPDATE t$counter SET e = REPEAT('ta',50);
161 EVAL DELETE FROM t$counter WHERE a = 3;
162 SAVEPOINT A;
163 EVAL INSERT INTO t$counter(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
164 REPEAT('a4',50));
165 EVAL UPDATE t$counter SET e = REPEAT('yb',50);
166 EVAL DELETE FROM t$counter WHERE a = 1;
167 SAVEPOINT B;
168 ROLLBACK TO SAVEPOINT A;
169 COMMIT;
170 EVAL ALTER TABLE t$counter ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
171 EVAL ALTER TABLE t$counter DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
172
173 # create and drop same table in loop
174 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
175 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
176 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
177 REPEAT('a1',50));
178 START TRANSACTION;
179 INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
180 REPEAT('a2',50));
181 INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
182 REPEAT('a3',50));
183 UPDATE t1 SET e = REPEAT('ta',50);
184 DELETE FROM t1 WHERE a = 3;
185 SAVEPOINT A;
186 INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
187 REPEAT('a4',50));
188 UPDATE t1 SET e = REPEAT('yb',50);
189 DELETE FROM t1 WHERE a = 1;
190 SAVEPOINT B;
191 ROLLBACK TO SAVEPOINT A;
192 COMMIT;
193 ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
194 ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
195 DROP TABLE t1;
196
197 # check separate ibd file is not created
198 --list_files $MYSQL_DATA_DIR/test_db/ *ibd
199 --inc $counter
200 }
201
202
203 DROP DATABASE test_db;
204 USE test;
205 --enable_query_log
206 --echo # creation of shared temp-tablespace in MYSQL_DATA_DIR
207 --list_files $MYSQL_DATA_DIR/ ibtmp1*
208
209 #-----------------------------------------------------------------------------
210 #
211 # 3. Temp table operation -
212 # a) can not create temp table with partition
213 # b) can not create/drop view on temp tables.
214 # c) select/join on temp tables.
215 # d) Lock/flush on tables
216 # e) create temp table as select
217 #
218 --disable_warnings
219 DROP TABLE IF EXISTS t1,t2,t3,t4;
220 --enable_warnings
221 # check by allowing file per table on
222 SELECT @@global.innodb_file_per_table;
223 USE test;
224
225 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
226 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
227 CREATE TEMPORARY TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT, c BLOB(100),
228 d VARCHAR(100),e TEXT) ENGINE=InnoDB;
229
230 # Can not create temp table with partition
231 --ERROR 1562
232 CREATE TEMPORARY TABLE t3 (val INT) ENGINE = InnoDB PARTITION BY LIST(val)(
233 PARTITION mypart1 VALUES IN (1,3,5),
234 PARTITION MyPart2 VALUES IN (2,4,6)
235 );
236
237 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
238 REPEAT('a1',50));
239 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t1',50),REPEAT('t1',50),
240 REPEAT('a2',50));
241 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t2',50),REPEAT('t2',50),
242 REPEAT('a2',50));
243
244 # Check with join of
245 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
246 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
247
248 # you cannot create a TEMPORARY view
249 --ERROR 1352
250 CREATE OR REPLACE VIEW vw1 AS SELECT * FROM t1;
251
252 # Check with create temp as <select>
253 CREATE TEMPORARY TABLE t3 AS
254 SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
255 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
256 SELECT * FROM t3;
257 CREATE TABLE t4 AS
258 SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
259 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
260 SELECT * FROM t4;
261 # join on temp and non temp table
262 SELECT * FROM t3,t4 WHERE t3.a=t4.a;
263 SELECT * FROM t3,t4 WHERE t3.a!=t4.a;
264
265 #Acquire lock and flush
266 LOCK TABLES t1 READ;
267 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
268 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
269 # delete records even type is read as lock table is ignored for temp tables
270 DELETE FROM t1;
271 SELECT * FROM t1;
272 UNLOCK TABLES;
273 LOCK TABLES t2 WRITE,t3 WRITE,t4 WRITE;
274 DELETE FROM t2 WHERE t2.c LIKE REPEAT('t2',50);
275 SELECT t2.a,RIGHT(t2.c,10) FROM t2;
276 DELETE FROM t3;
277 DELETE FROM t4;
278 SELECT * FROM t3,t4;
279 UNLOCK TABLES;
280
281 --list_files $MYSQL_DATA_DIR/ ibtmp1*
282 --list_files $MYSQL_DATA_DIR/test/ t4*ibd*
283 DROP TABLE t1,t2,t3,t4;
284
285
286 #-----------------------------------------------------------------------------
287 #
288 # 4. Temp table operation -
289 # a) restart on existing datadir and see ibtmp1 removed on clean shutdown
290 #
291 # check by allowing file per table on
292 SELECT @@global.innodb_file_per_table;
293
294 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
295 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
296 CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
297 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
298
299 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
300 REPEAT('a1',50));
301 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
302 REPEAT('a1',50));
303 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
304 UPDATE t1 SET a = a + 5;
305 DELETE FROM t1 WHERE a > 6;
306 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
307
308 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
309 REPEAT('a1',50));
310 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
311 UPDATE t2 SET a = a + 5;
312 DELETE FROM t2 WHERE a > 6;
313 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
314
315 #
316 --echo # shutting down the server
317 --source include/shutdown_mysqld.inc
318 --echo # list_file, no files expected - temp tablespace cleared on stopping server
319 --list_files $MYSQL_DATA_DIR/ ibtmp1*
320 #
321 --source include/start_mysqld.inc
322
323 # check temp table after restart
324 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
325 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
326
327 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
328 REPEAT('a1',50));
329 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
330 REPEAT('a1',50));
331 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
332 UPDATE t1 SET a = a + 5;
333 DELETE FROM t1 WHERE a > 6;
334 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
335 DROP TABLE t1;
336
337 --source include/restart_mysqld.inc
338
339 # check not temp table
340 SHOW TABLES;
341 DROP TABLE t2;