1CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f5 BIGINT, f6 INT, f7 INT, f8 char(1)); 2CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMINT, f6 INT, f7 INT, f8 char(1)); 3INSERT INTO t1 VALUES (10, 1, 2, 3, 4, 5, 6, 7, ""); 4INSERT INTO t1 VALUES (11, 1, 2, 3, 4, 5, 6, 7, NULL); 5INSERT INTO t1 VALUES (12, 1, 2, 3, NULL, 5, 6, 7, "A"); 6INSERT INTO t1 VALUES (13, 1, 2, 3, 0, 5, 6, 7, "A"); 7INSERT INTO t2 SELECT * FROM t1; 8UPDATE t2 SET f4=5 WHERE f4>0 or f4 is NULL; 9DELETE FROM t1; 10DELETE FROM t2; 11FLUSH BINARY LOGS; 12/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 13/*!40019 SET @@session.max_insert_delayed_threads=0*/; 14/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 15DELIMITER /*!*/; 16# at 4 17#<date> server id 1 end_log_pos 256 CRC32 XXX Start: xxx 18ROLLBACK/*!*/; 19# at 256 20#<date> server id 1 end_log_pos 285 CRC32 XXX Gtid list [] 21# at 285 22#<date> server id 1 end_log_pos 329 CRC32 XXX Binlog checkpoint master-bin.000001 23# at 329 24#<date> server id 1 end_log_pos 371 CRC32 XXX GTID 0-1-1 ddl 25/*!100101 SET @@session.skip_parallel_replication=0*//*!*/; 26/*!100001 SET @@session.gtid_domain_id=0*//*!*/; 27/*!100001 SET @@session.server_id=1*//*!*/; 28/*!100001 SET @@session.gtid_seq_no=1*//*!*/; 29# at 371 30#<date> server id 1 end_log_pos 555 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 31use `test`/*!*/; 32SET TIMESTAMP=X/*!*/; 33SET @@session.pseudo_thread_id=5/*!*/; 34SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; 35SET @@session.sql_mode=#/*!*/; 36SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 37/*!\C latin1 *//*!*/; 38SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; 39SET @@session.lc_time_names=0/*!*/; 40SET @@session.collation_database=DEFAULT/*!*/; 41CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f5 BIGINT, f6 INT, f7 INT, f8 char(1)) 42/*!*/; 43# at 555 44#<date> server id 1 end_log_pos 597 CRC32 XXX GTID 0-1-2 ddl 45/*!100001 SET @@session.gtid_seq_no=2*//*!*/; 46# at 597 47#<date> server id 1 end_log_pos 774 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 48SET TIMESTAMP=X/*!*/; 49CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMINT, f6 INT, f7 INT, f8 char(1)) 50/*!*/; 51# at 774 52#<date> server id 1 end_log_pos 816 CRC32 XXX GTID 0-1-3 53/*!100001 SET @@session.gtid_seq_no=3*//*!*/; 54START TRANSACTION 55/*!*/; 56# at 816 57# at 890 58#<date> server id 1 end_log_pos 890 CRC32 XXX Annotate_rows: 59#Q> INSERT INTO t1 VALUES (10, 1, 2, 3, 4, 5, 6, 7, "") 60#<date> server id 1 end_log_pos 946 CRC32 XXX Table_map: `test`.`t1` mapped to number num 61# at 946 62#<date> server id 1 end_log_pos 1015 CRC32 XXX Write_rows: table id 32 flags: STMT_END_F 63### INSERT INTO `test`.`t1` 64### SET 65### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 66### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 67### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 68### @4=3 /* TINYINT meta=0 nullable=1 is_null=0 */ 69### @5=4 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 70### @6=5 /* LONGINT meta=0 nullable=1 is_null=0 */ 71### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 72### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 73### @9='' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 74# Number of rows: 1 75# at 1015 76#<date> server id 1 end_log_pos 1088 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 77SET TIMESTAMP=X/*!*/; 78COMMIT 79/*!*/; 80# at 1088 81#<date> server id 1 end_log_pos 1130 CRC32 XXX GTID 0-1-4 82/*!100001 SET @@session.gtid_seq_no=4*//*!*/; 83START TRANSACTION 84/*!*/; 85# at 1130 86# at 1206 87#<date> server id 1 end_log_pos 1206 CRC32 XXX Annotate_rows: 88#Q> INSERT INTO t1 VALUES (11, 1, 2, 3, 4, 5, 6, 7, NULL) 89#<date> server id 1 end_log_pos 1262 CRC32 XXX Table_map: `test`.`t1` mapped to number num 90# at 1262 91#<date> server id 1 end_log_pos 1330 CRC32 XXX Write_rows: table id 32 flags: STMT_END_F 92### INSERT INTO `test`.`t1` 93### SET 94### @1=11 /* INT meta=0 nullable=0 is_null=0 */ 95### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 96### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 97### @4=3 /* TINYINT meta=0 nullable=1 is_null=0 */ 98### @5=4 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 99### @6=5 /* LONGINT meta=0 nullable=1 is_null=0 */ 100### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 101### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 102### @9=NULL /* STRING(1) meta=65025 nullable=1 is_null=1 */ 103# Number of rows: 1 104# at 1330 105#<date> server id 1 end_log_pos 1403 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 106SET TIMESTAMP=X/*!*/; 107COMMIT 108/*!*/; 109# at 1403 110#<date> server id 1 end_log_pos 1445 CRC32 XXX GTID 0-1-5 111/*!100001 SET @@session.gtid_seq_no=5*//*!*/; 112START TRANSACTION 113/*!*/; 114# at 1445 115# at 1523 116#<date> server id 1 end_log_pos 1523 CRC32 XXX Annotate_rows: 117#Q> INSERT INTO t1 VALUES (12, 1, 2, 3, NULL, 5, 6, 7, "A") 118#<date> server id 1 end_log_pos 1579 CRC32 XXX Table_map: `test`.`t1` mapped to number num 119# at 1579 120#<date> server id 1 end_log_pos 1646 CRC32 XXX Write_rows: table id 32 flags: STMT_END_F 121### INSERT INTO `test`.`t1` 122### SET 123### @1=12 /* INT meta=0 nullable=0 is_null=0 */ 124### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 125### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 126### @4=3 /* TINYINT meta=0 nullable=1 is_null=0 */ 127### @5=NULL /* MEDIUMINT meta=0 nullable=1 is_null=1 */ 128### @6=5 /* LONGINT meta=0 nullable=1 is_null=0 */ 129### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 130### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 131### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 132# Number of rows: 1 133# at 1646 134#<date> server id 1 end_log_pos 1719 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 135SET TIMESTAMP=X/*!*/; 136COMMIT 137/*!*/; 138# at 1719 139#<date> server id 1 end_log_pos 1761 CRC32 XXX GTID 0-1-6 140/*!100001 SET @@session.gtid_seq_no=6*//*!*/; 141START TRANSACTION 142/*!*/; 143# at 1761 144# at 1836 145#<date> server id 1 end_log_pos 1836 CRC32 XXX Annotate_rows: 146#Q> INSERT INTO t1 VALUES (13, 1, 2, 3, 0, 5, 6, 7, "A") 147#<date> server id 1 end_log_pos 1892 CRC32 XXX Table_map: `test`.`t1` mapped to number num 148# at 1892 149#<date> server id 1 end_log_pos 1962 CRC32 XXX Write_rows: table id 32 flags: STMT_END_F 150### INSERT INTO `test`.`t1` 151### SET 152### @1=13 /* INT meta=0 nullable=0 is_null=0 */ 153### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 154### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 155### @4=3 /* TINYINT meta=0 nullable=1 is_null=0 */ 156### @5=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 157### @6=5 /* LONGINT meta=0 nullable=1 is_null=0 */ 158### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 159### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 160### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 161# Number of rows: 1 162# at 1962 163#<date> server id 1 end_log_pos 2035 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 164SET TIMESTAMP=X/*!*/; 165COMMIT 166/*!*/; 167# at 2035 168#<date> server id 1 end_log_pos 2077 CRC32 XXX GTID 0-1-7 169/*!100001 SET @@session.gtid_seq_no=7*//*!*/; 170START TRANSACTION 171/*!*/; 172# at 2077 173# at 2131 174#<date> server id 1 end_log_pos 2131 CRC32 XXX Annotate_rows: 175#Q> INSERT INTO t2 SELECT * FROM t1 176#<date> server id 1 end_log_pos 2187 CRC32 XXX Table_map: `test`.`t2` mapped to number num 177# at 2187 178#<date> server id 1 end_log_pos 2354 CRC32 XXX Write_rows: table id 33 flags: STMT_END_F 179### INSERT INTO `test`.`t2` 180### SET 181### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 182### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 183### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 184### @4=3 /* INT meta=0 nullable=1 is_null=0 */ 185### @5=4 /* INT meta=0 nullable=1 is_null=0 */ 186### @6=5 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 187### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 188### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 189### @9='' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 190### INSERT INTO `test`.`t2` 191### SET 192### @1=11 /* INT meta=0 nullable=0 is_null=0 */ 193### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 194### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 195### @4=3 /* INT meta=0 nullable=1 is_null=0 */ 196### @5=4 /* INT meta=0 nullable=1 is_null=0 */ 197### @6=5 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 198### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 199### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 200### @9=NULL /* STRING(1) meta=65025 nullable=1 is_null=1 */ 201### INSERT INTO `test`.`t2` 202### SET 203### @1=12 /* INT meta=0 nullable=0 is_null=0 */ 204### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 205### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 206### @4=3 /* INT meta=0 nullable=1 is_null=0 */ 207### @5=NULL /* INT meta=0 nullable=1 is_null=1 */ 208### @6=5 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 209### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 210### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 211### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 212### INSERT INTO `test`.`t2` 213### SET 214### @1=13 /* INT meta=0 nullable=0 is_null=0 */ 215### @2=1 /* INT meta=0 nullable=1 is_null=0 */ 216### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 217### @4=3 /* INT meta=0 nullable=1 is_null=0 */ 218### @5=0 /* INT meta=0 nullable=1 is_null=0 */ 219### @6=5 /* MEDIUMINT meta=0 nullable=1 is_null=0 */ 220### @7=6 /* INT meta=0 nullable=1 is_null=0 */ 221### @8=7 /* INT meta=0 nullable=1 is_null=0 */ 222### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ 223# Number of rows: 4 224# at 2354 225#<date> server id 1 end_log_pos 2427 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 226SET TIMESTAMP=X/*!*/; 227COMMIT 228/*!*/; 229# at 2427 230#<date> server id 1 end_log_pos 2469 CRC32 XXX GTID 0-1-8 231/*!100001 SET @@session.gtid_seq_no=8*//*!*/; 232START TRANSACTION 233/*!*/; 234# at 2469 235# at 2535 236#<date> server id 1 end_log_pos 2535 CRC32 XXX Annotate_rows: 237#Q> UPDATE t2 SET f4=5 WHERE f4>0 or f4 is NULL 238#<date> server id 1 end_log_pos 2591 CRC32 XXX Table_map: `test`.`t2` mapped to number num 239# at 2591 240#<date> server id 1 end_log_pos 2657 CRC32 XXX Update_rows: table id 33 flags: STMT_END_F 241### UPDATE `test`.`t2` 242### WHERE 243### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 244### SET 245### @5=5 /* INT meta=0 nullable=1 is_null=0 */ 246### UPDATE `test`.`t2` 247### WHERE 248### @1=11 /* INT meta=0 nullable=0 is_null=0 */ 249### SET 250### @5=5 /* INT meta=0 nullable=1 is_null=0 */ 251### UPDATE `test`.`t2` 252### WHERE 253### @1=12 /* INT meta=0 nullable=0 is_null=0 */ 254### SET 255### @5=5 /* INT meta=0 nullable=1 is_null=0 */ 256# Number of rows: 3 257# at 2657 258#<date> server id 1 end_log_pos 2730 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 259SET TIMESTAMP=X/*!*/; 260COMMIT 261/*!*/; 262# at 2730 263#<date> server id 1 end_log_pos 2772 CRC32 XXX GTID 0-1-9 264/*!100001 SET @@session.gtid_seq_no=9*//*!*/; 265START TRANSACTION 266/*!*/; 267# at 2772 268# at 2809 269#<date> server id 1 end_log_pos 2809 CRC32 XXX Annotate_rows: 270#Q> DELETE FROM t1 271#<date> server id 1 end_log_pos 2865 CRC32 XXX Table_map: `test`.`t1` mapped to number num 272# at 2865 273#<date> server id 1 end_log_pos 2919 CRC32 XXX Delete_rows: table id 32 flags: STMT_END_F 274### DELETE FROM `test`.`t1` 275### WHERE 276### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 277### DELETE FROM `test`.`t1` 278### WHERE 279### @1=11 /* INT meta=0 nullable=0 is_null=0 */ 280### DELETE FROM `test`.`t1` 281### WHERE 282### @1=12 /* INT meta=0 nullable=0 is_null=0 */ 283### DELETE FROM `test`.`t1` 284### WHERE 285### @1=13 /* INT meta=0 nullable=0 is_null=0 */ 286# Number of rows: 4 287# at 2919 288#<date> server id 1 end_log_pos 2992 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 289SET TIMESTAMP=X/*!*/; 290COMMIT 291/*!*/; 292# at 2992 293#<date> server id 1 end_log_pos 3034 CRC32 XXX GTID 0-1-10 294/*!100001 SET @@session.gtid_seq_no=10*//*!*/; 295START TRANSACTION 296/*!*/; 297# at 3034 298# at 3071 299#<date> server id 1 end_log_pos 3071 CRC32 XXX Annotate_rows: 300#Q> DELETE FROM t2 301#<date> server id 1 end_log_pos 3127 CRC32 XXX Table_map: `test`.`t2` mapped to number num 302# at 3127 303#<date> server id 1 end_log_pos 3181 CRC32 XXX Delete_rows: table id 33 flags: STMT_END_F 304### DELETE FROM `test`.`t2` 305### WHERE 306### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 307### DELETE FROM `test`.`t2` 308### WHERE 309### @1=11 /* INT meta=0 nullable=0 is_null=0 */ 310### DELETE FROM `test`.`t2` 311### WHERE 312### @1=12 /* INT meta=0 nullable=0 is_null=0 */ 313### DELETE FROM `test`.`t2` 314### WHERE 315### @1=13 /* INT meta=0 nullable=0 is_null=0 */ 316# Number of rows: 4 317# at 3181 318#<date> server id 1 end_log_pos 3254 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 319SET TIMESTAMP=X/*!*/; 320COMMIT 321/*!*/; 322# at 3254 323#<date> server id 1 end_log_pos 3302 CRC32 XXX Rotate to master-bin.000002 pos: 4 324DELIMITER ; 325# End of log file 326ROLLBACK /* added by mysqlbinlog */; 327/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 328/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 329DROP TABLE t1,t2; 330# 331# MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log with system table 332# 333FLUSH BINARY LOGS; 334CREATE TABLE t1 (pk INT PRIMARY KEY); 335INSERT INTO t1 VALUES (1); 336INSERT INTO t1 VALUES (1), (2) ON DUPLICATE KEY UPDATE pk= pk + 10; 337FLUSH BINARY LOGS; 338Proof: two subsequent patterns must be found 339FOUND 1 /### UPDATE `test`.`t1`/ in mysqlbinlog.sql 340FOUND 2 /### INSERT INTO `test`.`t1`/ in mysqlbinlog.sql 341DROP TABLE t1; 342SELECT * FROM t1; 343pk 3442 34511 346# Cleanup 347DROP TABLE t1; 348CREATE TABLE `t1` ( 349`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 350`is_deleted` BIT(1) DEFAULT b'0', 351`last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 352`ref_id` BIGINT(20) UNSIGNED NOT NULL, 353PRIMARY KEY (`id`), 354KEY `last_updated_KEY` (`last_updated`) 355); 356CREATE TABLE `t2` ( 357`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 358`short_desc` VARCHAR(50) NOT NULL, 359PRIMARY KEY (`id`) 360); 361INSERT INTO t2 (id, short_desc) VALUES (1, 'test'); 362INSERT INTO t1 (id, is_deleted, ref_id) VALUES (1, b'0', 1); 363FLUSH BINARY LOGS; 364UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id 365SET t1.is_deleted = TRUE 366WHERE t1.id = 1; 367FLUSH BINARY LOGS; 368/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 369/*!40019 SET @@session.max_insert_delayed_threads=0*/; 370/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 371DELIMITER /*!*/; 372# at POS 373#<date> server id 1 end_log_pos END_LOG_POS CRC32 XXX GTID D-S-N 374/*!100101 SET @@session.skip_parallel_replication=0*//*!*/; 375/*!100001 SET @@session.gtid_domain_id=0*//*!*/; 376/*!100001 SET @@session.server_id=1*//*!*/; 377/*!100001 SET @@session.gtid_seq_no=21*//*!*/; 378START TRANSACTION 379/*!*/; 380# at POS 381# at POS 382#<date> server id 1 end_log_pos END_LOG_POS CRC32 XXX Annotate_rows: 383#Q> UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id 384#Q> SET t1.is_deleted = TRUE 385#Q> WHERE t1.id = 386#<date> server id 1 end_log_pos END_LOG_POS CRC32 XXX Table_map: `test`.`t1` mapped to number TID 387# at POS 388#<date> server id 1 end_log_pos END_LOG_POS CRC32 XXX Update_rows: table id TID flags: STMT_END_F 389### UPDATE `test`.`t1` 390### WHERE 391### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */ 392### SET 393### @2=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ 394### @3=X /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */ 395# Number of rows: 1 396# at POS 397#<date> server id 1 end_log_pos END_LOG_POS CRC32 XXX Query thread_id=TID exec_time=x error_code=0 398SET TIMESTAMP=X/*!*/; 399SET @@session.pseudo_thread_id=TID/*!*/; 400SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; 401SET @@session.sql_mode=#/*!*/; 402SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 403/*!\C latin1 *//*!*/; 404SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=#/*!*/; 405SET @@session.lc_time_names=0/*!*/; 406SET @@session.collation_database=DEFAULT/*!*/; 407COMMIT 408/*!*/; 409DELIMITER ; 410# End of log file 411ROLLBACK /* added by mysqlbinlog */; 412/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 413/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 414DROP TABLE t1,t2; 415