Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.26
Description
When binlog_row_image=MINIMAL is configured and an "ON UPDATE CURRENT_TIMESTAMP" field is automatically changed due to an update to the row, that change should be logged to the binlog with all of the other changes to the row, but that does not always happen.
One case where this happens is when using the multi-table syntax for UPDATE. For example, run the following test:
CREATE TABLE `tab1` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`is_deleted` bit(1) DEFAULT b'0',
|
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
`ref_id` bigint(20) unsigned NOT NULL,
|
PRIMARY KEY (`id`),
|
KEY `last_updated_KEY` (`last_updated`)
|
);
|
|
|
CREATE TABLE `tab2` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`short_desc` varchar(50) NOT NULL,
|
PRIMARY KEY (`id`)
|
);
|
|
|
INSERT INTO tab2 (id, short_desc) VALUES (1, 'test');
|
INSERT INTO tab1 (id, is_deleted, ref_id) VALUES (1, b'0', 1);
|
UPDATE tab1 t1
|
INNER JOIN tab2 t2 ON t1.ref_id = t2.id
|
SET t1.is_deleted = TRUE
|
WHERE t1.id = 1;
|
When the following is configured:
binlog_format=ROW
|
binlog_row_image = MINIMAL
|
Then the updated timestamp will be missing from the resulting binlog event created by the UPDATE statement:
# at 3829
|
#171207 13:45:43 server id 1 end_log_pos 3867 GTID 1-1-55 trans
|
/*!100001 SET @@session.gtid_seq_no=55*//*!*/;
|
BEGIN
|
/*!*/;
|
# at 3867
|
#171207 13:45:43 server id 1 end_log_pos 3915 Table_map: `db1`.`tab1` mapped to number 29
|
# at 3915
|
#171207 13:45:43 server id 1 end_log_pos 3965 Update_rows: table id 29 flags: STMT_END_F
|
|
|
BINLOG '
|
V4wpWhMBAAAAMAAAAEsPAAAAAB0AAAAAAAEAA2RiMQAEdGFiMQAECBARCAMBAAAC
|
V4wpWhgBAAAAMgAAAH0PAAAAAB0AAAAAAAEABAsC+AEAAAAAAAAAAAEAAAAAAAAA/gE=
|
'/*!*/;
|
### UPDATE `db1`.`tab1`
|
### WHERE
|
### @1=1
|
### @2=b'0'
|
### @4=1
|
### SET
|
### @2=b'1'
|
# at 3965
|
#171207 13:45:43 server id 1 end_log_pos 3992 Xid = 10
|
COMMIT/*!*/;
|
If binlog_row_image=MINIMAL is not set, then the updated value is in the binlog event:
# at 622
|
#171207 14:17:58 server id 1 end_log_pos 660 GTID 1-1-58 trans
|
/*!100001 SET @@session.gtid_seq_no=58*//*!*/;
|
BEGIN
|
/*!*/;
|
# at 660
|
#171207 14:17:58 server id 1 end_log_pos 708 Table_map: `db1`.`tab1` mapped to number 25
|
# at 708
|
#171207 14:17:58 server id 1 end_log_pos 782 Update_rows: table id 25 flags: STMT_END_F
|
|
|
BINLOG '
|
5pMpWhMBAAAAMAAAAMQCAAAAABkAAAAAAAEAA2RiMQAEdGFiMQAECBARCAMBAAAC
|
5pMpWhgBAAAASgAAAA4DAAAAABkAAAAAAAEABP//8AEAAAAAAAAAAFopk+UBAAAAAAAAAPABAAAA
|
AAAAAAFaKZPmAQAAAAAAAAA=
|
'/*!*/;
|
### UPDATE `db1`.`tab1`
|
### WHERE
|
### @1=1
|
### @2=b'0'
|
### @3=1512674277
|
### @4=1
|
### SET
|
### @1=1
|
### @2=b'1'
|
### @3=1512674278
|
### @4=1
|
# at 782
|
#171207 14:17:58 server id 1 end_log_pos 809 Xid = 13
|
COMMIT/*!*/;
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-