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