← Back to team overview

maria-developers team mailing list archive

Re: MWL#39 improving mysqlbinlog output and doing rename



>>>>> "Kristian" == Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx> writes:


>> Note that it would also be very nice to have in the binlog the exact
>> original statement:

Kristian> Yes. There was actually a customer request for this.

Kristian> I think for this we would actually need a new binlog event type
Kristian> (Comment_log_event?). Unless we want to log an empty statement Query_log_event
Kristian> containing only a comment (a bit of a hack).

I don't think it would be hard to add a tag to the row-binlog-event to
add the original statement in such a way that it would be backward
compatible.  Someone should dig into the protocol spec to see if that
would be possible.


Kristian> BINLOG
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.

After some thinking, my biggest problem with the above is that we
would have to keep the syntax up to date and extend it for every new
addition Sun makes to the binary log.  Over time that may become a big
task in itself.


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 ?

Kristian> The original customer problem was that they have a working solution for SBR
Kristian> using some custom awk post-processing of the mysqlbinlog output. But this
Kristian> solution can not be adapted for RBR, as the BINLOG 'xxx' statements are
Kristian> impossible (or very close) to do anything with.

Kristian> Having a readable BINLOG statement would allow them to adapt their awk script
Kristian> to work with RBR events as well. Since my understanding was that they have a
Kristian> working solution for SBR now. So they might decide they do not need any new
Kristian> mysqlbinlog options at all.

The AWK script the user used was mostly to remove some statements from
the log, not to modify the statements.  If the MySQL slave suppression
rules would work properly and there where an option to remove some
kind of statements (like analyze table) the AWK would not be needed in
this case.

The problem with AWK is that it doesn't work in the general case; For
the general case to work, they would need to parse the statements into
token and ensure that they only modify things like the database

In other words, we should assume that AWK or any replacement in a
script is not a viable solution for the long term and we need to solve
this in another way.

Kristian> I agree it does not do anything solve the general problems with using regexp
Kristian> post-processing to filter/modify mysqlbinlog output for SBR.

Kristian> You could say that the general method of awk post-processing the mysqlbinlog
Kristian> output becomes more usable when it works for RBR as well, reducing the need
Kristian> for special filtering and rewrite options for mysqlbinlog. But such options
Kristian> would in any case be useful to simplify use cases.

This would break down for any insert of a blob that includes things
that would match the regexp.

>> 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 ).

Kristian> I was thinking of things like embedding a full SQL parser into
Kristian> mysqlbinlog, option 2.1 in MWL#40.

Yes, that would be hard, but don't think we have to do this to solve
the current problems.

Kristian>  But I think I was confusing things a bit, since as you pointed out this
Kristian> solves a somewhat different problem. The point is more that this is something
Kristian> that may allow the customer to solve their original problem in a different way
Kristian> than proposed.


Follow ups