Debian Codegraph @

mysql-test/suite/innodb/r/tablespace_temp_table_1.result

raw
1 call mtr.add_suppression("Please add another temp-data file or use 'autoextend' for the last data file");
2 call mtr.add_suppression("The table 't1' is full");
3 set global innodb_file_per_table = off;
4 SELECT @@global.innodb_file_per_table;
5 @@global.innodb_file_per_table
6 0
7 SELECT @mycount_SYS_DATAFILES:=COUNT(*) FROM information_schema.INNODB_DATAFILES;
8 @mycount_SYS_DATAFILES:=COUNT(*)
9 NUM
10 Warnings:
11 NUM 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
12 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
13 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
14 Warnings:
15 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
16 # creation of shared temp-tablespace in MYSQL_DATA_DIR
17 ibtmp1
18 # check no entries made INNODB_DATAFILES
19 SELECT @mycount_SYS_DATAFILES=COUNT(*) FROM information_schema.INNODB_DATAFILES;
20 @mycount_SYS_DATAFILES=COUNT(*)
21 1
22 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
23 REPEAT('a1',50));
24 START TRANSACTION;
25 INSERT INTO t1(b,c,d,e) VALUES (12.111,REPEAT('a2',50),REPEAT('a2',50),
26 REPEAT('a2',50));
27 INSERT INTO t1(b,c,d,e) VALUES (13.111,REPEAT('a3',50),REPEAT('a3',50),
28 REPEAT('a3',50));
29 UPDATE t1 SET e = REPEAT('ta',50);
30 DELETE FROM t1 WHERE a = 3;
31 SAVEPOINT A;
32 INSERT INTO t1(b,c,d,e) VALUES (14.111,REPEAT('a4',50),REPEAT('a4',50),
33 REPEAT('a4',50));
34 UPDATE t1 SET e = REPEAT('yb',50);
35 DELETE FROM t1 WHERE a = 1;
36 SAVEPOINT B;
37 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
38 a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
39 2 12.111 a2a2a2a2a2 a2a2a2a2a2 ybybybybyb
40 4 14.111 a4a4a4a4a4 a4a4a4a4a4 ybybybybyb
41 ROLLBACK TO SAVEPOINT A;
42 COMMIT;
43 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
44 a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
45 1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata
46 2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata
47 ALTER TABLE t1 ADD COLUMN f BLOB(100), ADD INDEX `idx1` (d);
48 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10),RIGHT(f,10) FROM t1;
49 a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10) RIGHT(f,10)
50 1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata NULL
51 2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata NULL
52 ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=INPLACE;
53 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
54 ALTER TABLE t1 DROP COLUMN f, DROP INDEX `idx1` , ADD INDEX `idx2` (d), ALGORITHM=COPY;
55 SELECT a,RIGHT(b,10),RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
56 a RIGHT(b,10) RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
57 1 11.111 a1a1a1a1a1 a1a1a1a1a1 tatatatata
58 2 12.111 a2a2a2a2a2 a2a2a2a2a2 tatatatata
59 DROP TABLE t1;
60 DROP TABLE IF EXISTS t1;
61 DROP DATABASE IF EXISTS test_db;
62 set global innodb_file_per_table = on;
63 SELECT @@global.innodb_file_per_table;
64 @@global.innodb_file_per_table
65 1
66 CREATE DATABASE test_db;
67 "Create con1 client connection"
68 Warnings:
69 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
70 Warnings:
71 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
72 Warnings:
73 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
74 Warnings:
75 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
76 Warnings:
77 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
78 Warnings:
79 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
80 Warnings:
81 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
82 Warnings:
83 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
84 Warnings:
85 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
86 Warnings:
87 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
88 Warnings:
89 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
90 Warnings:
91 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
92 Warnings:
93 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
94 Warnings:
95 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
96 Warnings:
97 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
98 Warnings:
99 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
100 Warnings:
101 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
102 Warnings:
103 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
104 Warnings:
105 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
106 Warnings:
107 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
108 Warnings:
109 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
110 Warnings:
111 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
112 Warnings:
113 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
114 Warnings:
115 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
116 Warnings:
117 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
118 Warnings:
119 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
120 Warnings:
121 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
122 Warnings:
123 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
124 Warnings:
125 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
126 Warnings:
127 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
128 Warnings:
129 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
130 Warnings:
131 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
132 Warnings:
133 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
134 Warnings:
135 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
136 Warnings:
137 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
138 Warnings:
139 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
140 Warnings:
141 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
142 Warnings:
143 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
144 Warnings:
145 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
146 Warnings:
147 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
148 Warnings:
149 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
150 Warnings:
151 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
152 Warnings:
153 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
154 Warnings:
155 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
156 Warnings:
157 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
158 Warnings:
159 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
160 Warnings:
161 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
162 Warnings:
163 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
164 Warnings:
165 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
166 Warnings:
167 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
168 Warnings:
169 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
170 Warnings:
171 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
172 Warnings:
173 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
174 Warnings:
175 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
176 Warnings:
177 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
178 Warnings:
179 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
180 Warnings:
181 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
182 Warnings:
183 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
184 Warnings:
185 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
186 Warnings:
187 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
188 Warnings:
189 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
190 Warnings:
191 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
192 Warnings:
193 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
194 # creation of shared temp-tablespace in MYSQL_DATA_DIR
195 ibtmp1
196 DROP TABLE IF EXISTS t1,t2,t3,t4;
197 SELECT @@global.innodb_file_per_table;
198 @@global.innodb_file_per_table
199 1
200 USE test;
201 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
202 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
203 Warnings:
204 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
205 CREATE TEMPORARY TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT, c BLOB(100),
206 d VARCHAR(100),e TEXT) ENGINE=InnoDB;
207 CREATE TEMPORARY TABLE t3 (val INT) ENGINE = InnoDB PARTITION BY LIST(val)(
208 PARTITION mypart1 VALUES IN (1,3,5),
209 PARTITION MyPart2 VALUES IN (2,4,6)
210 );
211 ERROR HY000: Cannot create temporary table with partitions
212 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
213 REPEAT('a1',50));
214 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t1',50),REPEAT('t1',50),
215 REPEAT('a2',50));
216 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('t2',50),REPEAT('t2',50),
217 REPEAT('a2',50));
218 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
219 a a RIGHT(t1.c,10) RIGHT(t2.c,10)
220 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;
221 a a RIGHT(t1.c,10) RIGHT(t2.c,10)
222 1 1 a1a1a1a1a1 t1t1t1t1t1
223 CREATE OR REPLACE VIEW vw1 AS SELECT * FROM t1;
224 ERROR HY000: View's SELECT refers to a temporary table 't1'
225 CREATE TEMPORARY TABLE t3 AS
226 SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
227 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
228 SELECT * FROM t3;
229 a RIGHT(t1.c,10) RIGHT(t2.c,10)
230 1 a1a1a1a1a1 t1t1t1t1t1
231 CREATE TABLE t4 AS
232 SELECT t1.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2
233 WHERE t1.c NOT LIKE t2.c AND t1.a=t2.a;
234 SELECT * FROM t4;
235 a RIGHT(t1.c,10) RIGHT(t2.c,10)
236 1 a1a1a1a1a1 t1t1t1t1t1
237 SELECT * FROM t3,t4 WHERE t3.a=t4.a;
238 a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
239 1 a1a1a1a1a1 t1t1t1t1t1 1 a1a1a1a1a1 t1t1t1t1t1
240 SELECT * FROM t3,t4 WHERE t3.a!=t4.a;
241 a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
242 LOCK TABLES t1 READ;
243 SELECT t1.a,t2.a,RIGHT(t1.c,10),RIGHT(t2.c,10) FROM t1,t2 WHERE t1.c LIKE t2.c;
244 a a RIGHT(t1.c,10) RIGHT(t2.c,10)
245 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;
246 a a RIGHT(t1.c,10) RIGHT(t2.c,10)
247 1 1 a1a1a1a1a1 t1t1t1t1t1
248 DELETE FROM t1;
249 SELECT * FROM t1;
250 a b c d e
251 UNLOCK TABLES;
252 LOCK TABLES t2 WRITE,t3 WRITE,t4 WRITE;
253 DELETE FROM t2 WHERE t2.c LIKE REPEAT('t2',50);
254 SELECT t2.a,RIGHT(t2.c,10) FROM t2;
255 a RIGHT(t2.c,10)
256 1 t1t1t1t1t1
257 DELETE FROM t3;
258 DELETE FROM t4;
259 SELECT * FROM t3,t4;
260 a RIGHT(t1.c,10) RIGHT(t2.c,10) a RIGHT(t1.c,10) RIGHT(t2.c,10)
261 UNLOCK TABLES;
262 ibtmp1
263 t4.ibd
264 DROP TABLE t1,t2,t3,t4;
265 SELECT @@global.innodb_file_per_table;
266 @@global.innodb_file_per_table
267 1
268 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
269 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
270 Warnings:
271 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
272 CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
273 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
274 Warnings:
275 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
276 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
277 REPEAT('a1',50));
278 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
279 REPEAT('a1',50));
280 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
281 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
282 1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
283 2 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
284 UPDATE t1 SET a = a + 5;
285 DELETE FROM t1 WHERE a > 6;
286 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
287 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
288 6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
289 INSERT INTO t2(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
290 REPEAT('a1',50));
291 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
292 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
293 1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
294 UPDATE t2 SET a = a + 5;
295 DELETE FROM t2 WHERE a > 6;
296 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t2;
297 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
298 6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
299 # shutting down the server
300 # list_file, no files expected - temp tablespace cleared on stopping server
301 # restart
302 CREATE TEMPORARY TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b FLOAT(5,3),
303 c BLOB(100),d VARCHAR(100),e TEXT) ENGINE=InnoDB;
304 Warnings:
305 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
306 INSERT INTO t1(b,c,d,e) VALUES (11.111,REPEAT('a1',50),REPEAT('a1',50),
307 REPEAT('a1',50));
308 INSERT INTO t1(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 t1;
311 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
312 1 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
313 2 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
314 UPDATE t1 SET a = a + 5;
315 DELETE FROM t1 WHERE a > 6;
316 SELECT a,b,RIGHT(c,10),RIGHT(d,10),RIGHT(e,10) FROM t1;
317 a b RIGHT(c,10) RIGHT(d,10) RIGHT(e,10)
318 6 11.111 a1a1a1a1a1 a1a1a1a1a1 a1a1a1a1a1
319 DROP TABLE t1;
320 # restart
321 SHOW TABLES;
322 Tables_in_test
323 t2
324 DROP TABLE t2;