Thursday, February 07, 2008

Statement-based replication for Falcon

I just read the post from Mark regarding some questions he had about the Falcon engine. One of the points that made me jump is the following:
Row-level replication (instead of statement-based replication) is required when replicating Falcon objects.

This might be enough to keep me from upgrading, but I am not sure if this is limited to Falcon. Will future MySQL releases require the use of row based replication? Having SQL statements in the binlog is invaluable to me and I am not willing to give that up.

Now, seriously, it is not trivial to disable statement-based replication for any engine so it is basically always on; whether it works as expected is a different story. So, in short: there is nothing specifically done to disable statement-based replication for the Falcon engine. It is just not supported by them (yet).

You can actually use statement-based replication to replicate any tables using any engine, Cluster tables as well. It is not a good idea to replicate Cluster tables for several reasons, but it is possible.

We will continue to maintain statement-based replication for the foreseeable future, but it is just not possible to handle all the quirks that can occur in odd situations (for some examples, see BUG#31168, BUG#3989, and BUG#19630). If you know how to write queries that avoid problems like these, you will not have any problems, but if you are concerned about whether you're up to it, switch to use row-based replication.

5 comments:

pabloj said...

Did you check why he needs statement based replication?
A questionable reason IMHO

Mats Kindahl said...

Not at all, it is a very valid reason since statement-based replication is very efficient if the circumstances is right.

Mark knows intimately how replication works, knows how to use it correctly and efficiently, and have the resources necessary to use it correctly.

Tobias said...

You can actually use statement-based replication to replicate any tables using any engine, Cluster tables as well.

Will things like this be fixed not to give an error, then? :)


mysql> CREATE TABLE t1 ( a INT ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> SET SESSION binlog_format=STATEMENT;
Query OK, 0 rows affected (0.01 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1);
ERROR 1595 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

Mats Kindahl said...

Ah, good catch. :)

As noted, it is possible for an engine to disable replication, and this situation is when InnoDB has decided to not allow replication.

By setting the isolation level properly, you can however replicate changes to InnoDB tables as well.

Nilesh Jethwa said...

Hi,
I have been working on an MySQL DBA Dashboard. The Dashboard currently has basic information from the information schema.

Would like your opinion to improve and make it useful, for e.g to add DB performance metrics etc, but not sure where this information would be available.

Your opinion is highly regarded and will really appreciate if you could point me to some resources on this.
( njethwa @ gma!l . com)
Regards
Nilesh
MySQL Dashboards