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 --source include/test_db_charset_latin1.inc
21
22 RESET MASTER;
23 SET GLOBAL binlog_row_metadata = MINIMAL;
24
25 --let $MYSQLD_DATADIR= `select @@datadir`
26 --let $binlog_file= $MYSQLD_DATADIR/master-bin.000001
27
28 --echo #
29 --echo # Temporal types can be printed correctly
30 --echo #
31 CREATE TABLE t1(c_year YEAR, c_date DATE, c_time TIME, c_time_f TIME(3),
32 c_datetime DATETIME, c_datetime_f DATETIME(3),
33 c_timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
34 c_timestamp_f TIMESTAMP(3) DEFAULT "2017-1-1 10:10:10");
35
36 INSERT INTO t1(c_year) VALUES(2017);
37 --source include/print_optional_metadata.inc
38
39 DROP TABLE t1;
40 RESET MASTER;
41
42 --echo #
43 --echo # Geometry types can be printed correctly
44 --echo #
45 CREATE TABLE t1 (c_geo GEOMETRY, c_point POINT, c_linestring LINESTRING,
46 c_polygon POLYGON, c_multi_point MULTIPOINT,
47 c_multi_linestring MULTILINESTRING, c_multi_polygon MULTIPOLYGON,
48 c_geometrycollection GEOMETRYCOLLECTION, c_char CHAR(100));
49
50 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
51 --source include/print_optional_metadata.inc
52
53 RESET MASTER;
54 SET GLOBAL binlog_row_metadata = FULL;
55
56 # geometry type is binlogged, the real geometry types are printed
57 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
58 --source include/print_optional_metadata.inc
59
60 DROP TABLE t1;
61 RESET MASTER;
62
63 --echo #
64 --echo # Numeric types can be printed correctly
65 --echo #
66 CREATE TABLE t1(c_bit BIT(10), c_bool BOOL, c_smallint SMALLINT,
67 c_mediumint MEDIUMINT, c_int INT UNSIGNED, c_bigint BIGINT,
68 c_float FLOAT UNSIGNED, c_double DOUBLE, c_decimal DECIMAL(10, 2));
69
70 SET GLOBAL binlog_row_metadata = MINIMAL;
71 INSERT INTO t1(c_bool) VALUES(1);
72
73 --echo # UNSIGNED flag should be printed
74 --source include/print_optional_metadata.inc
75
76 RESET MASTER;
77 SET GLOBAL binlog_row_metadata = FULL;
78 INSERT INTO t1(c_bool) VALUES(1);
79
80 --source include/print_optional_metadata.inc
81
82 DROP TABLE t1;
83 RESET MASTER;
84
85 --echo #
86 --echo # Character types can be printed correctly
87 --echo #
88 CREATE TABLE t1(c_char CHAR(10), c_varchar VARCHAR(500),
89 c_tinytext TINYTEXT, c_text TEXT,
90 c_mediumtext MEDIUMTEXT, c_longtext LONGTEXT CHARSET utf8);
91
92 SET GLOBAL binlog_row_metadata = MINIMAL;
93 INSERT INTO t1(c_char) VALUES("1");
94
95 # Charset set is printed with default charset
96 --source include/print_optional_metadata.inc
97
98 RESET MASTER;
99 SET GLOBAL binlog_row_metadata = FULL;
100 INSERT INTO t1(c_char) VALUES("1");
101
102 --source include/print_optional_metadata.inc
103
104 DROP TABLE t1;
105 RESET MASTER;
106
107 --echo #
108 --echo # Column names with non-ascii characters and escape characters can be printed correctly
109 --echo #
110 set names utf8;
111 CREATE TABLE t1(`åäö表\a'``"` INT);
112
113 SHOW CREATE TABLE t1;
114
115 INSERT INTO t1 VALUES(1);
116 --source include/print_optional_metadata.inc
117 DROP TABLE t1;
118 RESET MASTER;
119 --echo #
120 --echo # Charsets can be printed correctly
121 --echo #
122 CREATE TABLE t1(c_char_utf8 CHAR(10) CHARSET utf8,
123 c_varchar_utf8 VARCHAR(10) CHARSET utf8,
124 c_text_utf8 TEXT CHARSET utf8);
125
126 INSERT INTO t1 VALUES("1", "2", "3");
127
128 # Charset set is printed with Default charset
129 --source include/print_optional_metadata.inc
130
131 DROP TABLE t1;
132 RESET MASTER;
133
134 # Test collation number less than 250 and collation number greater than 250
135 CREATE TABLE t1(c_utf8mb4_520 CHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
136 c_utf8mb4_0900 VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_polish_ci,
137 c_utf8mb4_def TEXT CHARSET utf8mb4);
138
139 INSERT INTO t1 VALUES("1", "2", "3");
140
141 # Charset set is printed without default charset
142 --source include/print_optional_metadata.inc
143
144 DROP TABLE t1;
145 RESET MASTER;
146
147 --echo #
148 --echo # Blob and binary columns can be printed correctly
149 --echo #
150 CREATE TABLE t1(c_binary BINARY(10), c_varbinary VARBINARY(10),
151 c_tinyblob TINYBLOB, c_blob BLOB,
152 c_mediumblob MEDIUMBLOB, c_longblob LONGBLOB);
153
154 INSERT INTO t1 VALUES("1", "2", "3", "4", "5", "6");
155 --source include/print_optional_metadata.inc
156
157 DROP TABLE t1;
158 RESET MASTER;
159
160 --echo #
161 --echo # Verify that SET string values and character sets can be printed correctly
162 --echo #
163
164 set names utf8;
165 CREATE TABLE t1(
166 c_set_1 SET("set1_v1_å", "set1_v2_ä", "set1_v3_ö"),
167 c_set_2 SET("set2_v1_å", "set2_v2_ä", "set2_v3_ö") CHARACTER SET latin1,
168 c_set_4 SET("set3_v1_å", "set3_v2_ä", "set3_v3_ö") CHARACTER SET swe7 COLLATE swe7_bin);
169
170 SET GLOBAL binlog_row_metadata = MINIMAL;
171 INSERT INTO t1 VALUES("set1_v1_å", "set2_v3_ö", "set3_v1_å");
172 --source include/print_optional_metadata.inc
173
174 RESET MASTER;
175 SET GLOBAL binlog_row_metadata = FULL;
176 INSERT INTO t1 VALUES("set1_v1_å", "set2_v3_ö", "set3_v1_å");
177 --source include/print_optional_metadata.inc
178
179 DROP TABLE t1;
180 RESET MASTER;
181
182 --echo #
183 --echo # Verify that ENUM string values and character sets can be printed correctly
184 --echo #
185
186 CREATE TABLE t1(
187 c_enum_1 ENUM("enum1_v1_å", "enum1_v2_ä", "enum1_v3_ö"),
188 c_enum_3 ENUM("enum2_v1_å", "enum2_v2_ä", "enum2_v3_ö") CHARACTER SET latin1,
189 c_enum_4 ENUM("enum3_v1_å", "enum3_v2_ä", "enum3_v3_ö") CHARACTER SET swe7 COLLATE swe7_bin);
190
191 SET GLOBAL binlog_row_metadata = MINIMAL;
192 INSERT INTO t1 VALUES("enum1_v1_å", "enum2_v3_ö", "enum3_v1_å");
193 --source include/print_optional_metadata.inc
194
195 RESET MASTER;
196 SET GLOBAL binlog_row_metadata = FULL;
197 INSERT INTO t1 VALUES("enum1_v1_å", "enum2_v3_ö", "enum3_v1_å");
198 --source include/print_optional_metadata.inc
199
200 DROP TABLE t1;
201 RESET MASTER;
202
203 --echo #
204 --echo # Verify that explicit NOT NULL can be printed correctly
205 --echo #
206 CREATE TABLE t1(c_not_null1 INT NOT NULL, c_null1 INT, c_not_null2 INT NOT NULL,
207 c_null2 INT);
208
209 INSERT INTO t1 VALUES(1, 2, 3, 4);
210 --source include/print_optional_metadata.inc
211
212 DROP TABLE t1;
213 RESET MASTER;
214
215 --echo #
216 --echo # Verify that primary key can be printed correctly
217 --echo #
218 CREATE TABLE t1(c_key1 INT, c_key3 INT, c_not_key INT, c_key2 INT,
219 PRIMARY KEY(c_key1, c_key2, c_key3));
220
221 INSERT INTO t1 VALUES(1, 2, 3, 4);
222 --let $print_primary_key= 1
223 --source include/print_optional_metadata.inc
224
225 DROP TABLE t1;
226 RESET MASTER;
227
228 # Key has prefix
229 CREATE TABLE t1(c_key1 CHAR(100), c_key3 CHAR(100), c_not_key INT, c_key2 CHAR(10),
230 PRIMARY KEY(c_key1(5), c_key2, c_key3(10)));
231
232 INSERT INTO t1 VALUES("1", "2", 3, "4");
233 --source include/print_optional_metadata.inc
234
235 RESET MASTER;
236 # Primary key should not be printed
237 SET GLOBAL binlog_row_metadata = MINIMAL;
238
239 INSERT INTO t1 VALUES("2", "2", 3, "4");
240 --source include/print_optional_metadata.inc
241
242 RESET MASTER;
243 --echo #
244 --echo # Coverage test: Print column index instead of column name if column name
245 --echo # is not binlogged.
246 --echo #
247 SET GLOBAL binlog_row_metadata = FULL;
248
249 SET SESSION debug_dbug = 'd, dont_log_column_name';
250 INSERT INTO t1 VALUES("3", "2", 3, "4");
251 --source include/print_optional_metadata.inc
252
253 --let $print_primary_key=
254 DROP TABLE t1;
255 RESET MASTER;
256
257 --echo #
258 --echo # Coverage test: Inject an invalid column type
259 --echo #
260 CREATE TABLE t1(c1 int, c2 BLOB);
261
262 SET SESSION debug_dbug = 'd,inject_invalid_column_type';
263 INSERT INTO t1 VALUES(1, "a");
264 # It prints an error
265 --source include/print_optional_metadata.inc
266
267 RESET MASTER;
268
269 --echo #
270 --echo # Coverage test: Inject an invalid BLOB metadata
271 --echo #
272 --let $start_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
273
274 SET SESSION debug_dbug = 'd,inject_invalid_blob_size';
275 INSERT INTO t1 VALUES(2, "b");
276
277 # The invalid metadata will case assertion failure on Write_rows_log_event
278 # So we need to stop mysqlbinlog before reading Write_rows_log_event.
279 --let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 4, Pos, 4)
280 --source include/print_optional_metadata.inc
281
282 --echo #
283 --echo # Coverage test: Inject an invalid Geometry type
284 --echo #
285 DROP TABLE t1;
286 CREATE TABLE t1(c_geometry GEOMETRY, c_point POINT, c_multilinestring MULTILINESTRING);
287 RESET MASTER;
288 --let $start_pos= query_get_value(SHOW MASTER STATUS, Position, 1)
289
290 SET SESSION debug_dbug = 'd,inject_invalid_geometry_type';
291 INSERT INTO t1(c_point) VALUES(ST_PointFromText('POINT(10 10)'));
292
293 # The invalid metadata will case assertion failure on Write_rows_log_event
294 # So we need to stop mysqlbinlog before reading Write_rows_log_event.
295 --let $stop_position= query_get_value(SHOW BINLOG EVENTS FROM $start_pos LIMIT 4, Pos, 4)
296 --source include/print_optional_metadata.inc
297
298 DROP TABLE t1;
299 RESET MASTER;
300 --echo #
301 --echo # Comptibility Test: Verify mysqlbinlog can print OLD table_map_log_event
302 --echo # without any optional metadata
303 --echo #
304 CREATE TABLE t1(c_int INT, c_tiny_int_unsigned TINYINT UNSIGNED,
305 c_binary BINARY(10), c_text TEXT, c_point POINT);
306
307 SET session debug_dbug='d,simulate_no_optional_metadata';
308 INSERT INTO t1(c_int) VALUES(1);
309 # TINYINT will be printed without UNSIGNED flag,
310 # CHAR will be printed as BINARY(10)
311 # POINT will be printed as GEOMETRY
312 --let $stop_position=
313 --source include/print_optional_metadata.inc
314
315 DROP TABLE t1;
316 RESET MASTER;
317 --echo #
318 --echo # Simulate error on initializing charset and primary key metadata
319 --echo #
320 CREATE TABLE t1(c1 char(10) PRIMARY KEY);
321
322 SET session debug_dbug='d,simulate_init_charset_field_error';
323 INSERT INTO t1 VALUES("a");
324
325 SET GLOBAL binlog_row_metadata = FULL;
326 SET session debug_dbug='d,simulate_init_primary_key_field_error';
327 INSERT INTO t1 VALUES("b");
328
329 --let $print_primary_key= 1
330 --source include/print_optional_metadata.inc
331
332 SET SESSION debug_dbug = '';
333 SET GLOBAL binlog_row_metadata = NO_LOG;
334 DROP TABLE t1;
335 RESET MASTER;
336
337 --source include/test_db_charset_restore.inc