mozilla/browserid — Issue #561: handle mysql master failover in dbwriter process
Author: fetep
Created: 2011-11-10 22:49:14+00:00
State: open

When we failover the prod db master, there will be a new IP/hostname to connect to (for dbwriters), and we should direct further queries there. The overall process looks something like:

* stop writes to the old master, let slaves catch up (ops: set global read_only=true)
** This will cause errors like "ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement".
** Note that this could happen mid-transaction, which will mean the entire transaction does not get committed.
** If one dbwriter http request maps to multiple SQL statements, we may want to wrap them in a commit so we can return a 500 on failure, and not end up with data half-way-there. (e.g. user signing up, we insert the user row, but none of the email rows).
** We might want to disconnect immediately on a 1290 error and wait for a new master (or wait some maximum amount of seconds before retrying the same host)
* behind-the-scenes work to switch around replication
* dbwriters get notified (or poll some location) of the new master, and reconnect there
* puppet pushes a new dbwriter config that sets the appropriate master

There are two failover scenarios:
* planned, where the master will be up and you'll get the read-only errors
* unplanned, where the master will blow up and connections will die, fail, or hang

We could publish the master in an administrative table in the browserid database, so the dbwriter would keep two sql connections open (one read-only to the Zeus MySQL VIP) and periodically poll a table ("select master from foo") or periodically poll a local memcache instance where we set a key to the current master. On dbwriter start, we should assume the master in the config file is right. If we go to poll memcache (or whereever) and there's no master listed, we shouldn't fail and try to connect to an empty hostname -- just keep using the existing connection. We should only take action if we read a non-null non-zero-length hostname from memcache/special table/whereever and it doesn't match our current master hostname.

Open to ideas on how we get the dbwriters talking to the new process. We could just push a new config file and do a node restart, too (this might be a little slower, though).

(note: if we were just one datacenter, we could have a floating VIP we move between masters so the client always has one place to go for db writes. since we're going for multi-site, this is a bit trickier. it may be an option, need to talk to netops.)