Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.18, 10.5.9
-
None
-
Ubuntu 18.04 / Ubuntu 20.04
Description
option --table=tablename for mysqlbinlog is shown in mysqlbinlog --help but not documented online. Specifying this option does not seem to select SQL statements for the specified table.
What is the purpose of this option and what should it do?
2023 08 16 - If, according to the current documentation (--table (-T)), list entries for just this table (local log only). Then this is a straight-up bug rather than a documentation bug. Current behavior, when satisfying all conditions (using a local binary log file), is that it is failing to filter for the table given. It is effectively ignoring -T.
Attachments
Issue Links
Activity
Added the brief mention in the man page on Knowlendge Base, I'll leave this open to incorporate a longer description at some point
This option was added to mysqlbinlog during the work on the flashback feature https://mariadb.com/kb/en/flashback/. (
MDEV-10570)-T, --table= List entries for just this table (local log only).So -vv --table=X will have dml for table X +all ddl, while --flashback --table=X only inverted dml for the table X (INSERT to DELETE, DELETE to INSERT, and for UPDATEs the before and after images are swapped). For example:
mysqlbinlog -v --table=t1
CREATE TABLE t1 (i int)/*!*/;# at 461#210414 13:22:37 server id 1 end_log_pos 503 CRC32 0x6e5bb1a1 GTID 0-1-2 ddl/*!100001 SET @@session.gtid_seq_no=2*//*!*/;# at 503#210414 13:22:37 server id 1 end_log_pos 593 CRC32 0xb1521a92 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;CREATE TABLE t2 (i int)/*!*/;# at 593#210414 13:22:37 server id 1 end_log_pos 635 CRC32 0xf2f14206 GTID 0-1-3/*!100001 SET @@session.gtid_seq_no=3*//*!*/;START TRANSACTION/*!*/;# at 635# at 687#210414 13:22:37 server id 1 end_log_pos 687 CRC32 0x80ab464d Annotate_rows:#Q> INSERT into t1 values (1),(2)#210414 13:22:37 server id 1 end_log_pos 732 CRC32 0xefae12b5 Table_map: `test`.`t1` mapped to number 32# at 732#210414 13:22:37 server id 1 end_log_pos 775 CRC32 0x3e42cf41 Write_rows: table id 32 flags: STMT_END_FBINLOG 'fdB2YBMBAAAALQAAANwCAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAG1Eq7vfdB2YBcBAAAAKwAAAAcDAAAAACAAAAAAAAEAAf/+AQAAAP4CAAAAQc9CPg=='/*!*/;### INSERT INTO `test`.`t1`### SET### @1=1### INSERT INTO `test`.`t1`### SET### @1=2# Number of rows: 2# at 775#210414 13:22:37 server id 1 end_log_pos 848 CRC32 0xea6a5861 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;COMMIT/*!*/;# at 848#210414 13:22:37 server id 1 end_log_pos 890 CRC32 0x9c613c64 GTID 0-1-4/*!100001 SET @@session.gtid_seq_no=4*//*!*/;START TRANSACTION/*!*/;# at 890# at 942# at 987# at 1030#210414 13:22:37 server id 1 end_log_pos 1103 CRC32 0xba039fbd Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;COMMIT/*!*/;# at 1103#210414 13:22:37 server id 1 end_log_pos 1145 CRC32 0x26bc0f30 GTID 0-1-5 ddl/*!100001 SET @@session.gtid_seq_no=5*//*!*/;# at 1145#210414 13:22:37 server id 1 end_log_pos 1235 CRC32 0x971cfcbc Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;CREATE TABLE t3 (i int)/*!*/;# at 1235#210414 13:22:37 server id 1 end_log_pos 1277 CRC32 0xbab7c0f9 GTID 0-1-6/*!100001 SET @@session.gtid_seq_no=6*//*!*/;START TRANSACTION/*!*/;# at 1277# at 1319#210414 13:22:37 server id 1 end_log_pos 1319 CRC32 0xa6134208 Annotate_rows:#Q> UPDATE t1 set i=100#210414 13:22:37 server id 1 end_log_pos 1364 CRC32 0xea2a325b Table_map: `test`.`t1` mapped to number 32# at 1364#210414 13:22:37 server id 1 end_log_pos 1418 CRC32 0x64738dc8 Update_rows: table id 32 flags: STMT_END_FBINLOG 'fdB2YBMBAAAALQAAAFQFAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAFbMirqfdB2YBgBAAAANgAAAIoFAAAAACAAAAAAAAEAAf///gEAAAD+ZAAAAP4CAAAA/mQAAADIjXNk'/*!*/;### UPDATE `test`.`t1`### WHERE### @1=1### SET### @1=100### UPDATE `test`.`t1`### WHERE### @1=2### SET### @1=100# Number of rows: 2# at 1418#210414 13:22:37 server id 1 end_log_pos 1491 CRC32 0x11dd1ff0 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;COMMIT/*!*/;with --flashback --table=t1 we have update and then delete:
#210414 13:22:37 server id 1 end_log_pos 285 CRC32 0x72f8db35 Gtid list []#210414 13:22:37 server id 1 end_log_pos 329 CRC32 0x10f5ed58 Binlog checkpoint master-bin.000001#210414 13:22:37 server id 1 end_log_pos 687 CRC32 0x80ab464d Annotate_rows:#Q> INSERT into t1 values (1),(2)#210414 13:22:37 server id 1 end_log_pos 732 CRC32 0xefae12b5 Table_map: `test`.`t1` mapped to number 32# Number of rows: 2#210414 13:22:37 server id 1 end_log_pos 1319 CRC32 0xa6134208 Annotate_rows:#Q> UPDATE t1 set i=100#210414 13:22:37 server id 1 end_log_pos 1364 CRC32 0xea2a325b Table_map: `test`.`t1` mapped to number 32# Number of rows: 2#210414 13:22:37 server id 1 end_log_pos 1539 CRC32 0x0af3a99d Rotate to master-bin.000002 pos: 4#210414 13:22:37 server id 1 end_log_pos 1491 CRC32 0x11dd1ff0 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;START TRANSACTION/*!*/;#210414 13:22:37 server id 1 end_log_pos 1418 CRC32 0x64738dc8 Update_rows: table id 32 flags: STMT_END_FBINLOG 'fdB2YBMBAAAALQAAAFQFAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAFbMirqfdB2YBgBAAAANgAAAIoFAAAAACAAAAAAAAEAAf///mQAAAD+AgAAAP5kAAAA/gEAAADIjXNk'/*!*/;COMMIT/*!*/;#210414 13:22:37 server id 1 end_log_pos 1235 CRC32 0x971cfcbc Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;#210414 13:22:37 server id 1 end_log_pos 1103 CRC32 0xba039fbd Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;START TRANSACTION/*!*/;COMMIT/*!*/;#210414 13:22:37 server id 1 end_log_pos 848 CRC32 0xea6a5861 Query thread_id=10 exec_time=0 error_code=0SET TIMESTAMP=1618399357/*!*/;START TRANSACTION/*!*/;#210414 13:22:37 server id 1 end_log_pos 775 CRC32 0x3e42cf41 Delete_rows: table id 32 flags: STMT_END_FBINLOG 'fdB2YBMBAAAALQAAANwCAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAG1Eq7vfdB2YBkBAAAAKwAAAAcDAAAAACAAAAAAAAEAAf/+AgAAAP4BAAAAQc9CPg=='/*!*/;COMMIT/*!*/;