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 <mo...@askmonty.org> writes:
>
> > Hi!
> >
> >>>>>> "Kristian" == Kristian Nielsen <kniel...@knielsen-hq.org> writes:
> >
> > Kristian> Sergey Petrunya <pser...@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
_______________________________________________
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
