Event scheduler and Galera

[somecallmemike]

I recently started experimenting with the MySQL event scheduler and I was wondering if Galera integrates with this function in the sense that events can be scheduled cluster wide.  More specifically if I schedule events on one node, can those events be failed over to another node in the event the first node failed?  

[Henrik Ingo]

Events and triggers are a bit problematic in a cluster like Galera. If
you ask whether they are supported, the answer is yes. You can
definitively create them. The problem is to try to understand what
they do when executed. Most likely your event will be replicated on
all nodes, and execute on all nodes at the same time. With interesting
results.

So my advice is to simply use a cron job or something in your
application for this purpose. This is not restricted to Galera - I
have seen a lot of mess created by using events together with MySQL
replication too.

If you were asking whether events are somehow magically cluster-wide
in a Galera cluster, then the answer is no. They exist on each MySQL
node individually. They are replicated, and the results of whatever
transactions they execute are also replicated. But using them is
probably very counterintuitive and hard to manage.

> --
>
>



--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[Jay Janssen]

On Dec 7, 2012, at 3:59 PM, Henrik Ingo <henri...@avoinelama.fi> wrote:

Events and triggers are a bit problematic in a cluster like Galera. 

I understand events, but why triggers?  Those shouldn't fire on all nodes with RBR, right?

Jay Janssen, MySQL Consulting Lead, Percona Inc.

http://about.me/jay.janssen

Percona Live in London, UK Dec 3-4th: http://www.percona.com/live/london-2012/

[Henrik Ingo]

On Fri, Dec 7, 2012 at 11:34 PM, Jay Janssen <jay.j...@percona.com> wrote:
>
> On Dec 7, 2012, at 3:59 PM, Henrik Ingo <henri...@avoinelama.fi> wrote:
>
> Events and triggers are a bit problematic in a cluster like Galera.
>
>
> I understand events, but why triggers? Those shouldn't fire on all nodes
> with RBR, right?

I don't know, but happy to take your word for it. Then what about SBR?
You can apparently force SBR through a Galera cluster too
(pt-table-checksum does that).

Also, maybe in most cases you'd expect triggers to only fire once per
cluster (on the master) but there might be people out there who expect
them to fire on each node.

So it's just an area I generally prefer avoiding.

henrik

[Jay Janssen]

On Dec 8, 2012, at 5:15 AM, Henrik Ingo <henri...@avoinelama.fi> wrote:

I don't know, but happy to take your word for it.

http://dev.mysql.com/doc/refman/5.5/en/replication-features-triggers.html

Then what about SBR?
You can apparently force SBR through a Galera cluster too
(pt-table-checksum does that).

I'm not expecting SBR to be a normal occurrence in a Galera cluster, and if anything it seems less needy to describe limitations of triggers than it does to describe the limitations of using SBR in the cluster (with triggers as a bullet point) in some manual page somewhere.  

[Henrik Ingo]

Ok, if it is indeed the case that triggers are not triggered on slaves
applying RBR, and if it is also the case for galera replication, then
I agree that triggers can work with Galera. But I think my original
point is still true: It might not be clear to most people what happens
with triggers in a cluster, and for this reasons you may choose to not
use them at all. If you do want to use them, you probably need to test
quite thoroughly to be sure you understand what they are doing.

henrik

> --