Debian Codegraph @

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

raw
1 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
2 SET GLOBAL innodb_stats_persistent = 0;
3 FLUSH TABLES;
4 # Treating compact format as dynamic format after import stmt
5 CREATE TABLE t1
6 (a int AUTO_INCREMENT PRIMARY KEY,
7 b blob,
8 c blob,
9 KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT;
10 BEGIN;
11 INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200));
12 INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200));
13 INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200));
14 INSERT INTO t1 (b, c) values (repeat("de", 200), repeat("fg", 200));
15 INSERT INTO t1 (b, c) values (repeat("ef", 200), repeat("gh", 200));
16 INSERT INTO t1 (b, c) values (repeat("fg", 200), repeat("hi", 200));
17 INSERT INTO t1 (b, c) values (repeat("gh", 200), repeat("ij", 200));
18 INSERT INTO t1 (b, c) values (repeat("hi", 200), repeat("jk", 200));
19 INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200));
20 INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200));
21 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
22 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
23 COMMIT;
24 SELECT COUNT(*) FROM t1;
25 COUNT(*)
26 40
27 FLUSH TABLE t1 FOR EXPORT;
28 # List before copying files
29 db.opt
30 t1.cfg
31 t1.frm
32 t1.ibd
33 backup: t1
34 UNLOCK TABLES;
35 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
36 ALTER TABLE t1 DISCARD TABLESPACE;
37 db.opt
38 t1.frm
39 restore: t1 .ibd and .cfg files
40 ALTER TABLE t1 IMPORT TABLESPACE;
41 ERROR HY000: Internal error: Drop all secondary indexes before importing table test/t1 when .cfg file is missing.
42 ALTER TABLE t1 DROP INDEX b;
43 Warnings:
44 Warning 1814 Tablespace has been discarded for table `t1`
45 ALTER TABLE t1 IMPORT TABLESPACE;
46 SHOW CREATE TABLE t1;
47 Table Create Table
48 t1 CREATE TABLE `t1` (
49 `a` int(11) NOT NULL AUTO_INCREMENT,
50 `b` blob DEFAULT NULL,
51 `c` blob DEFAULT NULL,
52 PRIMARY KEY (`a`)
53 ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
54 # Auto increment value must be more than maximum column value
55 SELECT MAX(a) FROM t1;
56 MAX(a)
57 45
58 SELECT auto_increment FROM information_schema.tables
59 WHERE table_name like 't1';
60 auto_increment
61 46
62 UPDATE t1 set b = repeat("de", 100) where b = repeat("cd", 200);
63 explain SELECT a FROM t1 where b = repeat("de", 100);
64 id select_type table type possible_keys key key_len ref rows Extra
65 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using where
66 SELECT a FROM t1 where b = repeat("de", 100);
67 a
68 3
69 13
70 28
71 38
72 SELECT COUNT(*) FROM t1;
73 COUNT(*)
74 40
75 DELETE FROM t1;
76 InnoDB 0 transactions not purged
77 CHECK TABLE t1;
78 Table Op Msg_type Msg_text
79 test.t1 check status OK
80 DROP TABLE t1;
81 CREATE TABLE t1
82 (c1 int AUTO_INCREMENT PRIMARY KEY,
83 c2 POINT NOT NULL,
84 c3 LINESTRING NOT NULL,
85 SPATIAL INDEX idx1(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
86 INSERT INTO t1(c2,c3) VALUES(
87 ST_GeomFromText('POINT(10 10)'),
88 ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'));
89 INSERT INTO t1(c2,c3) VALUES(
90 ST_GeomFromText('POINT(20 20)'),
91 ST_GeomFromText('LINESTRING(5 15,20 10,30 20)'));
92 INSERT INTO t1(c2,c3) VALUES(
93 ST_GeomFromText('POINT(30 30)'),
94 ST_GeomFromText('LINESTRING(10 5,20 24,30 32)'));
95 INSERT INTO t1(c2,c3) VALUES(
96 ST_GeomFromText('POINT(40 40)'),
97 ST_GeomFromText('LINESTRING(15 5,25 20,35 30)'));
98 INSERT INTO t1(c2,c3) VALUES(
99 ST_GeomFromText('POINT(50 10)'),
100 ST_GeomFromText('LINESTRING(15 15,24 10,31 20)'));
101 INSERT INTO t1(c2,c3) VALUES(
102 ST_GeomFromText('POINT(60 50)'),
103 ST_GeomFromText('LINESTRING(10 15,20 44,35 32)'));
104 BEGIN;
105 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
106 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
107 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
108 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
109 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
110 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
111 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
112 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
113 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
114 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
115 INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1;
116 COMMIT;
117 FLUSH TABLE t1 FOR EXPORT;
118 # List before copying files
119 db.opt
120 t1.cfg
121 t1.frm
122 t1.ibd
123 backup: t1
124 UNLOCK TABLES;
125 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
126 ALTER TABLE t1 DISCARD TABLESPACE;
127 restore: t1 .ibd and .cfg files
128 ALTER TABLE t1 IMPORT TABLESPACE;
129 ERROR HY000: Internal error: Drop all secondary indexes before importing table test/t1 when .cfg file is missing.
130 ALTER TABLE t1 DROP INDEX idx1;
131 Warnings:
132 Warning 1814 Tablespace has been discarded for table `t1`
133 ALTER TABLE t1 IMPORT TABLESPACE;
134 Warnings:
135 Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t1.cfg', will attempt to import without schema verification
136 SHOW CREATE TABLE t1;
137 Table Create Table
138 t1 CREATE TABLE `t1` (
139 `c1` int(11) NOT NULL AUTO_INCREMENT,
140 `c2` point NOT NULL,
141 `c3` linestring NOT NULL,
142 PRIMARY KEY (`c1`)
143 ) ENGINE=InnoDB AUTO_INCREMENT=14325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
144 # Auto increment value must be more than maximum column value
145 SELECT MAX(c1) FROM t1;
146 MAX(c1)
147 14324
148 SELECT auto_increment FROM information_schema.tables
149 WHERE table_name like 't1';
150 auto_increment
151 14325
152 UPDATE t1 SET C2 = ST_GeomFromText('POINT(0 0)');
153 SELECT COUNT(*) FROM t1;
154 COUNT(*)
155 12288
156 DELETE FROM t1;
157 CHECK TABLE t1;
158 Table Op Msg_type Msg_text
159 test.t1 check status OK
160 InnoDB 0 transactions not purged
161 DROP TABLE t1;
162 SET @save_algo = @@GLOBAL.innodb_compression_algorithm;
163 SET GLOBAL innodb_compression_algorithm=2;
164 CREATE TABLE t1(a SERIAL) PAGE_COMPRESSED=1 ENGINE=InnoDB;
165 INSERT INTO t1 VALUES(1);
166 FLUSH TABLE t1 FOR EXPORT;
167 # List before copying files
168 db.opt
169 t1.cfg
170 t1.frm
171 t1.ibd
172 backup: t1
173 UNLOCK TABLES;
174 SET GLOBAL innodb_compression_algorithm=0;
175 ALTER TABLE t1 FORCE;
176 ALTER TABLE t1 DISCARD TABLESPACE;
177 db.opt
178 t1.frm
179 restore: t1 .ibd and .cfg files
180 ALTER TABLE t1 IMPORT TABLESPACE;
181 INSERT INTO t1 VALUES(2);
182 SELECT * FROM t1;
183 a
184 1
185 2
186 SET GLOBAL innodb_compression_algorithm=3;
187 FLUSH TABLE t1 FOR EXPORT;
188 # List before copying files
189 db.opt
190 t1.cfg
191 t1.frm
192 t1.ibd
193 backup: t1
194 UNLOCK TABLES;
195 SET GLOBAL innodb_compression_algorithm=0;
196 ALTER TABLE t1 FORCE;
197 ALTER TABLE t1 DISCARD TABLESPACE;
198 # Display the discarded table name by using SPACE and PAGE_NO
199 # column in INNODB_SYS_INDEXES and discard doesn't affect the
200 # SPACE in INNODB_SYS_TABLES
201 SELECT t.NAME, t.SPACE BETWEEN 1 and 0xFFFFFFEF as SYS_TABLE_SPACE_RANGE
202 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t
203 WHERE t.TABLE_ID IN (
204 SELECT i.TABLE_ID FROM
205 INFORMATION_SCHEMA.INNODB_SYS_INDEXES i WHERE
206 i.PAGE_NO IS NULL and i.SPACE IS NULL);
207 NAME SYS_TABLE_SPACE_RANGE
208 test/t1 1
209 db.opt
210 t1.frm
211 restore: t1 .ibd and .cfg files
212 ALTER TABLE t1 IMPORT TABLESPACE;
213 INSERT INTO t1 VALUES(3);
214 SELECT * FROM t1;
215 a
216 1
217 2
218 3
219 DROP TABLE t1;
220 SET GLOBAL innodb_compression_algorithm=@save_algo;
221 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;