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