Debian Codegraph @

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

raw
1 # Test "ALTER TABLE ... IMPORT TABLESPACE" in InnoDB
2
3 --echo #
4 --echo # Bug#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES
5 --echo #
6
7 CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32),
8 PRIMARY KEY (c1, c2, c3))
9 ENGINE=InnoDB;
10
11 ALTER TABLE t1 ADD INDEX ind1(c1(5), c2, c3);
12 ALTER TABLE t1 ADD INDEX ind2(c3, c1(10), c2);
13 ALTER TABLE t1 ADD INDEX ind3(c2, c3, c1(20));
14
15 INSERT INTO t1 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3');
16
17 --echo # Test with 2ndary index having prefix
18 --source suite/innodb/include/import.inc
19
20 --echo # Test with PK & 2ndary index with prefix
21 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1(5), c2(10), c3(20));
22 --source suite/innodb/include/import.inc
23 DROP TABLE t1;
24
25 --echo #
26 --echo # Bug#30561144 IMPORT TABLESPACE FAILS: ERROR 1808 (HY000): SCHEMA MISMATCH
27 --echo #
28 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
29 ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
30
31 --echo # Insert some data into t1
32 --disable_query_log
33 let $i=1000;
34 while ($i)
35 {
36 INSERT INTO t1(v) VALUES ("aaaaaaaaaaaaa");
37 dec $i;
38 }
39 --enable_query_log
40
41 SELECT * from t1 limit 10;
42 SELECT COUNT(*) from t1;
43
44 --echo # Flush the table and store CFG/IBD files to temp
45 FLUSH TABLE t1 FOR EXPORT;
46 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/t1.cfg_back
47 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back
48 UNLOCK TABLES;
49
50 --echo #
51 --echo # 1) Both tables contain INSTANT column, with matching default values.
52 --echo #
53 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
54 ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
55 ALTER TABLE t2 DISCARD TABLESPACE;
56
57 --echo # Copy CFG/IBD file from temp
58 --copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t2.cfg
59 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd
60
61 --echo # IMPORT should succeed now
62 ALTER TABLE t2 IMPORT TABLESPACE;
63 SELECT * from t2 limit 10;
64 DROP TABLE t2;
65
66 --echo #
67 --echo # 2) Both tables contain INSTANT column, with conflicting default values.
68 --echo #
69 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
70 ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "different_value", ALGORITHM=INSTANT;
71 ALTER TABLE t2 DISCARD TABLESPACE;
72
73 --echo # Copy CFG/IBD file from temp
74 --copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t2.cfg
75 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd
76
77 --echo # IMPORT should fail because of the conflicting values
78 --error 1808
79 ALTER TABLE t2 IMPORT TABLESPACE;
80 --echo # Manually remove tablespace files, since import failed drop won't delete them.
81 --remove_file $MYSQLD_DATADIR/test/t2.cfg
82 --remove_file $MYSQLD_DATADIR/test/t2.ibd
83 --echo # Cleanup
84 DROP TABLE t2;
85 DROP TABLE t1;
86 --remove_file $MYSQLD_DATADIR/t1.cfg_back
87 --remove_file $MYSQLD_DATADIR/t1.ibd_back
88
89 --echo #
90 --echo # Bug #30830441 ALTER TABLE IMPORT TABLESPACE FAILS AFTER A SERVER RESTART
91 --echo #
92 CREATE TABLE t1 ( id int );
93 INSERT INTO t1 values (1),(2),(3);
94 FLUSH TABLES t1 FOR EXPORT;
95 UNLOCK TABLES;
96
97 --source include/shutdown_mysqld.inc
98 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back
99 --source include/start_mysqld.inc
100
101 ALTER TABLE t1 DISCARD TABLESPACE;
102
103 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t1.ibd
104
105 --disable_warnings
106 ALTER TABLE t1 IMPORT TABLESPACE;
107 --enable_warnings
108
109 DROP TABLE t1;
110 --remove_file $MYSQLD_DATADIR/t1.ibd_back