maria-developers team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Michael Widenius <monty@xxxxxxxxxxxx> writes:

> Hi!
>
>>>>>> "Kristian" == Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx> writes:
>
> Kristian> Sergey Petrunya <psergey@xxxxxxxxxxxx> 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.