maria-developers team mailing list archive

[Michael Widenius <monty@xxxxxxxxxxxx>]

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> Thanks, Sergey! I've started to work my way through them and the many
Kristian> associated issues to understand for this.

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:

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

Kristian> There are also a number of potential problems that need careful consideration
Kristian> of course. I haven't yet fully thought things through, just wanted to throw up
Kristian> the idea.

Kristian> I went throught the code for RBR events, and came up with the following
Kristian> possible syntax:

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 ?

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> So I need to think a bit more about this to make up my mind if I like it. But
Kristian> I think at least it is a very interesting idea.

Kristian> Comments?

Kristian> (Also, isn't it time to move the discussion to the public
Kristian> maria-developers@xxxxxxxxxxxxxxxxxxx list?)

Done.

Regards,
Monty