Hi! I've collected the suggestions below and filed: MWL#45 Add a mysqlbinlog option to produce succint output MWL#46 Change BINLOG statement syntax to be human-readable MWL#47 Store in binlog text of statements that caused RBR events --SergeyP. On Fri, Aug 14, 2009 at 03:16:29PM +0200, Kristian Nielsen wrote:
Michael Widenius <monty@askmonty.org> writes:
Hi!
> "Kristian" == Kristian Nielsen <knielsen@knielsen-hq.org> writes:
Kristian> Sergey Petrunya <psergey@askmonty.org> writes:
I've worked through the list and filed
MWL#39 Replication tasks
and its subordinate tasks:
MWL#36 Add a mysqlbinlog option to change the used database MWL#37 Add an option to mysqlbinlog to produce SQL script with fewer roundtrips MWL#38 Make mysqlbinlog not to output unneeded statements when using --database MWL#40 Add a mysqlbinlog option to filter updates to certain tables MWL#41 Add a mysqlbinlog option to filter certain kinds of statements
Kristian> One idea that occured to me and that I would like to bounce off of you others Kristian> (while I still continue thinking more about it and also working on the Kristian> original proposals):
Kristian> In some sense the root of the problem is the magic "BINLOG" statement, which Kristian> really is not very nice. It is much much harder than it should be to see from Kristian> the binlog what data is actually changed from row-based events.
Kristian> If the BINLOG statement was using proper syntax showing the actual data Kristian> changes, then the original customer problem would likely be solved as they Kristian> could treat it the same way as statement-based replication events. This is Kristian> especially nice when one considers that even row-based replication uses lots Kristian> of statement-based events, not to mention mixed-mode replication.
Kristian> But readable BINLOG statements would be very nice in any case, and solve a Kristian> much more general problem than these worklogs, in an arguably nicer way.
Note that it would also be very nice to have in the binlog the exact original statement:
Yes. There was actually a customer request for this.
I think for this we would actually need a new binlog event type (Comment_log_event?). Unless we want to log an empty statement Query_log_event containing only a comment (a bit of a hack).
- Easier to understand why updates happened. - Would make it easier to find out where in application things went wrong (as you can search for exact strings) - Allow one to filter things based on comments in the statement.
The cost would be to have an approximately 2 as big binlog. (Especially insert of big blob's would be a bit painful).
Yes. So should maybe be optional.
Kristian> BINLOG Kristian> WITH TIMESTAMP xxx SERVER_ID 1 MASTER_POS 415 FLAGS 0x0 Kristian> TABLE db1.table1 AS 1 COLUMNS (INT NOT NULL, BLOB, VARCHAR(100)) FLAGS 0x0 Kristian> TABLE db2.table2 AS 2 COLUMNS (CHAR(10)) FLAGS 0x0 Kristian> WRITE_ROW INTO db1.table1(1,3) VALUES (42, 'foobar'), (10, NULL) FLAGS 0x2 Kristian> UPDATE_ROW INTO db2.table2 (1) (1) VALUES FROM ('beforeval') TO ('toval'), Kristian> FROM ('a') TO ('b') FLAGS 0x0 Kristian> DELETE_ROW INTO db2.table2 (1) VALUES ('row_to_delete') FLAGS 0x0;
Kristian> This is basically a dump of what is stored in the events, and would be an Kristian> alternative to BINLOG 'gwWEShMBAA...'.
Kristian> So what do people think?
The above would be a much better option than using the current syntax.
Kristian> The implementation of this is not necessarily all that much harder than the Kristian> suggested worklogs under MWL#39 I think. The server upon reading this would Kristian> just reconstruct the binary representation of the binlog and proceed as the Kristian> old BINLOG statement. The mysqlbinlog program would just print out the Kristian> fields. The main complications are the addition of syntax to the Bison grammer Kristian> plus the need to handle all the possible types.
Kristian> If we choose the easiest option in all the MWL#39 subtasks that would probably Kristian> be somewhat easier. On the other hand this would be a much more generally Kristian> useful feature, and would make trivial a lot of the suggested modifications to Kristian> mysqlbinlog.
Which of the original customer problems would the above solve ?
The original customer problem was that they have a working solution for SBR using some custom awk post-processing of the mysqlbinlog output. But this solution can not be adapted for RBR, as the BINLOG 'xxx' statements are impossible (or very close) to do anything with.
Having a readable BINLOG statement would allow them to adapt their awk script to work with RBR events as well. Since my understanding was that they have a working solution for SBR now. So they might decide they do not need any new mysqlbinlog options at all.
I agree it does not do anything solve the general problems with using regexp post-processing to filter/modify mysqlbinlog output for SBR.
You could say that the general method of awk post-processing the mysqlbinlog output becomes more usable when it works for RBR as well, reducing the need for special filtering and rewrite options for mysqlbinlog. But such options would in any case be useful to simplify use cases.
For exampling, doing general rename of databases wouldn't be much easier to do with the the above syntax (as we still need to handle SBR).
Kristian> I'm pretty sure this would be easier than some of the harder options in the Kristian> MWL#39 subtasks.
Which one are you thinking about ? (I like the proposed syntax, but don't grasp why things would be simple when doing this ).
I was thinking of things like embedding a full SQL parser into mysqlbinlog, option 2.1 in MWL#40.
But I think I was confusing things a bit, since as you pointed out this solves a somewhat different problem. The point is more that this is something that may allow the customer to solve their original problem in a different way than proposed.
- Kristian.
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog