----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Change BINLOG statement syntax to be human-readable CREATION DATE..: Sat, 15 Aug 2009, 23:42 SUPERVISOR.....: Monty IMPLEMENTOR....: Psergey COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 46 (http://askmonty.org/worklog/?tid=46) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Knielsen - Mon, 17 Aug 2009, 11:38)=-=- High-Level Specification modified. --- /tmp/wklog.46.old.4940 2009-08-17 11:38:49.000000000 +0300 +++ /tmp/wklog.46.new.4940 2009-08-17 11:38:49.000000000 +0300 @@ -16,8 +16,8 @@ Feedback and other suggestions ------------------------------ -* What is the need for WITH TIMESTAMP part? Can't one use a separate - SET TIMESTAMP statement? +* TIMESTAMP part is better omitted and replaced with separate SET TIMESTAMP +statement for consistency with other events. * mysqlbinlog --base64-output=DECODE-ROWS --verbose already produces something that's close to readable SQL. Can we make it to be regular parseable SQL? @@ -25,6 +25,7 @@ - A stream of SQL statements will be slower to run than BINLOG statements (due to locking, table open/close, etc). (TODO: is it really slower? we haven't checked). + One problem is that we do not have column names available in the binary log. * When SBR replication is used and the statements refer to the current database (a common scenario), one can use awk to filter out updates made in certain -=-=(Psergey - Sun, 16 Aug 2009, 11:30)=-=- High-Level Specification modified. --- /tmp/wklog.46.old.13453 2009-08-16 11:30:06.000000000 +0300 +++ /tmp/wklog.46.new.13453 2009-08-16 11:30:06.000000000 +0300 @@ -26,3 +26,7 @@ (due to locking, table open/close, etc). (TODO: is it really slower? we haven't checked). +* When SBR replication is used and the statements refer to the current database + (a common scenario), one can use awk to filter out updates made in certain + databases. The proposed syntax doesn't allow to perform equivalent filtering? + -=-=(Psergey - Sun, 16 Aug 2009, 11:13)=-=- High Level Description modified. --- /tmp/wklog.46.old.12747 2009-08-16 11:13:54.000000000 +0300 +++ /tmp/wklog.46.new.12747 2009-08-16 11:13:54.000000000 +0300 @@ -6,4 +6,4 @@ This WL task is about making BINLOG statements to be human-readable (either as an option or by default -The approach of this WL is to some extent an alternative to WL#38, WL#40, WL41. +The approach of this WL is to some extent an alternative to WL#38, WL#40, WL#41. -=-=(Psergey - Sun, 16 Aug 2009, 11:13)=-=- High Level Description modified. --- /tmp/wklog.46.old.12717 2009-08-16 11:13:40.000000000 +0300 +++ /tmp/wklog.46.new.12717 2009-08-16 11:13:40.000000000 +0300 @@ -5,3 +5,5 @@ This WL task is about making BINLOG statements to be human-readable (either as an option or by default + +The approach of this WL is to some extent an alternative to WL#38, WL#40, WL41. -=-=(Psergey - Sun, 16 Aug 2009, 11:07)=-=- Dependency created: 39 now depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 11:07)=-=- Dependency deleted: 48 no longer depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 10:59)=-=- Dependency created: 48 now depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 10:59)=-=- Dependency deleted: 39 no longer depends on 46 -=-=(Psergey - Sun, 16 Aug 2009, 00:02)=-=- Dependency created: 39 now depends on 46 -=-=(Psergey - Sat, 15 Aug 2009, 23:43)=-=- High-Level Specification modified. --- /tmp/wklog.46.old.17742 2009-08-15 23:43:09.000000000 +0300 +++ /tmp/wklog.46.new.17742 2009-08-15 23:43:09.000000000 +0300 @@ -1 +1,28 @@ +Suggestion 1 +------------ +Original syntax suggestion by Kristian: + + BINLOG + WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 + TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 + TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 + WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 + UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), + FROM ('a') TO ('b') FLAGS 0x0 + DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0; + + This is basically a dump of what is stored in the events, and would be an + alternative to BINLOG 'gwWEShMBAA...'. + +Feedback and other suggestions +------------------------------ +* What is the need for WITH TIMESTAMP part? Can't one use a separate + SET TIMESTAMP statement? + +* mysqlbinlog --base64-output=DECODE-ROWS --verbose already produces something + that's close to readable SQL. Can we make it to be regular parseable SQL? + + This will be syntax that's familiar to our parser and to the users + - A stream of SQL statements will be slower to run than BINLOG statements + (due to locking, table open/close, etc). (TODO: is it really slower? we + haven't checked). DESCRIPTION: One of great things about mysqlbinlog was that its output was human-readable SQL, so it was possible to edit it manually or with help of scripts. With RBR events and BINLOG 'DpiGShMBAAAALQAAADcBAA...' statements this is no longer the case. This WL task is about making BINLOG statements to be human-readable (either as an option or by default The approach of this WL is to some extent an alternative to WL#38, WL#40, WL#41. HIGH-LEVEL SPECIFICATION: Suggestion 1 ------------ Original syntax suggestion by Kristian: BINLOG WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), FROM ('a') TO ('b') FLAGS 0x0 DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0; This is basically a dump of what is stored in the events, and would be an alternative to BINLOG 'gwWEShMBAA...'. Feedback and other suggestions ------------------------------ * TIMESTAMP part is better omitted and replaced with separate SET TIMESTAMP statement for consistency with other events. * mysqlbinlog --base64-output=DECODE-ROWS --verbose already produces something that's close to readable SQL. Can we make it to be regular parseable SQL? + This will be syntax that's familiar to our parser and to the users - A stream of SQL statements will be slower to run than BINLOG statements (due to locking, table open/close, etc). (TODO: is it really slower? we haven't checked). One problem is that we do not have column names available in the binary log. * When SBR replication is used and the statements refer to the current database (a common scenario), one can use awk to filter out updates made in certain databases. The proposed syntax doesn't allow to perform equivalent filtering? ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)