Debian Codegraph @

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

raw
1 #
2 # This test upgrades a 5.7 database with tables having "FTS"
3 # in their names to 8.0
4 #
5 # Stop the running server
6 ##############################################################
7 # Copy and unzip the datadir created with lctn=0
8 ##############################################################
9 # Restart the server with the unzipped datadir
10 # restart: --datadir=DATADIR --innodb_page_size=16k
11 # Check the contents of INFORMATION_SCHEMA
12 SHOW TABLES;
13 Tables_in_test
14 FTS_0000000000000001
15 FTS_table
16 tFTS1
17 t_FTS_1
18 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES
19 WHERE name LIKE '%test%'
20 ORDER BY name;
21 lower(name)
22 test/fts_0000000000000001
23 test/fts_0000000000000427_000000000000002d_index_1
24 test/fts_0000000000000427_000000000000002d_index_2
25 test/fts_0000000000000427_000000000000002d_index_3
26 test/fts_0000000000000427_000000000000002d_index_4
27 test/fts_0000000000000427_000000000000002d_index_5
28 test/fts_0000000000000427_000000000000002d_index_6
29 test/fts_0000000000000427_being_deleted
30 test/fts_0000000000000427_being_deleted_cache
31 test/fts_0000000000000427_config
32 test/fts_0000000000000427_deleted
33 test/fts_0000000000000427_deleted_cache
34 test/fts_0000000000000434_000000000000003c_index_1
35 test/fts_0000000000000434_000000000000003c_index_2
36 test/fts_0000000000000434_000000000000003c_index_3
37 test/fts_0000000000000434_000000000000003c_index_4
38 test/fts_0000000000000434_000000000000003c_index_5
39 test/fts_0000000000000434_000000000000003c_index_6
40 test/fts_0000000000000434_being_deleted
41 test/fts_0000000000000434_being_deleted_cache
42 test/fts_0000000000000434_config
43 test/fts_0000000000000434_deleted
44 test/fts_0000000000000434_deleted_cache
45 test/fts_table
46 test/tfts1
47 test/t_fts_1
48 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
49 WHERE name LIKE '%test%'
50 ORDER BY name;
51 lower(name)
52 test/fts_0000000000000001
53 test/fts_0000000000000427_000000000000002d_index_1
54 test/fts_0000000000000427_000000000000002d_index_2
55 test/fts_0000000000000427_000000000000002d_index_3
56 test/fts_0000000000000427_000000000000002d_index_4
57 test/fts_0000000000000427_000000000000002d_index_5
58 test/fts_0000000000000427_000000000000002d_index_6
59 test/fts_0000000000000427_being_deleted
60 test/fts_0000000000000427_being_deleted_cache
61 test/fts_0000000000000427_config
62 test/fts_0000000000000427_deleted
63 test/fts_0000000000000427_deleted_cache
64 test/fts_0000000000000434_000000000000003c_index_1
65 test/fts_0000000000000434_000000000000003c_index_2
66 test/fts_0000000000000434_000000000000003c_index_3
67 test/fts_0000000000000434_000000000000003c_index_4
68 test/fts_0000000000000434_000000000000003c_index_5
69 test/fts_0000000000000434_000000000000003c_index_6
70 test/fts_0000000000000434_being_deleted
71 test/fts_0000000000000434_being_deleted_cache
72 test/fts_0000000000000434_config
73 test/fts_0000000000000434_deleted
74 test/fts_0000000000000434_deleted_cache
75 test/fts_table
76 test/tfts1
77 test/t_fts_1
78 # Cleanup
79 # Shutdown server
80 # Remove copied files
81 ##############################################################
82 # Copy and unzip the datadir created with lctn=1
83 ##############################################################
84 # Restart the server with the unzipped datadir
85 # restart: --datadir=DATADIR --innodb_page_size=16k
86 # Check the contents of INFORMATION_SCHEMA
87 SHOW TABLES;
88 Tables_in_test
89 fts_0000000000000001
90 fts_table
91 t_fts_1
92 tfts1
93 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES
94 WHERE name LIKE '%test%'
95 ORDER BY name;
96 lower(name)
97 test/fts_0000000000000001
98 test/fts_0000000000000427_000000000000002d_index_1
99 test/fts_0000000000000427_000000000000002d_index_2
100 test/fts_0000000000000427_000000000000002d_index_3
101 test/fts_0000000000000427_000000000000002d_index_4
102 test/fts_0000000000000427_000000000000002d_index_5
103 test/fts_0000000000000427_000000000000002d_index_6
104 test/fts_0000000000000427_being_deleted
105 test/fts_0000000000000427_being_deleted_cache
106 test/fts_0000000000000427_config
107 test/fts_0000000000000427_deleted
108 test/fts_0000000000000427_deleted_cache
109 test/fts_0000000000000434_000000000000003c_index_1
110 test/fts_0000000000000434_000000000000003c_index_2
111 test/fts_0000000000000434_000000000000003c_index_3
112 test/fts_0000000000000434_000000000000003c_index_4
113 test/fts_0000000000000434_000000000000003c_index_5
114 test/fts_0000000000000434_000000000000003c_index_6
115 test/fts_0000000000000434_being_deleted
116 test/fts_0000000000000434_being_deleted_cache
117 test/fts_0000000000000434_config
118 test/fts_0000000000000434_deleted
119 test/fts_0000000000000434_deleted_cache
120 test/fts_table
121 test/tfts1
122 test/t_fts_1
123 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
124 WHERE name LIKE '%test%'
125 ORDER BY name;
126 lower(name)
127 test/fts_0000000000000001
128 test/fts_0000000000000427_000000000000002d_index_1
129 test/fts_0000000000000427_000000000000002d_index_2
130 test/fts_0000000000000427_000000000000002d_index_3
131 test/fts_0000000000000427_000000000000002d_index_4
132 test/fts_0000000000000427_000000000000002d_index_5
133 test/fts_0000000000000427_000000000000002d_index_6
134 test/fts_0000000000000427_being_deleted
135 test/fts_0000000000000427_being_deleted_cache
136 test/fts_0000000000000427_config
137 test/fts_0000000000000427_deleted
138 test/fts_0000000000000427_deleted_cache
139 test/fts_0000000000000434_000000000000003c_index_1
140 test/fts_0000000000000434_000000000000003c_index_2
141 test/fts_0000000000000434_000000000000003c_index_3
142 test/fts_0000000000000434_000000000000003c_index_4
143 test/fts_0000000000000434_000000000000003c_index_5
144 test/fts_0000000000000434_000000000000003c_index_6
145 test/fts_0000000000000434_being_deleted
146 test/fts_0000000000000434_being_deleted_cache
147 test/fts_0000000000000434_config
148 test/fts_0000000000000434_deleted
149 test/fts_0000000000000434_deleted_cache
150 test/fts_table
151 test/tfts1
152 test/t_fts_1
153 # Cleanup
154 # Shutdown server
155 # Remove copied files
156 # Restart the server
157 # restart
158 #
159 # Bug#30357954 UPGRADE TO 8.0 FAILS WHEN FULLTEXT INDEX HAS BEEN DROPPED
160 #
161 # This test upgrades a 5.7 database with table having "FTS_DOC_ID"
162 # column & "FTS_DOC_ID_INDEX" index to 8.0. (And not having any
163 # FULLTEXT index)
164 #
165 # Stop the running server
166 #
167 # Copy and unzip the datadir
168 #
169 # Restart the server with the unzipped datadir
170 # restart: --datadir=DATADIR --innodb_page_size=16k
171 # Check the contents of INFORMATION_SCHEMA
172 SHOW TABLES;
173 Tables_in_test
174 t1
175 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES
176 WHERE name LIKE '%test%'
177 ORDER BY name;
178 lower(name)
179 test/fts_0000000000000424_being_deleted
180 test/fts_0000000000000424_being_deleted_cache
181 test/fts_0000000000000424_config
182 test/fts_0000000000000424_deleted
183 test/fts_0000000000000424_deleted_cache
184 test/t1
185 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
186 WHERE name LIKE '%test%'
187 ORDER BY name;
188 lower(name)
189 test/fts_0000000000000424_being_deleted
190 test/fts_0000000000000424_being_deleted_cache
191 test/fts_0000000000000424_config
192 test/fts_0000000000000424_deleted
193 test/fts_0000000000000424_deleted_cache
194 test/t1
195 # Check DDL & DML opearations are working
196 SELECT * FROM t1;
197 id val
198 1 Upgrade mysql database to 8.0 version
199 SET DEBUG='+d,skip_dd_table_access_check';
200 SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden
201 FROM mysql.columns a, mysql.tables b
202 WHERE a.table_id = b.id AND b.name = 't1'
203 ORDER BY a.ordinal_position;
204 table_name column_name ordinal_position hidden
205 t1 id 1 Visible
206 t1 val 2 Visible
207 t1 DB_TRX_ID 3 SE
208 t1 DB_ROLL_PTR 4 SE
209 t1 FTS_DOC_ID 5 SE
210 ALTER TABLE t1 ADD FULLTEXT INDEX val_ft_idx(val);
211 SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden
212 FROM mysql.columns a, mysql.tables b
213 WHERE a.table_id = b.id AND b.name = 't1'
214 ORDER BY a.ordinal_position;
215 table_name column_name ordinal_position hidden
216 t1 id 1 Visible
217 t1 val 2 Visible
218 t1 FTS_DOC_ID 3 SE
219 t1 DB_TRX_ID 4 SE
220 t1 DB_ROLL_PTR 5 SE
221 INSERT INTO t1 (val) VALUES ('Upgraded successfully to 8.0 version');
222 SELECT * FROM t1;
223 id val
224 1 Upgrade mysql database to 8.0 version
225 2 Upgraded successfully to 8.0 version
226 SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden
227 FROM mysql.columns a, mysql.tables b
228 WHERE a.table_id = b.id AND b.name = 't1'
229 ORDER BY a.ordinal_position;
230 table_name column_name ordinal_position hidden
231 t1 id 1 Visible
232 t1 val 2 Visible
233 t1 FTS_DOC_ID 3 SE
234 t1 DB_TRX_ID 4 SE
235 t1 DB_ROLL_PTR 5 SE
236 ALTER TABLE t1 DROP INDEX val_ft_idx;
237 SELECT b.name table_name, a.name column_name, a.ordinal_position, a.hidden
238 FROM mysql.columns a, mysql.tables b
239 WHERE a.table_id = b.id AND b.name = 't1'
240 ORDER BY a.ordinal_position;
241 table_name column_name ordinal_position hidden
242 t1 id 1 Visible
243 t1 val 2 Visible
244 t1 DB_TRX_ID 3 SE
245 t1 DB_ROLL_PTR 4 SE
246 t1 FTS_DOC_ID 5 SE
247 SET DEBUG='-d,skip_dd_table_access_check';
248 DELETE FROM t1 WHERE val LIKE 'Upgrade mysql database to 8.0 version';
249 SELECT * FROM t1;
250 id val
251 2 Upgraded successfully to 8.0 version
252 DROP TABLE t1;
253 # Cleanup
254 # Shutdown server
255 # Remove copied files
256 # Restart the server
257 # restart
258 #
259 # Bug #31749490 OLD FTS FILES AFTER UPGRADE: ASSERTION FAILURE:
260 # PARS0PARS.CC:736:SYM_NODE->TABLE != NULLPTR
261 #
262 # Stop the running server
263 #
264 # Copy and unzip the datadir
265 #
266 # Restart the server with the unzipped datadir
267 # restart: --datadir=DATADIR --innodb_page_size=16k
268 # Check the contents of INFORMATION_SCHEMA
269 SHOW TABLES;
270 Tables_in_test
271 t1
272 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLES
273 WHERE name LIKE '%test%'
274 ORDER BY name;
275 lower(name)
276 test/fts_0000000000000424_being_deleted
277 test/fts_0000000000000424_being_deleted_cache
278 test/fts_0000000000000424_config
279 test/fts_0000000000000424_deleted
280 test/fts_0000000000000424_deleted_cache
281 test/t1
282 SELECT lower(name) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
283 WHERE name LIKE '%test%'
284 ORDER BY name;
285 lower(name)
286 test/fts_0000000000000424_being_deleted
287 test/fts_0000000000000424_being_deleted_cache
288 test/fts_0000000000000424_config
289 test/fts_0000000000000424_deleted
290 test/fts_0000000000000424_deleted_cache
291 test/t1
292 INSERT INTO t1 (val) VALUES ('Upgraded successfully to 8.0 version');
293 SELECT * FROM t1;
294 id val
295 1 Upgrade mysql database to 8.0 version
296 2 Upgraded successfully to 8.0 version
297 DELETE FROM t1 WHERE val LIKE 'Upgrade mysql database to 8.0 version';
298 SELECT * FROM t1;
299 id val
300 2 Upgraded successfully to 8.0 version
301 DROP TABLE t1;
302 # Cleanup
303 # Shutdown server
304 # Remove copied files
305 # Restart the server
306 # restart