-
Notifications
You must be signed in to change notification settings - Fork 473
/
dd_upgrade_compression_dict.result
299 lines (299 loc) · 10.8 KB
/
dd_upgrade_compression_dict.result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
# Set different paths for --datadir
# Copy the remote tablespace & DB zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
# Stop DB server which was created by MTR default
# Start the 8.0 server on 5.7 datadir
# restart: --loose-skip-log-bin --skip-log-slave-updates --datadir=MYSQLD_DATADIR1
# Execute mysql_upgrade
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
mtr.global_suppressions OK
mtr.test_suppressions OK
sys.sys_config OK
test.t1 OK
test.t10 OK
test.t11 OK
test.t2 OK
test.t3 OK
test.t4 OK
test.t5 OK
test.t6 OK
test.t7 OK
test.t8 OK
test.t9 OK
SHOW CREATE TABLE test.t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` text /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd` */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` text /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `d1` */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` text /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `d2` */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t4;
Table Create Table
t4 CREATE TABLE `t4` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` text /*!50633 COLUMN_FORMAT COMPRESSED */,
`c` varchar(1000) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL,
`d` varchar(10000) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT 'hello, world! see you!',
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t5;
Table Create Table
t5 CREATE TABLE `t5` (
`id` bigint(20) unsigned NOT NULL,
`a` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SHOW CREATE TABLE test.t6;
Table Create Table
t6 CREATE TABLE `t6` (
`id` bigint(20) unsigned NOT NULL,
`a` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SHOW CREATE TABLE test.t7;
Table Create Table
t7 CREATE TABLE `t7` (
`id` bigint(20) unsigned NOT NULL,
`a` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 2 */
SHOW CREATE TABLE test.t8;
Table Create Table
t8 CREATE TABLE `t8` (
`id` bigint(20) unsigned NOT NULL,
`a` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
(PARTITION p0 VALUES LESS THAN (1024)
(SUBPARTITION s0 ENGINE = InnoDB,
SUBPARTITION s1 ENGINE = InnoDB),
PARTITION p1 VALUES LESS THAN MAXVALUE
(SUBPARTITION s2 ENGINE = InnoDB,
SUBPARTITION s3 ENGINE = InnoDB)) */
SHOW CREATE TABLE test.t9;
Table Create Table
t9 CREATE TABLE `t9` (
`id` bigint(20) unsigned NOT NULL,
`a` varchar(250) /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */ DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (1024) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SHOW CREATE TABLE test.t10;
Table Create Table
t10 CREATE TABLE `t10` (
`id` int(11) NOT NULL,
`a` blob NOT NULL /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `numbers` */,
`vchar` char(2) GENERATED ALWAYS AS (substr(`a`,2,2)) VIRTUAL,
KEY `vchar` (`vchar`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE test.t11;
Table Create Table
t11 CREATE TABLE `t11` (
`f1` int(11) NOT NULL,
`f2` blob /*!50633 COLUMN_FORMAT COMPRESSED */,
`f3` blob GENERATED ALWAYS AS (`f2`) VIRTUAL,
PRIMARY KEY (`f1`),
KEY `f3` (`f3`(200))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SET @long_dictionary_data = REPEAT('ab', 32506 / 2);
SELECT dict_data = @long_dictionary_data AS long_dictionary_data_matches FROM information_schema.compression_dictionary WHERE dict_name = 'd1';
long_dictionary_data_matches
1
SET @dict_data8 = 'aaaaaaaabbbbbbbbccccccccdddddddd';
SELECT dict_data = @dict_data8 AS short_dictionary_data_matches FROM information_schema.compression_dictionary WHERE dict_name = 'd2';
short_dictionary_data_matches
1
# Retrieve all compression dictionaries
# Long dictionary data is already retrieved and verifed above
SELECT * FROM information_schema.compression_dictionary where dict_name != 'd1';
DICT_VERSION DICT_NAME DICT_DATA
1 d2 aaaaaaaabbbbbbbbccccccccdddddddd
1 dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd abcd
1 numbers onetwothree
# Retrieve all data from tables created in 5.7
SELECT LENGTH(a) FROM t1;
LENGTH(a)
160
SELECT LENGTH(a) FROM t2;
LENGTH(a)
65012
SELECT LENGTH(a) FROM t3;
LENGTH(a)
128
SELECT a, LENGTH(b), LENGTH(c), LENGTH(d) FROM t4;
a LENGTH(b) LENGTH(c) LENGTH(d)
1 1200 80 22
2 1500 40 5500
SELECT id, LENGTH(a) FROM t5;
id LENGTH(a)
1 180
2000 90
SELECT id, LENGTH(a) FROM t6;
id LENGTH(a)
1 240
200 240
300 250
SELECT id, LENGTH(a) FROM t7;
id LENGTH(a)
200 240
300 250
1 240
SELECT id, LENGTH(a) FROM t8;
id LENGTH(a)
200 240
300 250
1 240
SELECT id, LENGTH(a) FROM t9;
id LENGTH(a)
200 240
300 250
1 240
SELECT id, LENGTH(a) FROM t10;
id LENGTH(a)
1 8000
SELECT f1, LENGTH(f2), LENGTH(f3) FROM t11;
f1 LENGTH(f2) LENGTH(f3)
1 96 96
# Do ALTER before restart and check schema
ALTER TABLE t1 ADD COLUMN b BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `d2`;
SET @t1_data = REPEAT('aaaaaaaabbbbbbbbccccccccdddddddd', 40);
INSERT INTO t1 VALUES(@t1_data, @t1_data);
SELECT LENGTH(a), LENGTH(b) FROM t1;
LENGTH(a) LENGTH(b)
160 NULL
1280 1280
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` text /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd` */,
`b` blob /*!50633 COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `d2` */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# restart: --loose-skip-log-bin --skip-log-slave-updates --datadir=MYSQLD_DATADIR1
# Do ALTER before restart and check schema
ALTER TABLE t9 ADD COLUMN b BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY `d2`;
INSERT INTO t9 VALUES(240, @t1_data, @t1_data);
SELECT id, LENGTH(a), LENGTH(b) FROM t9;
id LENGTH(a) LENGTH(b)
200 240 NULL
300 250 NULL
240 NULL NULL
1 240 NULL
# Start the 8.0 server on 5.7 datadir
SET GLOBAL innodb_fast_shutdown = 0;
# restart: --loose-skip-log-bin --skip-log-slave-updates --datadir=MYSQLD_DATADIR1 --innodb-read-only
SET @long_dictionary_data = REPEAT('ab', 32506 / 2);
SELECT dict_data = @long_dictionary_data AS long_dictionary_data_matches FROM information_schema.compression_dictionary WHERE dict_name = 'd1';
long_dictionary_data_matches
1
SET @dict_data8 = 'aaaaaaaabbbbbbbbccccccccdddddddd';
SELECT dict_data = @dict_data8 AS short_dictionary_data_matches FROM information_schema.compression_dictionary WHERE dict_name = 'd2';
short_dictionary_data_matches
1
# Retrieve all compression dictionaries
# Long dictionary data is already retrieved and verifed above
SELECT * FROM information_schema.compression_dictionary where dict_name != 'd1';
DICT_VERSION DICT_NAME DICT_DATA
1 d2 aaaaaaaabbbbbbbbccccccccdddddddd
1 dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd abcd
1 numbers onetwothree
# Retrieve all data from tables created in 5.7
SELECT LENGTH(a) FROM t1;
LENGTH(a)
160
1280
SELECT LENGTH(a) FROM t2;
LENGTH(a)
65012
SELECT LENGTH(a) FROM t3;
LENGTH(a)
128
SELECT a, LENGTH(b), LENGTH(c), LENGTH(d) FROM t4;
a LENGTH(b) LENGTH(c) LENGTH(d)
1 1200 80 22
2 1500 40 5500
SELECT id, LENGTH(a) FROM t5;
id LENGTH(a)
1 180
2000 90
SELECT id, LENGTH(a) FROM t6;
id LENGTH(a)
1 240
200 240
300 250
SELECT id, LENGTH(a) FROM t7;
id LENGTH(a)
200 240
300 250
1 240
SELECT id, LENGTH(a) FROM t8;
id LENGTH(a)
200 240
300 250
1 240
SELECT id, LENGTH(a) FROM t9;
id LENGTH(a)
200 240
300 250
240 NULL
1 240
SELECT id, LENGTH(a) FROM t10;
id LENGTH(a)
1 8000
SELECT f1, LENGTH(f2), LENGTH(f3) FROM t11;
f1 LENGTH(f2) LENGTH(f3)
1 96 96
# Stop the server
# Remove copied files
# Restart the server with default options.
# restart