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