Debian Codegraph @

mysql-test/suite/binlog/t/binlog_table_map_optional_metadata.test

raw
1 ################################################################################
2 # WL#4618 RBR: extended table metadata in the binary log
3 #
4 # Below metadata is logged into Table_map_log_event
5 # - signedness of numeric columns
6 # - charsets of character columns
7 # - column names
8 # - set/enum character sets and string values
9 # - primary key
10 #
11 # The first two are always logged. The others are controlled by system
12 # variable --binlog-row-metadata
13 #
14 # The test will verify if the metadata can be logged and printed by mysqlbinlog
15 # correctly.
16 # mysqlbinlog --print-table-metadata will print the extra metadata
17 ################################################################################
18 --source include/have_debug.inc
19 --source include/have_binlog_format_row.inc
20
21 #
22 # This test case makes mysqlbinlog stop on specific event file
23 # position. With compression on, compressed events have the same
24 # file position of the enclosing event, thence, this is not possible.
25 #
26 # Skipping the test when compression is on.
27 --source include/not_binlog_transaction_compression_on.inc
28
29
30 RESET MASTER;
31
32 --let $MYSQLD_DATADIR= `select @@datadir`
33 --let $binlog_file= $MYSQLD_DATADIR/binlog.000001
34
35 --echo #
36 --echo # Temporal types can be printed correctly
37 --echo #
38 CREATE TABLE t1(c_year YEAR, c_date DATE, c_time TIME, c_time_f TIME(3),
39 c_datetime DATETIME, c_datetime_f DATETIME(3),
40 c_timestamp TIMESTAMP, c_timestamp_f TIMESTAMP(3) DEFAULT "2017-1-1 10:10:10");
41
42 INSERT INTO t1(c_year) VALUES(2017);
43 --source extra/binlog_tests/print_optional_metadata.inc
44
45 DROP TABLE t1;
46 RESET MASTER;
47
48 --echo #
49 --echo # Json types can be printed correctly
50 --echo #
51 CREATE TABLE t1 (c_json JSON, c_char CHAR(100));
52 INSERT INTO t1(c_char) VALUES("abc");
53 --source extra/binlog_tests/print_optional_metadata.inc
54
55 DROP TABLE t1;
56 RESET MASTER;
57
58 --echo #
59 --echo # Geometry types can be printed correctly
60 --echo #
61 CREATE TABLE t1 (c_geo GEOMETRY, c_point POINT, c_linestring LINESTRING,
62 c_polygon POLYGON, c_multi_point MULTIPOINT,
63 c_multi_linestring MULTILINESTRING, c_multi_polygon MULTIPOLYGON,
64 c_geometrycollection GEOMETRYCOLLECTION, c_char CHAR(100));
65
66 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
67 --source extra/binlog_tests/print_optional_metadata.inc
68
69 RESET MASTER;
70 SET GLOBAL binlog_row_metadata = FULL;
71
72 # geometry type is binlogged, the real geometry types are printed
73 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
74 --source extra/binlog_tests/print_optional_metadata.inc
75
76 DROP TABLE t1;
77 RESET MASTER;
78
79 --echo #
80 --echo # Numeric types can be printed correctly
81 --echo #
82 CREATE TABLE t1(c_bit BIT(10), c_bool BOOL, c_smallint SMALLINT,
83 c_mediumint MEDIUMINT, c_int INT UNSIGNED, c_bigint BIGINT,
84 c_float FLOAT UNSIGNED, c_double DOUBLE, c_decimal DECIMAL(10, 2));
85
86 SET GLOBAL binlog_row_metadata = MINIMAL;
87 INSERT INTO t1(c_bool) VALUES(1);
88
89 --echo # UNSIGNED flag should be printed
90 --source extra/binlog_tests/print_optional_metadata.inc
91
92 RESET MASTER;
93 SET GLOBAL binlog_row_metadata = FULL;
94 INSERT INTO t1(c_bool) VALUES(1);
95
96 --source extra/binlog_tests/print_optional_metadata.inc
97
98 DROP TABLE t1;
99 RESET MASTER;
100
101 --echo #
102 --echo # Character types can be printed correctly
103 --echo #
104 CREATE TABLE t1(c_char CHAR(10), c_varchar VARCHAR(500),
105 c_tinytext TINYTEXT, c_text TEXT,
106 c_mediumtext MEDIUMTEXT, c_longtext LONGTEXT CHARSET utf8);
107
108 SET GLOBAL binlog_row_metadata = MINIMAL;
109 INSERT INTO t1(c_char) VALUES("1");
110
111 # Charset set is printed with default charset
112 --source extra/binlog_tests/print_optional_metadata.inc
113
114 RESET MASTER;
115 SET GLOBAL binlog_row_metadata = FULL;
116 INSERT INTO t1(c_char) VALUES("1");
117
118 --source extra/binlog_tests/print_optional_metadata.inc
119
120 DROP TABLE t1;
121 RESET MASTER;
122
123 --echo #
124 --echo # Column names with non-ascii characters and escape characters can be printed correctly
125 --echo #
126 CREATE TABLE t1(`åäö表\a'``"` INT);
127
128 SHOW CREATE TABLE t1;
129
130 INSERT INTO t1 VALUES(1);
131
132 --source extra/binlog_tests/print_optional_metadata.inc
133 DROP TABLE t1;
134 RESET MASTER;
135
136 --echo #
137 --echo # Charsets can be printed correctly
138 --echo #
139 CREATE TABLE t1(c_char_utf8 CHAR(10) CHARSET utf8,
140 c_varchar_utf8 VARCHAR(10) CHARSET utf8,
141 c_text_utf8 TEXT CHARSET utf8);
142
143 INSERT INTO t1 VALUES("1", "2", "3");
144
145 # Charset set is printed with Default charset
146 --source extra/binlog_tests/print_optional_metadata.inc
147
148 DROP TABLE t1;
149 RESET MASTER;
150
151 # Test collation number less than 250 and collation number greater than 250
152 CREATE TABLE t1(c_utf8mb4_520 CHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
153 c_utf8mb4_0900 VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_pl_0900_as_cs,
154 c_utf8mb4_def TEXT CHARSET utf8mb4);
155
156 INSERT INTO t1 VALUES("1", "2", "3");
157
158 # Charset set is printed without default charset
159 --source extra/binlog_tests/print_optional_metadata.inc
160
161 DROP TABLE t1;
162 RESET MASTER;
163
164 --echo #
165 --echo # Blob and binary columns can be printed correctly
166 --echo #
167 CREATE TABLE t1(c_binary BINARY(10), c_varbinary VARBINARY(10),
168 c_tinyblob TINYBLOB, c_blob BLOB,
169 c_mediumblob MEDIUMBLOB, c_longblob LONGBLOB);
170
171 INSERT INTO t1 VALUES("1", "2", "3", "4", "5", "6");
172 --source extra/binlog_tests/print_optional_metadata.inc
173
174 DROP TABLE t1;
175 RESET MASTER;
176
177 --echo #
178 --echo # Verify that SET string values and character sets can be printed correctly
179 --echo #
180
181 CREATE TABLE t1(
182 c_set_1 SET("set1_v1_å", "set1_v2_ä", "set1_v3_ö"),
183 c_set_2 SET("set2_v1_å", "set2_v2_ä", "set2_v3_ö") CHARACTER SET binary,
184 c_set_3 SET("set3_v1_å", "set3_v2_ä", "set3_v3_ö") CHARACTER SET latin1,
185 c_set_4 SET("set4_v1_å", "set4_v2_ä", "set4_v3_ö") CHARACTER SET swe7 COLLATE swe7_bin,
186 c_set_5 SET("set5_v1_å", "set5_v2_ä", "set5_v3_ö") CHARACTER SET ucs2,
187 c_set_6 SET("set6_v1_å", "set6_v2_ä", "set6_v3_ö") CHARACTER SET utf32
188 );
189
190 SET GLOBAL binlog_row_metadata = MINIMAL;
191 INSERT INTO t1 VALUES("set1_v1_å", "set2_v2_ä", "set3_v3_ö", "set4_v1_å", "set5_v2_ä", "set6_v3_ö");
192 --source extra/binlog_tests/print_optional_metadata.inc
193
194 RESET MASTER;
195 SET GLOBAL binlog_row_metadata = FULL;
196 INSERT INTO t1 VALUES("set1_v1_å", "set2_v2_ä", "set3_v3_ö", "set4_v1_å", "set5_v2_ä", "set6_v3_ö");
197 --source extra/binlog_tests/print_optional_metadata.inc
198
199 DROP TABLE t1;
200 RESET MASTER;
201
202 --echo #
203 --echo # Verify that ENUM string values and character sets can be printed correctly
204 --echo #
205
206 CREATE TABLE t1(
207 c_enum_1 ENUM("enum1_v1_å", "enum1_v2_ä", "enum1_v3_ö"),
208 c_enum_2 ENUM("enum2_v1_å", "enum2_v2_ä", "enum2_v3_ö") CHARACTER SET binary,
209 c_enum_3 ENUM("enum3_v1_å", "enum3_v2_ä", "enum3_v3_ö") CHARACTER SET latin1,
210 c_enum_4 ENUM("enum4_v1_å", "enum4_v2_ä", "enum4_v3_ö") CHARACTER SET swe7 COLLATE swe7_bin,
211 c_enum_5 ENUM("enum5_v1_å", "enum5_v2_ä", "enum5_v3_ö") CHARACTER SET ucs2,
212 c_enum_6 ENUM("enum6_v1_å", "enum6_v2_ä", "enum6_v3_ö") CHARACTER SET utf32
213 );
214
215 SET GLOBAL binlog_row_metadata = MINIMAL;
216 INSERT INTO t1 VALUES("enum1_v1_å", "enum2_v2_ä", "enum3_v3_ö", "enum4_v1_å", "enum5_v2_ä", "enum6_v3_ö");
217 --source extra/binlog_tests/print_optional_metadata.inc
218
219 RESET MASTER;
220 SET GLOBAL binlog_row_metadata = FULL;
221 INSERT INTO t1 VALUES("enum1_v1_å", "enum2_v2_ä", "enum3_v3_ö", "enum4_v1_å", "enum5_v2_ä", "enum6_v3_ö");
222 --source extra/binlog_tests/print_optional_metadata.inc
223
224 DROP TABLE t1;
225 RESET MASTER;
226
227 --echo #
228 --echo # Verify that explicit NOT NULL can be printed correctly
229 --echo #
230 CREATE TABLE t1(c_not_null1 INT NOT NULL, c_null1 INT, c_not_null2 INT NOT NULL,
231 c_null2 INT);
232
233 INSERT INTO t1 VALUES(1, 2, 3, 4);
234 --source extra/binlog_tests/print_optional_metadata.inc
235
236 DROP TABLE t1;
237 RESET MASTER;
238
239 --echo #
240 --echo # Verify that primary key can be printed correctly
241 --echo #
242 CREATE TABLE t1(c_key1 INT, c_key3 INT, c_not_key INT, c_key2 INT,
243 PRIMARY KEY(c_key1, c_key2, c_key3));
244
245 INSERT INTO t1 VALUES(1, 2, 3, 4);
246 --let $print_primary_key= 1
247 --source extra/binlog_tests/print_optional_metadata.inc
248
249 DROP TABLE t1;
250 RESET MASTER;
251
252 # Key has prefix
253 CREATE TABLE t1(c_key1 CHAR(100), c_key3 CHAR(100), c_not_key INT, c_key2 CHAR(10),
254 PRIMARY KEY(c_key1(5), c_key2, c_key3(10)));
255
256 INSERT INTO t1 VALUES("1", "2", 3, "4");
257 --source extra/binlog_tests/print_optional_metadata.inc
258
259 RESET MASTER;
260 # Primary key should not be printed
261 SET GLOBAL binlog_row_metadata = MINIMAL;
262
263 INSERT INTO t1 VALUES("2", "2", 3, "4");
264 --source extra/binlog_tests/print_optional_metadata.inc
265
266 RESET MASTER;
267 --echo #
268 --echo # Coverage test: Print column index instead of column name if column name
269 --echo # is not binlogged.
270 --echo #
271 SET GLOBAL binlog_row_metadata = FULL;
272
273 SET SESSION debug = 'd, dont_log_column_name';
274 INSERT INTO t1 VALUES("3", "2", 3, "4");
275 --source extra/binlog_tests/print_optional_metadata.inc
276
277 --let $print_primary_key=
278 DROP TABLE t1;
279 RESET MASTER;
280
281 --echo #
282 --echo # Coverage test: Inject an invalid column type
283 --echo #
284 CREATE TABLE t1(c1 int, c2 BLOB);
285
286 SET SESSION debug = 'd,inject_invalid_column_type';
287 INSERT INTO t1 VALUES(1, "a");
288 # It prints an error
289 --source extra/binlog_tests/print_optional_metadata.inc
290
291 RESET MASTER;
292
293 --echo #
294 --echo # Coverage test: Inject an invalid BLOB metadata
295 --echo #
296 --let $start_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
297
298 SET SESSION debug = 'd,inject_invalid_blob_size';
299 INSERT INTO t1 VALUES(2, "b");
300
301 # The invalid metadata will case assertion failure on Write_rows_log_event
302 # So we need to stop mysqlbinlog before reading Write_rows_log_event.
303 --let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 3, End_log_pos, 3)
304 --source extra/binlog_tests/print_optional_metadata.inc
305
306 --echo #
307 --echo # Coverage test: Inject an invalid Geometry type
308 --echo #
309 DROP TABLE t1;
310 CREATE TABLE t1(c_geometry GEOMETRY, c_point POINT, c_multilinestring MULTILINESTRING);
311 RESET MASTER;
312 --let $start_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
313
314 SET SESSION debug = 'd,inject_invalid_geometry_type';
315 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
316
317 # The invalid metadata will case assertion failure on Write_rows_log_event
318 # So we need to stop mysqlbinlog before reading Write_rows_log_event.
319 --let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 3, End_log_pos, 3)
320 --source extra/binlog_tests/print_optional_metadata.inc
321
322 DROP TABLE t1;
323 RESET MASTER;
324 --echo #
325 --echo # Comptibility Test: Verify mysqlbinlog can print OLD table_map_log_event
326 --echo # without any optional metadata
327 --echo #
328 CREATE TABLE t1(c_int INT, c_tiny_int_unsigned TINYINT UNSIGNED,
329 c_binary BINARY(10), c_text TEXT, c_point POINT);
330
331 SET session debug='d,simulate_no_optional_metadata';
332 INSERT INTO t1(c_int) VALUES(1);
333 # TINYINT will be printed without UNSIGNED flag,
334 # CHAR will be printed as BINARY(10)
335 # POINT will be printed as GEOMETRY
336 --let $stop_position=
337 --source extra/binlog_tests/print_optional_metadata.inc
338
339 DROP TABLE t1;
340 RESET MASTER;
341 --echo #
342 --echo # Simulate error on initializing charset and primary key metadata
343 --echo #
344 CREATE TABLE t1(c1 char(10) PRIMARY KEY);
345
346 SET session debug='d,simulate_init_charset_field_error';
347 INSERT INTO t1 VALUES("a");
348
349 SET GLOBAL binlog_row_metadata = FULL;
350 SET session debug='d,simulate_init_primary_key_field_error';
351 INSERT INTO t1 VALUES("b");
352
353 --let $print_primary_key= 1
354 --source extra/binlog_tests/print_optional_metadata.inc
355
356 SET SESSION debug = '';
357 SET GLOBAL binlog_row_metadata = MINIMAL;
358 DROP TABLE t1;
359 RESET MASTER;
360
361 --echo #
362 --echo # WL#10905 - Support for INVISIBLE columns.
363 --echo #
364 --echo # Test case to verify that INVISIBLE clause can be printed correctly.
365 CREATE TABLE t1 (c1 INT,
366 c2 INT UNSIGNED INVISIBLE DEFAULT(10),
367 c3 INT UNSIGNED NOT NULL INVISIBLE DEFAULT(90),
368 c4 INT UNSIGNED PRIMARY KEY INVISIBLE DEFAULT(5),
369 c5 VARCHAR(100) CHARSET ucs2 INVISIBLE,
370 c6 GEOMETRY INVISIBLE,
371 c7 ENUM("enum_v1", "enum_v2", "enum_v3") CHARACTER SET swe7 COLLATE swe7_bin INVISIBLE,
372 c8 SET("set_v1", "set_v2", "set_v3") CHARACTER SET swe7 COLLATE swe7_bin INVISIBLE);
373
374 SET GLOBAL binlog_row_metadata = FULL;
375 INSERT INTO t1 VALUES(1);
376 --source extra/binlog_tests/print_optional_metadata.inc
377 UPDATE t1 SET c1=4 WHERE c4=5;
378 --source extra/binlog_tests/print_optional_metadata.inc
379
380 RESET MASTER;
381 --echo # INVISIBLE clause is included with only binlog_row_metadata = FULL.
382 SET GLOBAL binlog_row_metadata = MINIMAL;
383 INSERT INTO t1(c1, c4) VALUES(3, 6);
384 --source extra/binlog_tests/print_optional_metadata.inc
385 UPDATE t1 SET c1=4 WHERE c4=6;
386 --source extra/binlog_tests/print_optional_metadata.inc
387 DROP TABLE t1;
388 RESET MASTER;