The Percona Live MySQL Conference & Expo (PLMCE) 2015 Call for Papers will close November 16 2014. It is a great place to talk shop about all things MySQL, MariaDB, and the community surrounding it. It happens April 13-16 2015, and I’m sure you’re thinking about ideas so make use of the weekend well and submit a paper, because this is a great event to attend. It’s also a good time to meet some of the MariaDB crew, whom tend to show up as speakers & attendees alike!

MariaDB 10.1 server is now “Galera ready” with the latest 10.1.1 release. It includes wsrep (write set replication) patch that enables server to load the wsrep provider (galera) library and interact with it to provide multi-master synchronous replication support. The patch implements hooks inside server and storage engines to populate and apply the write sets on sender and receiver nodes in a cluster respectively. The wsrep patch also adds a number of system and status variables (prefixed with wsrep) that can be used to configure and monitor the server acting as a node in Galera cluster.

Unlike older MariaDB versions, the wsrep patch is now part of regular (vanilla) MariaDB 10.1 server, that is, with 10.1.1 there would be no separate vanilla and Galera server versions. As a result, same 10.1 packages (binary tarballs, deb, rpm, etc.) can now be used to run MariaDB server as standalone server or a node in MariaDB Galera cluster.

As a standalone server, it would require no additional settings, just your usual favorite options. However, if one wants to start it as a MariaDB Galera node the following mandatory settings would be required :

  • wsrep_on=ON
  • wsrep_provider
  • wsrep_cluster_address
  • binlog_format=ROW
  • default_storage_engine=InnoDB
  • innodb_autoinc_lock_mode=2
  • innodb_doublewrite=1
  • query_cache_size=0

Without these additional settings the Galera replication essentially gets disabled and server functions like a standalone server with no Galera-related overhead.

If you are building MariaDB 10.1 server from source, WITH_WSREP cmake switch (ON by default) can be used to control the inclusion of wsrep patch in the build.

More about MariaDB Galera cluster:

Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case.

I’ve had one of these moments when I’ve heard about a request of making triggers to work on the slave in the row-based replication. Like, really? In RBR all changes made by triggers are replicated from the master to slaves as row events. If triggers would be fired on the slave they would do their changes twice. And anyway, assuming that one only has triggers one the slave (why?) in statement-based replication triggers would run on the slave normally, wouldn’t they?

Well, yes, they would, but one cannot always use statement-based replication. If one could, RBR would’ve never been implemented. There are many cases that statement-based replication cannot handle correctly. Galera requires RBR too. And as it turned out, that user, indeed, only had triggers on the slave — the master gets all the updates and slaves maintain summary tables that triggers keep up to date.

That’s why MariaDB 10.1.1 can now optionally invoke triggers for row-based events. This is controlled by the slave_run_triggers_for_rbr system variable. This variable is defined as

That is, it’s a global variable, it can be set to YES, NO, and LOGGING. By default it’s NO. The value of YES will, naturally, make triggers to run for RBR events. The value of LOGGING will make them to run and changes made by the triggers will be written into the binary log. This mode can be changed run-time or from the command line or a config file.

Either way, when slave_run_triggers_for_rbr is not NO MariaDB slaves will invoke specific triggers for certain row events:

  • Update_row_event will invoke an UPDATE trigger
  • Delete_row_event will invoke a DELETE trigger
  • Write_row_event is a bit tricky. It is applied (yes, in MySQL too) as follows:
    • The slave tries to insert a row.
    • If the table has UNIQUE KEY constraints (or a PRIMARY KEY) and there is a conflicting row — it’ll be updated to have all values as in what should’ve been inserted.
    • But if the table also has FOREIGN KEY constraints or there are other UNIQUE keys in the table, old row will be deleted and new row will be inserted. Two operations instead of one, so it’s slower, but guarantees that there will be no references to the old row after it disappears.

    That is, Write_row_event can invoke INSERT trigger, DELETE trigger, or UPDATE trigger, depending on whether a conflicting row exists, which UNIQUE constraint was violated and whether a table was referenced in a foreign key constraint. If you think it’s too complex — you’re right, we’ll simplify it in 10.1.2.

In setups like this, particularly if one replicates further, these slaves being masters to some other slaves, there is a risk that triggers will be run multiple times for the same row. To detect this kind of mistakes, MariaDB marks row events that have invoked triggers, and when these events are replicated, they won’t cause further trigger invocations on slaves. That is, the master must not have any triggers on tables in question, otherwise slave-side triggers will not be invoked.