Debian Codegraph @

mysql-test/suite/innodb/t/innodb-wl5522-1.test

raw
1 # Not supported in embedded
2 --source include/not_embedded.inc
3
4 -- source include/have_innodb.inc
5
6 call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
7
8 let MYSQLD_DATADIR =`SELECT @@datadir`;
9
10 # Following testcases are created from JET cases (where import
11 # export instance are differnt server )
12 # Here test will be run on same import and export instance.
13
14 CREATE DATABASE testdb_wl5522;
15
16 # case 1
17 CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
18 INSERT INTO testdb_wl5522.t1 VALUES (1),(123),(331);
19 SELECT c1 FROM testdb_wl5522.t1;
20 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
21 SELECT * FROM testdb_wl5522.t1 ORDER BY c1;
22
23 perl;
24 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
25 ib_backup_tablespaces("testdb_wl5522", "t1");
26 EOF
27
28 UNLOCK TABLES;
29
30 DROP TABLE testdb_wl5522.t1;
31 CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
32 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
33
34
35 perl;
36 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
37 ib_discard_tablespaces("testdb_wl5522", "t1");
38 ib_restore_tablespaces("testdb_wl5522", "t1");
39 EOF
40
41 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
42 SELECT * FROM testdb_wl5522.t1 ORDER BY c1;
43 DROP TABLE testdb_wl5522.t1;
44
45 # case 2
46 CREATE TABLE testdb_wl5522.t1 (
47 col1 BIT(1),
48 col2 BOOLEAN,
49 col3 TINYINT,
50 col4 SMALLINT,
51 col5 MEDIUMINT,
52 col6 INT,
53 col7 BIGINT,
54 col8 FLOAT (14,3) ,
55 col9 DOUBLE (14,3),
56 col10 VARCHAR(20),
57 col11 TEXT ,
58 col12 ENUM('a','b','c'),
59 col13 TEXT,
60 col14 CHAR(20),
61 col15 VARBINARY (400),
62 col16 BINARY(40),
63 col17 BLOB (400),
64 col18 INT NOT NULL PRIMARY KEY,
65 col19 DATE,
66 col20 DATETIME,
67 col21 TIMESTAMP,
68 col22 TIME,
69 col23 YEAR ) ENGINE = Innodb;
70
71 CREATE INDEX idx1 ON testdb_wl5522.t1(col18);
72 CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10));
73 CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12);
74 CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8);
75 INSERT INTO testdb_wl5522.t1 VALUES
76 (1,1,-128,32767,-8388608,2147483647,-9223372036854775808, 92233720368.222,
77 -92233720368.222,'aaa', + 'aaaaaaaaaa','b','bbbbb','ccccc',
78 REPEAT('d',40),REPEAT('d',40),REPEAT('d',40),1,'1000-01-01',
79 '3000-12-31 23:59:59.99','1990-01-01 00:00:01.00',
80 '01:59:59.00','1901');
81 INSERT INTO testdb_wl5522.t1 VALUES
82 (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
83 NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
84 --error 1048
85 INSERT INTO testdb_wl5522.t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
86 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
87 --error 1062
88 INSERT INTO testdb_wl5522.t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
89 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
90
91 FLUSH TABLES testdb_wl5522.t1 WITH READ LOCK;
92 SELECT COUNT(*) FROM testdb_wl5522.t1;
93
94 perl;
95 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
96 ib_backup_tablespaces("testdb_wl5522", "t1");
97 EOF
98 UNLOCK TABLES;
99
100 DROP TABLE testdb_wl5522.t1;
101
102 CREATE TABLE testdb_wl5522.t1 (
103 col1 BIT(1),
104 col2 BOOLEAN,
105 col3 TINYINT,
106 col4 SMALLINT,
107 col5 MEDIUMINT,
108 col6 INT,
109 col7 BIGINT,
110 col8 FLOAT (14,3) ,
111 col9 DOUBLE (14,3),
112 col10 VARCHAR(20),
113 col11 TEXT,
114 col12 ENUM('a','b','c'),
115 col13 TEXT,
116 col14 CHAR(20) ,
117 col15 VARBINARY (400) ,
118 col16 BINARY(40),
119 col17 BLOB (400) ,
120 col18 INT NOT NULL PRIMARY KEY,
121 col19 DATE ,
122 col20 DATETIME ,
123 col21 TIMESTAMP ,
124 col22 TIME,
125 col23 YEAR ) ENGINE = Innodb;
126
127 CREATE INDEX idx1 ON testdb_wl5522.t1(col18);
128 CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10));
129 CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12);
130 CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8);
131
132 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
133
134 perl;
135 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
136 ib_discard_tablespaces("testdb_wl5522", "t1");
137 ib_restore_tablespaces("testdb_wl5522", "t1");
138 EOF
139
140 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
141 CHECK TABLE testdb_wl5522.t1;
142
143 SELECT COUNT(*) FROM testdb_wl5522.t1;
144
145 DROP TABLE testdb_wl5522.t1;
146
147
148 # case 3 - with blob objects
149
150 CREATE TABLE testdb_wl5522.t1 (
151 col_1_varbinary VARBINARY (4000) ,
152 col_2_varchar VARCHAR (4000),
153 col_3_text TEXT (4000),
154 col_4_blob BLOB (4000),
155 col_5_text TEXT (4000),
156 col_6_varchar VARCHAR (4000),
157 col_7_binary BINARY (255)
158 ) ROW_FORMAT=DYNAMIC ENGINE = Innodb;
159
160 INSERT INTO testdb_wl5522.t1 VALUES(
161 REPEAT('a', 4000),REPEAT('o', 4000),REPEAT('a', 4000), REPEAT('o', 4000),
162 REPEAT('a', 4000),REPEAT('a', 4000),REPEAT('a', 255));
163
164 SELECT col_1_varbinary = REPEAT("a", 4000),
165 col_2_varchar = REPEAT("o", 4000) ,
166 col_3_text = REPEAT("a", 4000) ,
167 col_4_blob = REPEAT("o", 4000) ,
168 col_5_text = REPEAT("a", 4000) ,
169 col_6_varchar = REPEAT("a", 4000) ,
170 col_7_binary = REPEAT("a", 255)
171 FROM testdb_wl5522.t1;
172 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
173
174 perl;
175 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
176 ib_backup_tablespaces("testdb_wl5522", "t1");
177 EOF
178
179 UNLOCK TABLES;
180
181 DROP TABLE testdb_wl5522.t1;
182
183 CREATE TABLE testdb_wl5522.t1 (
184 col_1_varbinary VARBINARY (4000) ,
185 col_2_varchar VARCHAR (4000),
186 col_3_text TEXT (4000),
187 col_4_blob BLOB (4000),
188 col_5_text TEXT (4000),
189 col_6_varchar VARCHAR (4000),
190 col_7_binary BINARY (255)
191 ) ROW_FORMAT=DYNAMIC ENGINE = Innodb;
192
193 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
194
195 perl;
196 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
197 ib_discard_tablespaces("testdb_wl5522", "t1");
198 ib_restore_tablespaces("testdb_wl5522", "t1");
199 EOF
200
201 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
202
203 SELECT col_1_varbinary = REPEAT("a", 4000) ,
204 col_2_varchar = REPEAT("o", 4000) ,
205 col_3_text = REPEAT("a", 4000) ,
206 col_4_blob = REPEAT("o", 4000) ,
207 col_5_text = REPEAT("a", 4000) ,
208 col_6_varchar = REPEAT("a", 4000) ,
209 col_7_binary = REPEAT("a", 255)
210 FROM testdb_wl5522.t1;
211
212 DROP TABLE testdb_wl5522.t1;
213
214 # case 4 - trasportable tablesace with autoincrement
215 CREATE TABLE testdb_wl5522.t1 (
216 col_1_int INT AUTO_INCREMENT,
217 col_2_varchar VARCHAR (20),
218 PRIMARY KEY (col_1_int)) ENGINE = Innodb;
219
220 INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2'),(3,'a3');
221 INSERT INTO testdb_wl5522.t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6');
222
223 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
224 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
225
226 perl;
227 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
228 ib_backup_tablespaces("testdb_wl5522", "t1");
229 EOF
230
231 UNLOCK TABLES;
232
233 DROP TABLE testdb_wl5522.t1;
234
235 CREATE TABLE testdb_wl5522.t1 (
236 col_1_int INT AUTO_INCREMENT,
237 col_2_varchar VARCHAR (20),
238 PRIMARY KEY (col_1_int)) ENGINE = Innodb;
239 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
240
241
242 perl;
243 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
244 ib_discard_tablespaces("testdb_wl5522", "t1");
245 ib_restore_tablespaces("testdb_wl5522", "t1");
246 EOF
247
248 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
249 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
250
251 # error on inserting duplicate value
252 --error 1062
253 INSERT INTO testdb_wl5522.t1 VALUES (1,'a1');
254 # insert new values
255 INSERT INTO testdb_wl5522.t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
256 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
257 # check table can be altered
258 ALTER TABLE testdb_wl5522.t1 MODIFY col_1_int BIGINT;
259 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
260
261 DROP TABLE testdb_wl5522.t1;
262
263 # case 5 - check with primary and foreign key
264 CREATE TABLE testdb_wl5522.t1 (
265 col_1_int INT,col_2_varchar VARCHAR (20),
266 PRIMARY KEY (col_2_varchar)) ENGINE = Innodb;
267
268 CREATE TABLE testdb_wl5522.t1_fk (
269 col_1_int INT,col_2_varchar VARCHAR (20),
270 PRIMARY KEY (col_1_int),
271 FOREIGN KEY (col_2_varchar) REFERENCES testdb_wl5522.t1(col_2_varchar)
272 ) ENGINE = Innodb;
273
274
275 INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
276 INSERT INTO testdb_wl5522.t1_fk VALUES (1,'a1'),(2,'a2'),(3,'a3');
277
278 SELECT * FROM testdb_wl5522.t1;
279 SELECT * FROM testdb_wl5522.t1_fk;
280 FLUSH TABLES testdb_wl5522.t1,testdb_wl5522.t1_fk FOR EXPORT;
281
282 perl;
283 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
284 ib_backup_tablespaces("testdb_wl5522", "t1");
285 ib_backup_tablespaces("testdb_wl5522", "t1_fk");
286 EOF
287
288 UNLOCK TABLES;
289
290 DROP TABLE testdb_wl5522.t1_fk,testdb_wl5522.t1;
291
292 CREATE TABLE testdb_wl5522.t1 (
293 col_1_int INT,col_2_varchar VARCHAR (20),
294 PRIMARY KEY (col_2_varchar)) ENGINE = Innodb;
295
296 CREATE TABLE testdb_wl5522.t1_fk (
297 col_1_int INT,col_2_varchar VARCHAR (20),
298 PRIMARY KEY (col_1_int),
299 FOREIGN KEY (col_2_varchar) REFERENCES testdb_wl5522.t1(col_2_varchar)
300 ) ENGINE = Innodb;
301
302 # Alter table discrad table is not allowed with foreign_key_checks = 1
303 SET foreign_key_checks = 0;
304 ALTER TABLE testdb_wl5522.t1_fk DISCARD TABLESPACE;
305 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
306 SET foreign_key_checks = 1;
307
308
309 perl;
310 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
311 ib_discard_tablespaces("testdb_wl5522", "t1");
312 ib_discard_tablespaces("testdb_wl5522", "t1_fk");
313 ib_restore_tablespaces("testdb_wl5522", "t1");
314 ib_restore_tablespaces("testdb_wl5522", "t1_fk");
315 EOF
316
317 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
318 ALTER TABLE testdb_wl5522.t1_fk IMPORT TABLESPACE;
319 SELECT * FROM testdb_wl5522.t1;
320 SELECT * FROM testdb_wl5522.t1_fk;
321
322 # Enter Invalid value: PK-FK relationship violation
323 --error 1452
324 INSERT INTO testdb_wl5522.t1_fk VALUES (100,'a100');
325
326 SET AUTOCOMMIT = 0;
327 INSERT INTO testdb_wl5522.t1_fk VALUES (4,'a4'),(5,'a5');
328 ROLLBACK;
329 SELECT * FROM testdb_wl5522.t1_fk;
330
331 DROP TABLE testdb_wl5522.t1_fk,testdb_wl5522.t1;
332 SET AUTOCOMMIT = 1;
333
334
335 # case 6 - transporatbale tablespace with transactions
336 CREATE TABLE testdb_wl5522.t1 (
337 col_1_int int,col_2_varchar VARCHAR (20),
338 PRIMARY KEY (col_2_varchar)) ENGINE = Innodb;
339 SET AUTOCOMMIT = 0;
340 INSERT INTO testdb_wl5522.t1 VALUES (1,'a1'),(2,'a2');
341 SELECT * FROM testdb_wl5522.t1;
342 COMMIT;
343 INSERT INTO testdb_wl5522.t1 VALUES (3,'a3'),(4,'a4');
344 ROLLBACK;
345 INSERT INTO testdb_wl5522.t1 VALUES (5,'a5'),(6,'a6');
346 COMMIT;
347 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
348 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
349
350 perl;
351 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
352 ib_backup_tablespaces("testdb_wl5522", "t1");
353 EOF
354
355 UNLOCK TABLES;
356
357 DROP TABLE testdb_wl5522.t1;
358 CREATE TABLE testdb_wl5522.t1 (
359 col_1_int int,col_2_varchar VARCHAR (20),
360 PRIMARY KEY (col_2_varchar)) ENGINE = Innodb;
361 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
362 SET AUTOCOMMIT = 0;
363
364 perl;
365 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
366 ib_discard_tablespaces("testdb_wl5522", "t1");
367 ib_restore_tablespaces("testdb_wl5522", "t1");
368 EOF
369
370 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
371
372
373 INSERT INTO testdb_wl5522.t1 VALUES (7,'a7'),(8,'a8');
374 COMMIT;
375 INSERT INTO testdb_wl5522.t1 VALUES (9,'a9'),(10,'a10');
376 ROLLBACK;
377 INSERT INTO testdb_wl5522.t1 VALUES (11,'a11'),(12,'a12');
378 COMMIT;
379 SELECT * FROM testdb_wl5522.t1 ORDER BY col_1_int;
380 SET AUTOCOMMIT = 1;
381
382 DROP TABLE testdb_wl5522.t1;
383
384 #case 7 - transpotable tablespace with transaction(earlier failed with jet)
385
386 CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
387 INSERT INTO testdb_wl5522.t1 VALUES (100),(200),(300);
388 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
389 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
390
391 perl;
392 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
393 ib_backup_tablespaces("testdb_wl5522", "t1");
394 EOF
395
396 UNLOCK TABLES;
397
398 DROP TABLE testdb_wl5522.t1;
399 CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
400 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
401
402
403 perl;
404 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
405 ib_discard_tablespaces("testdb_wl5522", "t1");
406 ib_restore_tablespaces("testdb_wl5522", "t1");
407 EOF
408
409 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
410 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
411 SET AUTOCOMMIT = 0;
412 INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103);
413 COMMIT;
414 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
415 SET AUTOCOMMIT = 1;
416 DROP TABLE testdb_wl5522.t1;
417
418
419 # case 8 - negative cases
420 CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
421 INSERT INTO testdb_wl5522.t1 VALUES (100),(200),(300);
422 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
423 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
424
425 perl;
426 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
427 ib_backup_tablespaces("testdb_wl5522", "t1");
428 EOF
429
430 UNLOCK TABLES;
431
432 # try if we can flush again
433 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
434 UNLOCK TABLES;
435
436 DROP TABLE testdb_wl5522.t1;
437
438 # create table with incorrect schema
439 CREATE TABLE testdb_wl5522.t1 ( i bigint) ENGINE = Innodb;
440 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
441
442 perl;
443 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
444 ib_discard_tablespaces("testdb_wl5522", "t1");
445 ib_restore_tablespaces("testdb_wl5522", "t1");
446 EOF
447
448 # error as mismatch in column data type
449 --error ER_TABLE_SCHEMA_MISMATCH
450 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
451
452 # explicilty delet idb file before creating table with correct schema
453 perl;
454 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
455 ib_unlink_tablespace("testdb_wl5522", "t1");
456 EOF
457
458 DROP TABLE testdb_wl5522.t1;
459
460 CREATE TABLE testdb_wl5522.t1 ( i int ) ENGINE = Innodb;
461 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
462
463 perl;
464 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
465 ib_discard_tablespaces("testdb_wl5522", "t1");
466 ib_restore_tablespaces("testdb_wl5522", "t1");
467 EOF
468
469 # Import should succeed
470 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
471 # Try to import twice
472 --error 1813
473 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
474 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
475
476 SET AUTOCOMMIT = 0;
477 INSERT INTO testdb_wl5522.t1 VALUES (101),(102),(103);
478 COMMIT;
479 SELECT * FROM testdb_wl5522.t1 ORDER BY i;
480 DROP TABLE testdb_wl5522.t1;
481 CREATE TABLE testdb_wl5522.t1 (i int) ENGINE = Innodb;
482 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
483 # do not delete ibt file and try to import
484 perl;
485 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
486 ib_restore_tablespaces("testdb_wl5522", "t1");
487 EOF
488 #--error 1000
489 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
490 DROP TABLE testdb_wl5522.t1;
491 SET AUTOCOMMIT = 1;
492
493
494 # case 9 - empty table import
495 CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
496 SET AUTOCOMMIT = 0;
497 INSERT INTO testdb_wl5522.t1 VALUES (1),(123),(331);
498 SELECT c1 FROM testdb_wl5522.t1;
499 ROLLBACK;
500 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
501
502 perl;
503 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
504 ib_backup_tablespaces("testdb_wl5522", "t1");
505 EOF
506
507 UNLOCK TABLES;
508
509 DROP TABLE testdb_wl5522.t1;
510 CREATE TABLE testdb_wl5522.t1 (c1 INT ) ENGINE = Innodb;
511 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
512
513
514 perl;
515 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
516 ib_discard_tablespaces("testdb_wl5522", "t1");
517 ib_restore_tablespaces("testdb_wl5522", "t1");
518 EOF
519
520 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
521 SELECT * FROM testdb_wl5522.t1 ORDER BY c1;
522 DROP TABLE testdb_wl5522.t1;
523 SET AUTOCOMMIT = 1;
524
525 # case 10 - tt with prefix index
526
527 CREATE TABLE testdb_wl5522.t1 (col_1 CHAR (255) ,
528 col_2 VARCHAR (255), col_3 VARCHAR (255),
529 col_4 VARCHAR (255),col_5 VARCHAR (255),
530 col_6 text (255), col_7 text (255),
531 col_8 text (255),col_9 text (255),
532 col_10 BLOB (255),col_11 BLOB (255),
533 col_12 BLOB (255), col_13 BLOB (255),
534 col_14 BLOB (255) , col_15 int ) ENGINE = innodb;
535
536 CREATE INDEX prefix_idx ON testdb_wl5522.t1(
537 col_1 (50),col_2 (50),col_3 (50),
538 col_4 (50),col_5 (50),col_6 (50),
539 col_7 (50),col_8 (50),col_9 (50),
540 col_10 (50),col_11 (50),col_12 (50),
541 col_13(50));
542
543 INSERT INTO testdb_wl5522.t1 VALUES (
544 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
545 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
546 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
547 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
548 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
549
550 SELECT
551 col_1 = REPEAT("col1_00001",10),
552 col_2 = REPEAT("col2_00001",10),
553 col_3 = REPEAT("col3_00001",10),
554 col_4 = REPEAT("col4_00001",10),
555 col_5 = REPEAT("col5_00001",10),
556 col_6 = REPEAT("col6_00001",10),
557 col_7 = REPEAT("col7_00001",10),
558 col_8 = REPEAT("col8_00001",10),
559 col_9 = REPEAT("col9_00001",10),
560 col_10 = REPEAT("col10_00001",10),
561 col_11 = REPEAT("col11_00001",10),
562 col_12 = REPEAT("col12_00001",10),
563 col_13 = REPEAT("col13_00001",10),
564 col_14 = REPEAT("col14_00001",10),
565 col_15
566 FROM testdb_wl5522.t1;
567
568 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
569
570 perl;
571 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
572 ib_backup_tablespaces("testdb_wl5522", "t1");
573 EOF
574
575 UNLOCK TABLES;
576
577 DROP TABLE testdb_wl5522.t1;
578
579 CREATE TABLE testdb_wl5522.t1 (col_1 CHAR (255) ,
580 col_2 VARCHAR (255), col_3 VARCHAR (255),
581 col_4 VARCHAR (255),col_5 VARCHAR (255),
582 col_6 text (255), col_7 text (255),
583 col_8 text (255),col_9 text (255),
584 col_10 BLOB (255),col_11 BLOB (255),
585 col_12 BLOB (255), col_13 BLOB (255),
586 col_14 BLOB (255) , col_15 int ) ENGINE = innodb;
587
588 CREATE INDEX prefix_idx ON testdb_wl5522.t1(
589 col_1 (50),col_2 (50),col_3 (50),
590 col_4 (50),col_5 (50),col_6 (50),
591 col_7 (50),col_8 (50),col_9 (50),
592 col_10 (50),col_11 (50),col_12 (50),
593 col_13(50));
594
595
596 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
597
598
599 perl;
600 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
601 ib_discard_tablespaces("testdb_wl5522", "t1");
602 ib_restore_tablespaces("testdb_wl5522", "t1");
603 EOF
604
605 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
606
607
608 SELECT
609 col_1 = REPEAT("col1_00001",10),
610 col_2 = REPEAT("col2_00001",10),
611 col_3 = REPEAT("col3_00001",10),
612 col_4 = REPEAT("col4_00001",10),
613 col_5 = REPEAT("col5_00001",10),
614 col_6 = REPEAT("col6_00001",10),
615 col_7 = REPEAT("col7_00001",10),
616 col_8 = REPEAT("col8_00001",10),
617 col_9 = REPEAT("col9_00001",10),
618 col_10 = REPEAT("col10_00001",10),
619 col_11 = REPEAT("col11_00001",10),
620 col_12 = REPEAT("col12_00001",10),
621 col_13 = REPEAT("col13_00001",10),
622 col_14 = REPEAT("col14_00001",10),
623 col_15
624 FROM testdb_wl5522.t1;
625
626 DROP TABLE testdb_wl5522.t1;
627
628
629 # case 11 - tt with secondary index
630
631 CREATE TABLE testdb_wl5522.t1 (col_1 CHAR (255) ,
632 col_2 VARCHAR (255), col_3 VARCHAR (255),
633 col_4 VARCHAR (255),col_5 VARCHAR (255),
634 col_6 text (255), col_7 text (255),
635 col_8 text (255),col_9 text (255),
636 col_10 BLOB (255),col_11 BLOB (255),
637 col_12 BLOB (255), col_13 BLOB (255),
638 col_14 BLOB (255) , col_15 int ) ENGINE = innodb;
639
640 CREATE INDEX idx1 ON testdb_wl5522.t1(col_1);
641 CREATE INDEX idx2 ON testdb_wl5522.t1(col_2);
642 CREATE INDEX idx3 ON testdb_wl5522.t1(col_3);
643 CREATE INDEX idx4 ON testdb_wl5522.t1(col_4);
644 CREATE INDEX idx5 ON testdb_wl5522.t1(col_5);
645 CREATE INDEX idx6 ON testdb_wl5522.t1(col_6(255));
646 CREATE INDEX idx7 ON testdb_wl5522.t1(col_7(255));
647 CREATE INDEX idx8 ON testdb_wl5522.t1(col_8(255));
648 CREATE INDEX idx9 ON testdb_wl5522.t1(col_9(255));
649 CREATE INDEX idx10 ON testdb_wl5522.t1(col_10(255));
650 CREATE INDEX idx11 ON testdb_wl5522.t1(col_11(255));
651 CREATE INDEX idx12 ON testdb_wl5522.t1(col_12(255));
652 CREATE INDEX idx13 ON testdb_wl5522.t1(col_13(255));
653 CREATE INDEX idx14 ON testdb_wl5522.t1(col_14(255));
654
655 INSERT INTO testdb_wl5522.t1 VALUES (
656 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
657 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
658 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
659 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
660 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
661
662 SELECT
663 col_1 = REPEAT("col1_00001",10),
664 col_2 = REPEAT("col2_00001",10),
665 col_3 = REPEAT("col3_00001",10),
666 col_4 = REPEAT("col4_00001",10),
667 col_5 = REPEAT("col5_00001",10),
668 col_6 = REPEAT("col6_00001",10),
669 col_7 = REPEAT("col7_00001",10),
670 col_8 = REPEAT("col8_00001",10),
671 col_9 = REPEAT("col9_00001",10),
672 col_10 = REPEAT("col10_00001",10),
673 col_11 = REPEAT("col11_00001",10),
674 col_12 = REPEAT("col12_00001",10),
675 col_13 = REPEAT("col13_00001",10),
676 col_14 = REPEAT("col14_00001",10),
677 col_15
678 FROM testdb_wl5522.t1;
679
680 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
681
682 perl;
683 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
684 ib_backup_tablespaces("testdb_wl5522", "t1");
685 EOF
686
687 UNLOCK TABLES;
688
689 DROP TABLE testdb_wl5522.t1;
690
691 CREATE TABLE testdb_wl5522.t1 (col_1 CHAR (255) ,
692 col_2 VARCHAR (255), col_3 VARCHAR (255),
693 col_4 VARCHAR (255),col_5 VARCHAR (255),
694 col_6 text (255), col_7 text (255),
695 col_8 text (255),col_9 text (255),
696 col_10 BLOB (255),col_11 BLOB (255),
697 col_12 BLOB (255), col_13 BLOB (255),
698 col_14 BLOB (255) , col_15 int ) ENGINE = innodb;
699
700 CREATE INDEX idx1 ON testdb_wl5522.t1(col_1);
701 CREATE INDEX idx2 ON testdb_wl5522.t1(col_2);
702 CREATE INDEX idx3 ON testdb_wl5522.t1(col_3);
703 CREATE INDEX idx4 ON testdb_wl5522.t1(col_4);
704 CREATE INDEX idx5 ON testdb_wl5522.t1(col_5);
705 CREATE INDEX idx6 ON testdb_wl5522.t1(col_6(255));
706 CREATE INDEX idx7 ON testdb_wl5522.t1(col_7(255));
707 CREATE INDEX idx8 ON testdb_wl5522.t1(col_8(255));
708 CREATE INDEX idx9 ON testdb_wl5522.t1(col_9(255));
709 CREATE INDEX idx10 ON testdb_wl5522.t1(col_10(255));
710 CREATE INDEX idx11 ON testdb_wl5522.t1(col_11(255));
711 CREATE INDEX idx12 ON testdb_wl5522.t1(col_12(255));
712 CREATE INDEX idx13 ON testdb_wl5522.t1(col_13(255));
713 CREATE INDEX idx14 ON testdb_wl5522.t1(col_14(255));
714
715 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
716
717
718 perl;
719 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
720 ib_discard_tablespaces("testdb_wl5522", "t1");
721 ib_restore_tablespaces("testdb_wl5522", "t1");
722 EOF
723
724 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
725
726 SELECT
727 col_1 = REPEAT("col1_00001",10),
728 col_2 = REPEAT("col2_00001",10),
729 col_3 = REPEAT("col3_00001",10),
730 col_4 = REPEAT("col4_00001",10),
731 col_5 = REPEAT("col5_00001",10),
732 col_6 = REPEAT("col6_00001",10),
733 col_7 = REPEAT("col7_00001",10),
734 col_8 = REPEAT("col8_00001",10),
735 col_9 = REPEAT("col9_00001",10),
736 col_10 = REPEAT("col10_00001",10),
737 col_11 = REPEAT("col11_00001",10),
738 col_12 = REPEAT("col12_00001",10),
739 col_13 = REPEAT("col13_00001",10),
740 col_14 = REPEAT("col14_00001",10),
741 col_15
742 FROM testdb_wl5522.t1;
743
744 # perform transaction on impoted table
745 SET AUTOCOMMIT = 0;
746 INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000);
747 SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
748 ROLLBACK;
749 SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
750 INSERT INTO testdb_wl5522.t1(col_15) VALUES (15000),(16000);
751 COMMIT;
752 SELECT col_15 FROM testdb_wl5522.t1 WHERE col_15 > 11000;
753 # dml
754 ALTER TABLE testdb_wl5522.t1 DROP INDEX idx1;
755 ALTER TABLE testdb_wl5522.t1 DROP INDEX idx6;
756 ALTER TABLE testdb_wl5522.t1 DROP INDEX idx10;
757
758 SELECT
759 col_1 = REPEAT("col1_00001",10),
760 col_2 = REPEAT("col2_00001",10),
761 col_3 = REPEAT("col3_00001",10),
762 col_4 = REPEAT("col4_00001",10),
763 col_5 = REPEAT("col5_00001",10),
764 col_6 = REPEAT("col6_00001",10),
765 col_7 = REPEAT("col7_00001",10),
766 col_8 = REPEAT("col8_00001",10),
767 col_9 = REPEAT("col9_00001",10),
768 col_10 = REPEAT("col10_00001",10),
769 col_11 = REPEAT("col11_00001",10),
770 col_12 = REPEAT("col12_00001",10),
771 col_13 = REPEAT("col13_00001",10),
772 col_14 = REPEAT("col14_00001",10),
773 col_15
774 FROM testdb_wl5522.t1;
775
776 ALTER TABLE testdb_wl5522.t1 ADD INDEX idx1 (col_1);
777 ALTER TABLE testdb_wl5522.t1 ADD INDEX idx6 (col_1(255));
778 ALTER TABLE testdb_wl5522.t1 ADD INDEX idx10 (col_10(255));
779
780
781 SELECT
782 col_1 = REPEAT("col1_00001",10),
783 col_2 = REPEAT("col2_00001",10),
784 col_3 = REPEAT("col3_00001",10),
785 col_4 = REPEAT("col4_00001",10),
786 col_5 = REPEAT("col5_00001",10),
787 col_6 = REPEAT("col6_00001",10),
788 col_7 = REPEAT("col7_00001",10),
789 col_8 = REPEAT("col8_00001",10),
790 col_9 = REPEAT("col9_00001",10),
791 col_10 = REPEAT("col10_00001",10),
792 col_11 = REPEAT("col11_00001",10),
793 col_12 = REPEAT("col12_00001",10),
794 col_13 = REPEAT("col13_00001",10),
795 col_14 = REPEAT("col14_00001",10),
796 col_15
797 FROM testdb_wl5522.t1;
798
799 DROP TABLE testdb_wl5522.t1;
800 SET AUTOCOMMIT = 1;
801
802 # case 12 - tt with trigger / view
803
804 CREATE TABLE testdb_wl5522.t1(col1 bit(1) ,
805 col2 boolean,col3 tinyint , col4 smallint ,
806 col5 mediumint ,col6 int , col7 bigint ,
807 col8 float (14,3) ,col9 double (14,3),
808 col10 VARCHAR(20) CHARACTER SET utf8 ,
809 col11 TEXT CHARACTER SET binary ,
810 col12 ENUM('a','b','c') CHARACTER SET binary,
811 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs ,
812 col14 CHAR(20) , col15 VARBINARY (400),
813 col16 BINARY(40), col17 BLOB (400),
814 col18 int not null primary key,
815 col19 DATE ,col20 DATETIME , col21 TIMESTAMP ,
816 col22 TIME , col23 YEAR ) ENGINE = Innodb;
817
818 # table for trigger action
819 CREATE TABLE testdb_wl5522.trigger_table ( i int ) ENGINE = Innodb;
820 # define trigger
821 CREATE TRIGGER testdb_wl5522.tri AFTER INSERT ON testdb_wl5522.t1
822 FOR EACH ROW INSERT INTO testdb_wl5522.trigger_table VALUES(NEW.col18);
823 # define view
824 CREATE OR REPLACE VIEW testdb_wl5522.VW1 AS SELECT * FROM testdb_wl5522.t1;
825
826 CREATE INDEX idx1 ON testdb_wl5522.t1(col18);
827 CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10));
828 CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12);
829 CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8);
830
831
832 INSERT INTO testdb_wl5522.t1 VALUES (
833 1,1,-128,32767,-8388608,2147483647,-9223372036854775808,92233720368.222,
834 -92233720368.222,'aaa','aaaaaaaaaa','b','bbbbb','ccccc',REPEAT('d',40),
835 REPEAT('d',40),REPEAT('d',40),1,'1000-01-01','3000-12-31 23:59:59.99',
836 '1990-01-01 00:00:01.00','01:59:59.00','1901');
837
838 INSERT INTO testdb_wl5522.t1 VALUES (
839 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
840 NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
841
842
843 --error 1048
844 INSERT INTO testdb_wl5522.t1 VALUES (
845 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
846 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
847
848 --error 1062
849 INSERT INTO testdb_wl5522.t1 VALUES (
850 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
851 NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
852
853 SELECT COUNT(*) FROM testdb_wl5522.t1;
854 SELECT * FROM testdb_wl5522.trigger_table;
855 SELECT COUNT(*) FROM testdb_wl5522.VW1;
856 FLUSH TABLES testdb_wl5522.t1 FOR EXPORT;
857
858 perl;
859 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
860 ib_backup_tablespaces("testdb_wl5522", "t1");
861 EOF
862
863 UNLOCK TABLES;
864
865 # trigger is also dropped when table is dropped
866 DROP TABLE testdb_wl5522.t1;
867
868 CREATE TABLE testdb_wl5522.t1(col1 bit(1) ,
869 col2 boolean,col3 tinyint , col4 smallint ,
870 col5 mediumint ,col6 int , col7 bigint ,
871 col8 float (14,3) ,col9 double (14,3),
872 col10 VARCHAR(20) CHARACTER SET utf8 ,
873 col11 TEXT CHARACTER SET binary ,
874 col12 ENUM('a','b','c') CHARACTER SET binary,
875 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs ,
876 col14 CHAR(20) , col15 VARBINARY (400),
877 col16 BINARY(40), col17 BLOB (400),
878 col18 int not null primary key,
879 col19 DATE ,col20 DATETIME , col21 TIMESTAMP ,
880 col22 TIME , col23 YEAR ) ENGINE = Innodb;
881
882 CREATE INDEX idx1 ON testdb_wl5522.t1(col18);
883 CREATE INDEX prefix_idx ON testdb_wl5522.t1(col14 (10));
884 CREATE UNIQUE INDEX idx2 ON testdb_wl5522.t1(col12);
885 CREATE UNIQUE INDEX idx3 ON testdb_wl5522.t1(col8);
886
887
888 ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE;
889
890
891 perl;
892 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
893 ib_discard_tablespaces("testdb_wl5522", "t1");
894 ib_restore_tablespaces("testdb_wl5522", "t1");
895 EOF
896
897 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
898
899 SELECT COUNT(*) FROM testdb_wl5522.t1;
900 SELECT * FROM testdb_wl5522.trigger_table;
901 SELECT COUNT(*) FROM testdb_wl5522.VW1;
902
903 # trigger table is not updated as trigger got dropped
904 INSERT INTO testdb_wl5522.t1(col18) VALUES (5);
905 # validate data in table not updated
906 SELECT * FROM testdb_wl5522.trigger_table;
907
908 UPDATE testdb_wl5522.t1 SET col18=10 WHERE col18=1;
909
910 # view shows updated data
911 SELECT COUNT(*) FROM testdb_wl5522.VW1;
912
913 SELECT COUNT(*) FROM testdb_wl5522.t1 WHERE col18=10;
914 ALTER TABLE testdb_wl5522.t1 ADD COLUMN col24 varbinary(40) default null;
915 INSERT INTO testdb_wl5522.t1(col18,col24) VALUES (6,REPEAT('a',10));
916 SELECT col24,col18 FROM testdb_wl5522.t1 WHERE col18 in (6,1,10) ORDER BY col18;
917 ALTER TABLE testdb_wl5522.t1 DROP INDEX prefix_idx;
918 SELECT col18,col14 FROM testdb_wl5522.t1 WHERE col14 like '_ccc%';
919 ALTER TABLE testdb_wl5522.t1 ADD INDEX prefix_idx (col24(10));
920 SELECT col18,col24 FROM testdb_wl5522.t1 WHERE col24 like '_a_a%';
921
922 DROP TABLE testdb_wl5522.t1;
923
924 DROP DATABASE testdb_wl5522;
925
926 call mtr.add_suppression("Got error -1 when reading table '.*'");
927 call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*");
928 call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded");
929
930 # cleanup
931 --remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
932 --remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
933 --remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.cfg
934 --remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.ibd
935
936 --echo #
937 --echo # MDEV-27882 Innodb - recognise MySQL-8.0 innodb flags and give a specific error message
938 --echo #
939 --echo #
940
941 CREATE TABLE `t1` (`i` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB;
942 FLUSH TABLES t1 FOR EXPORT;
943
944 # We use the cfg file of ours.
945 perl;
946 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
947 ib_backup_tablespaces("test", "t1");
948 EOF
949
950 UNLOCK TABLES;
951 ALTER TABLE t1 DISCARD TABLESPACE;
952
953 --move_file $MYSQLTEST_VARDIR/tmp/t1.cfg $MYSQLD_DATADIR/test/t1.cfg
954 --copy_file std_data/mysql80/t1.ibd $MYSQLD_DATADIR/test/t1.ibd
955
956 call mtr.add_suppression("InnoDB: unsupported MySQL tablespace");
957 --error ER_UNSUPPORTED_EXTENSION
958 ALTER TABLE t1 IMPORT TABLESPACE;
959
960 DROP TABLE t1;
961 --remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
962
963 --echo # End of 10.3 tests
964
965 --echo #
966 --echo # MDEV-29972 crash after "Unsupported meta-data version number"
967 --echo #
968
969 call mtr.add_suppression("Index for table 't2' is corrupt");
970
971 CREATE TABLE t2 (i INT PRIMARY KEY) ENGINE=InnoDB;
972 ALTER TABLE t2 DISCARD TABLESPACE;
973 --copy_file std_data/mysql80/t2.cfg $MYSQLD_DATADIR/test/t2.cfg
974 --copy_file std_data/mysql80/t2.ibd $MYSQLD_DATADIR/test/t2.ibd
975 --error ER_NOT_SUPPORTED_YET
976 ALTER TABLE t2 IMPORT TABLESPACE;
977 --remove_file $MYSQLD_DATADIR/test/t2.cfg
978 --error ER_NOT_KEYFILE
979 ALTER TABLE t2 IMPORT TABLESPACE;
980 --error ER_TABLESPACE_DISCARDED
981 SELECT * FROM t2;
982 DROP TABLE t2;
983
984 --echo # End of 10.4 tests