Description:
NOTE #1: replication works just fine, so this looks like a mysqlbinlog bug, not a binlog format issue.
NOTE #2: changing the data type of the blob column from JSON to LONGTEXT makes mysqlbinlog happy.
Table schema:
CREATE TABLE `dbarchived` (
`date` date NOT NULL COMMENT 'Archived Date (PK)',
`territoryId` varchar(36) NOT NULL COMMENT 'Territory UUID (PK)',
`accountId` varchar(36) DEFAULT NULL COMMENT 'Account UUID (indexed)',
`version` int(11) NOT NULL COMMENT 'Volo version that created this RoutePlan',
`vehicles` int(11) NOT NULL COMMENT 'Vehicles count',
`orders` int(11) NOT NULL COMMENT 'Orders count (all)',
`unassignedOrders` int(11) NOT NULL COMMENT 'Orders count (unassigned)',
`routes` int(11) NOT NULL COMMENT 'Routes count',
`routeplan` json DEFAULT NULL,
PRIMARY KEY (`territoryId`,`date`),
KEY `accountId_idx` (`accountId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
Command executed:
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/mysql-bin.xxxxxx | cut -c -100
Binlog parameters to be aware of:
binlog_format=mixed
binlog_row_image=minimal
Example of the error:
BEGIN
/*!*/;
# at 24530751
#170529 15:15:25 server id 1 end_log_pos 24530818 CRC32 0x50be0446 Table_map: `volo`.`dbarchived`
# at 24530818
#170529 15:15:25 server id 1 end_log_pos 25068541 CRC32 0x0c794971 Write_rows: table id 222 flags:
### INSERT INTO `mydb`.`dbarchived`
### SET
### @1='2017:05:24'
### @2='9b46813d-5328-418c-a43c-025437eee176'
### @3='20c4bf4a-a5d6-430e-ba23-ef6cb302c7a3'
### @4=7
### @5=14
### @6=117
### @7=0
### @8=7
### @9='\x01\x1a\x00\x00\x00▒3\x08\x00&\x01\x00\x00\x02\x00(\x01\x00\x00\x04\x00,\x01\x00\x00\x05\
### INSERT INTO `mydb`.`dbarchived`
### SET
### @1=NULL
### @2=NULL
### @3='ctimeInSecvehicleIdtimeOutSecstatusLatLngtimeInLatLngautoTimeInSecautoTimeOutSecautoTimeIn
### @4=1332047209
### @5=NULL
### @6=NULL
### @7=NULL
### @8=1632400501
### @9=NULL
### INSERT INTO `mydb`.`dbarchived`
### SET
### @1='0179:11:14'
### @2=''
### @3=NULL
### @4=721027
### @5=NULL
### @6=NULL
### @7=NULL
### @8=251658244
### @9=***Corrupted replication event was detected. Not printing the value***
# at 25068541
#170529 15:15:25 server id 1 end_log_pos 25068572 CRC32 0xfe1824fe Xid = 2181
COMMIT/*!*/;
How to repeat:
1) Enable binlog on MySQL 5.7.18
2) Configure binlog with
binlog_format=mixed
binlog_row_image=minimal
3) Create a table like
CREATE TABLE `dbarchived` (
`date` date NOT NULL COMMENT 'Archived Date (PK)',
`territoryId` varchar(36) NOT NULL COMMENT 'Territory UUID (PK)',
`accountId` varchar(36) DEFAULT NULL COMMENT 'Account UUID (indexed)',
`version` int(11) NOT NULL COMMENT 'Volo version that created this RoutePlan',
`vehicles` int(11) NOT NULL COMMENT 'Vehicles count',
`orders` int(11) NOT NULL COMMENT 'Orders count (all)',
`unassignedOrders` int(11) NOT NULL COMMENT 'Orders count (unassigned)',
`routes` int(11) NOT NULL COMMENT 'Routes count',
`routeplan` json DEFAULT NULL,
PRIMARY KEY (`territoryId`,`date`),
KEY `accountId_idx` (`accountId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
4) Insert a row with some JSON data in it
5) Run mysqlbinlog in verbose mode with DECODE-ROWS like:
mysqlbinlog --base64-output=DECODE-ROWS --verbose /var/lib/mysql/mysql-bin.xxxxxx
