Debian Codegraph @

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

raw
1 #
2 # MDEV-11415 AVOID INTERMEDIATE COMMIT WHILE DOING
3 # ALTER TABLE...ALGORITHM=COPY
4 #
5 CREATE TABLE t(a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB;
6 CREATE TABLE t1(a INT, b TEXT, c TEXT,
7 FULLTEXT(b), FULLTEXT(c(3)), FULLTEXT(b,c)) ENGINE=InnoDB;
8 BEGIN;
9 COMMIT;
10 SELECT COUNT(*) FROM t;
11 COUNT(*)
12 999
13 UPDATE t SET b=a%7, c=a%11, d=a%13;
14 INSERT INTO t1 VALUES(1, 'This is a first b column', 'This is a first c column');
15 INSERT INTO t1 VALUES(2, 'This is a second b column', 'This is a second c column');
16 INSERT INTO t1(a) VALUES(3);
17 INSERT INTO t1 VALUES(4, 'This is a third b column', 'This is a third c column');
18 DELETE FROM t1 WHERE a = 2;
19 SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
20 a b c
21 1 This is a first b column This is a first c column
22 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
23 a b c
24 1 This is a first b column This is a first c column
25 SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
26 a b c
27 1 This is a first b column This is a first c column
28 4 This is a third b column This is a third c column
29 SHOW CREATE TABLE t1;
30 Table Create Table
31 t1 CREATE TABLE `t1` (
32 `a` int(11) DEFAULT NULL,
33 `b` text DEFAULT NULL,
34 `c` text DEFAULT NULL,
35 FULLTEXT KEY `b` (`b`),
36 FULLTEXT KEY `c` (`c`),
37 FULLTEXT KEY `b_2` (`b`,`c`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
39 ALTER TABLE t1 FORCE, ALGORITHM=COPY;
40 connect hang,localhost,root;
41 SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever';
42 # create 32 secondary indexes
43 ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX(b,a,d,c),
44 ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c),
45 ADD INDEX(a,c,b,d),ADD INDEX(a,c,d,b),ADD INDEX(a,d,b,c),ADD INDEX(a,d,c,b),
46 ADD INDEX(c,a,b,d),ADD INDEX(c,a,d,b),ADD INDEX(c,b,a,d),ADD INDEX(c,b,d,a),
47 ADD INDEX(c,d,a,b),ADD INDEX(c,d,b,a),ADD INDEX(d,a,b,c),ADD INDEX(d,a,c,b),
48 ADD INDEX(d,b,a,c),ADD INDEX(d,b,c,a),ADD INDEX(d,c,a,b),ADD INDEX(d,c,b,a),
49 ADD INDEX(a,b,c), ADD INDEX(a,c,b), ADD INDEX(a,c,d), ADD INDEX(a,d,c),
50 ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c),
51 ALGORITHM=COPY;
52 connection default;
53 SET DEBUG_SYNC='now WAIT_FOR hung';
54 # restart: --innodb-force-recovery=3
55 disconnect hang;
56 FTS_INDEX_1.ibd
57 FTS_INDEX_2.ibd
58 FTS_INDEX_3.ibd
59 FTS_INDEX_4.ibd
60 FTS_INDEX_5.ibd
61 FTS_INDEX_6.ibd
62 FTS_INDEX_1.ibd
63 FTS_INDEX_2.ibd
64 FTS_INDEX_3.ibd
65 FTS_INDEX_4.ibd
66 FTS_INDEX_5.ibd
67 FTS_INDEX_6.ibd
68 FTS_INDEX_1.ibd
69 FTS_INDEX_2.ibd
70 FTS_INDEX_3.ibd
71 FTS_INDEX_4.ibd
72 FTS_INDEX_5.ibd
73 FTS_INDEX_6.ibd
74 FTSBEING_DELETED.ibd
75 FTSBEING_DELETED_CACHE.ibd
76 FTSCONFIG.ibd
77 FTSDELETED.ibd
78 FTSDELETED_CACHE.ibd
79 db.opt
80 t.frm
81 t.ibd
82 t1.frm
83 t1.ibd
84 SHOW CREATE TABLE t;
85 Table Create Table
86 t CREATE TABLE `t` (
87 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
88 `b` int(11) DEFAULT NULL,
89 `c` int(11) DEFAULT NULL,
90 `d` int(11) DEFAULT NULL,
91 UNIQUE KEY `a` (`a`)
92 ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
93 SELECT COUNT(*) FROM t;
94 COUNT(*)
95 999
96 CHECK TABLE t;
97 Table Op Msg_type Msg_text
98 test.t check status OK
99 SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
100 a b c
101 1 This is a first b column This is a first c column
102 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
103 a b c
104 1 This is a first b column This is a first c column
105 SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
106 a b c
107 1 This is a first b column This is a first c column
108 4 This is a third b column This is a third c column
109 SHOW CREATE TABLE t1;
110 Table Create Table
111 t1 CREATE TABLE `t1` (
112 `a` int(11) DEFAULT NULL,
113 `b` text DEFAULT NULL,
114 `c` text DEFAULT NULL,
115 FULLTEXT KEY `b` (`b`),
116 FULLTEXT KEY `c` (`c`),
117 FULLTEXT KEY `b_2` (`b`,`c`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
119 CHECK TABLE t1;
120 Table Op Msg_type Msg_text
121 test.t1 check status OK
122 # restart: --innodb-read-only
123 FTS_INDEX_1.ibd
124 FTS_INDEX_2.ibd
125 FTS_INDEX_3.ibd
126 FTS_INDEX_4.ibd
127 FTS_INDEX_5.ibd
128 FTS_INDEX_6.ibd
129 FTS_INDEX_1.ibd
130 FTS_INDEX_2.ibd
131 FTS_INDEX_3.ibd
132 FTS_INDEX_4.ibd
133 FTS_INDEX_5.ibd
134 FTS_INDEX_6.ibd
135 FTS_INDEX_1.ibd
136 FTS_INDEX_2.ibd
137 FTS_INDEX_3.ibd
138 FTS_INDEX_4.ibd
139 FTS_INDEX_5.ibd
140 FTS_INDEX_6.ibd
141 FTSBEING_DELETED.ibd
142 FTSBEING_DELETED_CACHE.ibd
143 FTSCONFIG.ibd
144 FTSDELETED.ibd
145 FTSDELETED_CACHE.ibd
146 db.opt
147 t.frm
148 t.ibd
149 t1.frm
150 t1.ibd
151 SHOW CREATE TABLE t;
152 Table Create Table
153 t CREATE TABLE `t` (
154 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
155 `b` int(11) DEFAULT NULL,
156 `c` int(11) DEFAULT NULL,
157 `d` int(11) DEFAULT NULL,
158 UNIQUE KEY `a` (`a`)
159 ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
160 SELECT COUNT(*) FROM t;
161 COUNT(*)
162 999
163 CHECK TABLE t;
164 Table Op Msg_type Msg_text
165 test.t check status OK
166 SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
167 a b c
168 1 This is a first b column This is a first c column
169 SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
170 a b c
171 1 This is a first b column This is a first c column
172 SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
173 a b c
174 1 This is a first b column This is a first c column
175 4 This is a third b column This is a third c column
176 SHOW CREATE TABLE t1;
177 Table Create Table
178 t1 CREATE TABLE `t1` (
179 `a` int(11) DEFAULT NULL,
180 `b` text DEFAULT NULL,
181 `c` text DEFAULT NULL,
182 FULLTEXT KEY `b` (`b`),
183 FULLTEXT KEY `c` (`c`),
184 FULLTEXT KEY `b_2` (`b`,`c`)
185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
186 CHECK TABLE t1;
187 Table Op Msg_type Msg_text
188 test.t1 check status OK
189 SET GLOBAL innodb_doublewrite=ON;
190 SELECT @@GLOBAL.innodb_doublewrite "OFF expected";
191 OFF expected
192 OFF
193 SET GLOBAL innodb_buf_flush_list_now=ON;
194 # restart
195 FTS_INDEX_1.ibd
196 FTS_INDEX_2.ibd
197 FTS_INDEX_3.ibd
198 FTS_INDEX_4.ibd
199 FTS_INDEX_5.ibd
200 FTS_INDEX_6.ibd
201 FTS_INDEX_1.ibd
202 FTS_INDEX_2.ibd
203 FTS_INDEX_3.ibd
204 FTS_INDEX_4.ibd
205 FTS_INDEX_5.ibd
206 FTS_INDEX_6.ibd
207 FTS_INDEX_1.ibd
208 FTS_INDEX_2.ibd
209 FTS_INDEX_3.ibd
210 FTS_INDEX_4.ibd
211 FTS_INDEX_5.ibd
212 FTS_INDEX_6.ibd
213 FTSBEING_DELETED.ibd
214 FTSBEING_DELETED_CACHE.ibd
215 FTSCONFIG.ibd
216 FTSDELETED.ibd
217 FTSDELETED_CACHE.ibd
218 db.opt
219 t.frm
220 t.ibd
221 t1.frm
222 t1.ibd
223 DROP TABLE t1,t;