![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
knielsen@knielsen-hq.org writes:
message: Merge Percona patch MWL#47 into mariadb 5.2-percona.
This patch adds options to annotate the binlog (and the mysqlbinlog output) with the original SQL query for queries that are logged using row-based replication.
Serg raised the very relevant issue about what to do about the similar feature in MySQL 5.6: http://forge.mysql.com/worklog/task.php?id=4033 http://forge.mysql.com/worklog/task.php?id=5404 The Oracle team has worked more on this since last I looked, and the two features are now very similar. Here are the main differences, as I see it: - The MySQL 5.6 feature is enabled by --binlog_rows_query_log_events, while the MWL#47 option is called --binlog-annotate-rows-events. - MWL#47 has --replicate-annotate-rows-events which makes the slave receive and binlog the annotations. The MySQL feature always receives annotation events if present in the binlog (so replication 5.6->pre-5.6 will break if annotations are enabled). The MySQL feature binlogs the annotations based on the same --binlog-annotate-rows-events option as the master. - MWL#47 has a mysqlbinlog option --skip-annotate-rows-events, MySQL 5.6 does not. - MySQL 5.6 mysqlbinlog can log the annotations with BINLOG statements so that they are preserved with `mysqlbinlog | mysql`; MWL#47 does not have this. - MWL#47 uses binlog event type 160, MySQL 5.6 uses an ignorable event of type 29. As far as I can tell, the actual event format is identical, except for the LOG_EVENT_IGNORABLE_F flag (which says it's ok for a slave to ignore this event if it doesn't know about it, however there is no server version that knows the LOG_EVENT_IGNORABLE_F but not the MySQL annotation event). - I think there are a couple of issues with the MySQL patch (newlines in queries breaking mysqlbinlog output, duplicated annotations for INSERT DELAYED). There might be issues with the MWL#47 patch also, of course. So the question is how to prepare for when we merge MySQL 5.6. An easy option could be to just ditch MWL#47 (and possibly backport the MySQL 5.6 patch). However, as I understand it, the original sponsor of MWL#47 is already using it, and so we may have to remain compatible with the existing MWL#47 implementation. Can we get some definite information on that? Another option might be to simply change the MWL#47 to use the same event type as MySQL (the event format is the same, and we can add the LOG_EVENT_IGNORABLE_F). And replace the MWL#47 options --binlog-annotate-rows-events and --replicate-annotate-rows-events with the MySQL --binlog_rows_query_log_events option (though I think the MySQL option name is not good). But again, I am not sure if this is a valid option given possible existing usage of MWL#47. The third option I can think of is to leave MWL#47 as it is. Then when we get to merge MySQL 5.6, we will basically drop the MySQL part, doing the following to remain backwards compatible with MySQL: - Make the --binlog_rows_query_log_events option set --binlog-annotate-rows-events and --replicate-annotate-rows-events. - Make a slave read a MySQL rows_query_log_event as a MWL#47 annotation event. - Merge over the bit of the MySQL patch that makes mysqlbinlog output annotation events using a BINLOG '...' statement. This should make upgrade from MySQL 5.6 -> MariaDB fully work. Using MySQL5.6+ with MariaDB will also work, only MySQL slaves will not receive MariaDB master annotation events. The third option seems to preserve the existing MWL#47 most faithfully and does not seem to need much work in terms of the eventual merge, so this is what I suggest as the best way forward. But I am open to other (justified) opinions. - Kristian.