[Maria-developers] MWL#39 improving mysqlbinlog output and doing rename
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> 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@lists.launchpad.net list?) Done. Regards, Monty
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.
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
Hi!
"Kristian" == Kristian Nielsen <knielsen@knielsen-hq.org> writes:
<cut>
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. <cut> 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.
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. <cut> 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 reference. 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. Regards, Monty
Michael Widenius <monty@askmonty.org> 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.
A Statement is logged in RBR as one or more Table_map_log_events, followed by one or more {Write,Update,Delete}_rows_log_event. So candidates would be the first (or last) map event, or the first row event. But the event format really is not very extensible (I checked the code), so not trivial to make backwards compatible. Anyway, it should be possible, new event or squeeze into existing, whatever works best.
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...'.
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.
Yes, my main concern too. Though I don't think there will be much addition to the format, as this would in any case break forwards/backwards compatibility in replication. But still a valid concern.
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 reference.
Yes, using awk (or whatever) is quite fragile (though still useful in many cases). For databases mysqlbinlog can do better, and for tables in RBR, as the names are directly available in the binlog format. For tables in SBR, or type of statement (ALTER/ANALYZE/etc.), mysqlbinlog can do slightly better since it knows where the statement begins, but it still has to rely on some kind of parsing. So conclusion: it is preferable to have 100% reliable database/table/statement filtering than having to rely on fragile awk-like post-processing. Thanks for the input! - Kristian.
I read throught the worklogs, did some research, and wrote up a number of comments. I am not sure about the best way to proceed, but this is my suggestion. I have written up in this email all my comments in detail. After any discussion, I suggest I update the worklogs as per the conclusions (though if Sergey wants to do that I am happy with that as well). As you will see, this results in a huge email :-( To help this problem, I will write a separate email, laying out the possible options to be chosen between without technical detail, along with guestimates of time needed to implement. ----------------------------------------------------------------------- General comments: 1. RBR vs. SBR. A fundamental decision in several of the worklogs is whether to do something for RBR only, or something that works for both SBR and RBR. Clearly it is preferable to have the features work the same for both RBR and SBR, but it also rather more difficult to implement. There are already a lot of subtle differences between SBR and RBR in the existing similar options for master and slave, motivated by this difficulty I assume. And the problem is even harder to solve in mysqlbinlog, as we lack information available inside the server (table definitions, parsed SQL statement). There is a need to choose between ease of implementation and completeness of feature. This makes it particular important to be consistent in the choices between the different worklogs. We don't want to make the choice one way in one worklog and the other way in another, as this would cause even more confusion for the user. 2. Quoting semantics. Several of the worklogs require the parsing of table names, eg. from command line options or comments. This is a non-trivial problem, as backtick quotes `...` allow more or less any character in database or table names. There may also be issues of charater sets. For example, this is accepted: mysql> create table `'db.table'` (a int); In the RBR events, the names are stored as bytes prefixed with length, so there is no issue of parsing. I assume the character set implied is utf8, though I need to check. In the command line options like --binlog-rewrite-db=db1->db2 and --binlog-do-table=db.t1, we need to decide what to do. I think it is reasonable to not support everything, as long as the semantics of what the option does is clear. For example, assume table names are given in utf8 (irrespectively of locate), and give an error if special characters like "." or "->" is given in the names. Alternatively, we could try to support the full set of valid database and table names, and character sets. Then mysqlbinlog would consult the locale and convert to utf8. And we would need to implement a parser for quoted names, so one can say --binlog-do-table=`'my.strange.db'`.`"my-wierd-table"`, or --binlog-rewrite-db=`a<->b`->`A>-<B'. One disadvantage is that this (probably?) differs from existing --replicate-do-table etc. options. For MWL#40 option 2.3, there is the suggestion to list tables in a comment: /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... In this case the problem is a bit more serious, as strange table names that are not handled correctly could make the binlog totally corrupt (eg. comment end */ in the names). I am really not comfortable with deliberately implementing such a bug, even if it is unlikely to cause any issues in "normal" cases. I am actually not sure about the character set implied for an SBR event. I think maybe it is given by values of system variables, which would make the problem somewhat harder to solve fully. Or maybe statements are converted to utf8 before being logged. I need to check. One option is to give an error if the option to add the comments is enabled and any table names are strange. Not a 100% clean solution, but simple and maybe acceptable. Alternatively we could implement full quoting in the master, and full parsing of quoted names in mysqlbinlog. Though I still need to do more research to know how to handle character set. 3. Option names. I think the mysqlbinlog command-line option names should not begin with --replicate, as they do not really affect replication, though they are of course related to the slave --replicate-* options. Maybe just drop the --replicate prefix, eg. --do-table, --do-table-wild, --rewrite-db, etc. This is consistent with the existing --database option of mysqlbinlog, which has no --replicate prefix. 4. Adding too many options. One thought reading through these worklogs is that we need to be careful that we do not start adding seperate options for every conceivable filtering of the mysqlbinlog output. If this would happen, we should instead add a general way to do this. Certainly fintering options on table and database name is fine. Filtering of statement types (ALTER/ANALYZE/...), MWL#41, is starting to smell a little bit like too much complexity, though maybe it is ok. Something to keep in mind. 5. Security issues for SQL SECURITY INVOKER stored procedures. Some of the worklog options involve general mechanisms to ignore or rewrite certain SQL statements, eg. rewriting one database name to another in MWL#36. This interacts in a problematic way with stored procedures defined with SQL SECURITY INVOKER semantics. Such stored procedures run with the privilege of the user who created them, even if run by users with less privileges. This means that if the statement rewriting applied to code in stored procedures, this could lead to users being able to manipulate such stored procedures to modify different tables that should not be accessible, circumvent logging/auditing, etc. Similar problems may apply to stored functions, triggers, and views, need to check. Something needs to be done to handle this. One option might be to make such statement rewriting have no effect inside stored procedure executing code. Another option would be to restrict such rewriting to the SUPER privilege, though that would somewhat restrict the added mysqlbinlog functionality. ----------------------------------------------------------------------- MWL#36. Add a mysqlbinlog option to change the used database Option 1: Implement --replicate-rewrite-db in mysqlbinlog. This is simple to implement for RBR. The problem is it is hard to implement consistent behaviour for SBR without having the parsed SQL statement. We can do something similar to --replicate-rewrite-db, but it will still apply only to the 'current' database for SBR, while for RBR it will apply both to current database and explicitly mentioned database. See also general comment 1, quoting semantics. Option 2: Add database-agnostic RBR events and --strip-db option I think this is fairly similar to option 1 in terms of end user experience. It will have the same problems of inconsistency between RBR and SBR for current database. It does not support multiple database rewrites (though option 1 also does not work with multiple rewrites for SBR, only for RBR). I prefer option 1, it feels cleaner, is probably simpler to implement (no need to change the server), and I think it solves more or less the same set of end user problems. Option 3: Server-side database rewrite support. This is a general server feature to allow rewriting one database name to another. Some good points about this option: - It is inside the server, so the parsed SQL statement is available and easier to be consistent between RBR and SBR. - It is actually a generally useable feature, not restricted to mysqlbinlog output. I do not like the suggested syntax: /* !database-alias(oldname,newname) */ Why a magic comment? Why not a proper SQL statement? Better to use for example a session variable: SET SESSION rewrite_database "db1->db1a,db2->db2a" This also makes it clear what the scope of the rewrite is, and how to change the mapping. It does have the quoting semantics issue (see general comment 2). The quoting issues could be solved by adding a new kind of statement, like for example: DATABASE ALIAS db1 AS db2, `strage...db` AS `sanename`; But then we have to define the scope of the statement, and I think maybe this is too small a think for adding a new statement. But comments welcome. In any case, I strongly prefer system variable or new statement to magic comment. Security concerns regarding stored procedures apply (general comment 5). ----------------------------------------------------------------------- MWL#37: Add an option to mysqlbinlog to produce SQL script with fewer roundtrips This seems straight-forward enough to implement as described. ----------------------------------------------------------------------- MWL#38: Make mysqlbinlog not to output unneeded statements when using --database Should probably check carefully if there are other statements than COMMIT or SET INSERT_ID that can appear without being useful, and should be eliminated along with these two. Otherwise, this seems straight-forward enough to implement as described. ----------------------------------------------------------------------- MWL#40: Add a mysqlbinlog option to filter updates to certain tables Implement in mysqlbinlog similar options to --replicate-do-table=db.tbl --replicate-ignore-table=db.tbl --replicate-wild-do-table=pattern.pattern --replicate-wild-ignore-table=pattern.pattern (see general comment 3 regarding option names). RBR is simple to do (option 2.0). Main issue is how to handle SBR. Option 2.0: RBR only. This should be easy to implement. Main problem is inconsistency with SBR, see general comment 1. Option 2.1: Embed the SQL parser. There are a number of things that makes this attractive. However, I think it is outside the scope of this particular project (unfortunately). This option would need to be split up in multiple worklogs, the first of which concern(s) removing dependencies in the parser from the rest of the server code. (I don't think embedded server is a solution, as it would probably require having table .frm files, which are not available in mysqlbinlog). Option 2.2: Use dumb regexp match This option I do not like. It basically amounts to re-implementing part of the parser, in a flawed way. It will not work in all cases, and it will be very hard for the user to understand exactly when it works and when it does not. For something like this, I would prefer to implement readable BINLOG statement (MWL#46), and have the users make their own regexp. Then at least they can know the limitations. But a robust solution is still to be prefered. 2.3 Have the master put annotations /* !mysqlbinlog: updates t1,db3.t2 */ UPDATE t1 LEFT JOIN ... Quoting semantics is an issue, see general comment 2. I think this should be a server option, off by default, as it is putting redundant information into the binlog. I need to check if the necessary information is available in the server when binlogging, or how it can be made available. The docs for --replicate-wild-do-table says: "Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns" So to be consistent with this, I think for multi-statements like DELETE t1 FROM t1, t2 WHERE <condition>; UPDATE t1, t2 SET t1.a = 1 WHERE <condition>; only t1 should be in the table list in the comment, not t2. 2.4. Implement server functionality to ignore certain tables This is similar to option 3 of MWL#36, so would make sense to choose or reject these together. SET SESSION ignored_tables = "db1.t1,db2.t2"; Again, quoting semantics is an issue (general comment 2). As with option 3 of MWL#36, another possibility is adding a seperate, new SQL statement for this, and again it maybe seems a bit much to add a separate statement for something like this. Security concerns regarding stored procedures apply (general comment 5). I vaguely prefer 2.4 to 2.3 due to being more general and not adding redundant info (which seems a bit hackish); also for consistency with option 3 of MWL#36 (if we choose that one). ----------------------------------------------------------------------- MWL#41: Add a mysqlbinlog option to filter certain kinds of statements Option 1: Embedded parser. (same comment as option 2.1 of MWL#40). Option 2: Partial parsing This amounts to re-implementing part of the parser. I generally do not like the idea of implementing a partial parser from scratch, as it will be inherently error prone to get it to be, and keep it in, 100% sync with the real parser. It will be quite hard to avoid missing one or two special cases where we will miss ignoring a statement in mysqlbinlog due to some special syntax not handled correctly. That being said, it seems this particular case actually could be handled more or less correctly with some effort, as it is not too complex. Basically, it seems the statement is always identifiable from first 1-2 keywords. Still there are issues to handle, like whitespace (might differ depending on character sets, need to check if SBR logs in client charset or utf8), comments, conditional execution /*!40101 <statement */, etc. Option 3: Server-side support. SET SESSION ignored_statements="alter table, analyze table, ..."; The nice thing about this option is that the parsed SQL statement is available. Security concerns regarding stored procedures apply (general comment 5). If we go for option 3 of MWL#36, it would make sense to choose this one as well for consistency. ----------------------------------------------------------------------- - Kristian.
Guestimates (hours), including low-level design, implementation, test cases, review, post-review fixes, and documentation. Note that without low-level designs, and with no prior experiences with estimating MariaDB work, these are little more than wild guesses: MWL#36: Option 1: 12+24+5+2+4+2 = 49 Option 2: 8+24+5+3+3+4 = 47 Option 3: 9+22+9+4+3+5 = 52 MWL#37: 6+12+4+2+2+4 = 30 MWL#38: 8+17+4+2+2+4 = 37 MWL#40: Option 2.0: 4+11+4+2+2+4 = 27 Option 2.1: Not estimated. Option 2.2: Not recommended. Option 2.3: 14+22+3+3+5 = 47 Option 2.4: 14+25+7+4+4+5 = 59 MWL#41: Option 1: Not estimated. Option 2: 20+25+15+3+6+8 = 77 Option 3: 10+15+7+3+6+4 = 45 I don't think all of the options are independent, some of them should be chosen together for consistency. Eg. MWL#36/3 and MWL#40/2.4 and MWL#41/3. Here are some suggested overall choices: 1. Minimal/RBR only. MWL#36/1 + MWL#40/2.0 + MWL#41/2 2. Partial SBR support, but with some inconsistencies: MWL#36/2 + MWL#40/2.0 + MWL#41/2 3. Complete SBR support, with server-side support: MWL#36/3 + MWL#40/2.4 + MWL#41/3 - Kristian.
participants (3)
-
Kristian Nielsen -
Michael Widenius -
Sergey Petrunya