SELECTs can cause writes - if they require temporary tables to resolve. This is quite often the case in MySQL, it being none too bright about how it resolves complex queries (particularly ORDER BY queries where no index exists). This is why master-master can be very tricky.
The only good master-master (well, technically, elected-master-shared-datastore) replication models for failover I've seen have been either implemented by fairly complex middleware (sitting between SQL servers and your app) or in nonrelational database designs like Riak and MongoDB which use forms of arbitrated master selection with any node being able to become the master. In a MongoDB based setup you could run MongoDB on each machine and every machine has a full DB copy, primarily using your usual DB server for writes but if that falls over, any machine running a MongoDB server can be elected the new master. Of course, this sort of system is then vulnerable to split-brain and all the other fun things we get for free with elected-master setups. Of course, that doesn't solve how you make the audio store redundant, but the audio store is less atomically vulnerable to inconsistency. So long as DB atomicity is maintained, the audio store should be fine... I think. Sadly I'm unaware of a similar system to the above available for MySQL outside of really nastily complex middleware (which does some of the above) and awkward master-master replication fakery (which does very little of the above). It's a shame the MySQL stuff is so tightly integrated in RD throughout the codebase; pulling out all that to an abstraction layer might make life easier for porting to new DBs and implementing stuff like this. Cheers, James Harrison On 30 April 2012 00:56:27, Fred Gleason wrote: > On Apr 27, 2012, at 13:06 34, Wayne Merricks wrote: >> Just wondering if this is something anyone has seriously thought about? > > Very much so. In fact, implementing precisely these features (fully > automatic and seamless failover and recovery) for both the DB and audio store > are top-of-the-list goals for RD 3.x. > > >> In theory I could set up a multi-master MySQL between the server and >> critical studios. You can offset auto-numbers to alleviate race conditions >> on record inserts however I don't know enough about MySQL to know how it >> handles updates/deletes and how well/badly it recovers from a network loss. >> >> Problems like this make me think multi master is a bad idea for Riv. > > On the contrary, I think master-master replication is a perfectly viable > approach. The trick is to ensure that any active RD systems are using only > one of the pair for INSERT/SELECT queries at any one time. Basically, this > means a 'watchdog' process on each MySQL instance with redundant heartbeat > links to all other watchdogs, along with logic to elect a one-and-only > 'online master' and transmit that information to the clients in a timely > manner. Both failover and recovery are easy then -- just change the current > 'online-master'. > > >> I had a custom program made for call handling (really basic) a few years ago >> and when I set up a simple slave replicator I found that I couldn't do >> things like ordered SQL selects with limits e.g. ORDER BY id DESC LIMIT 1 as >> it broke the replication. > > Very strange -- how would SELECT queries break replication? Those don't even > go in the bin-log. > > >> /var/snd: >> >> Have a "drop box" share on the server that is nothing to do with Riv. When >> this share receives files a daemon script copies it into the server /var/snd >> via rdimport. The output of this gives you the cart number (and as such the >> file name the cart has). After the import has finished the script then >> copies the file to each critical clients /var/snd. >> >> Would it be easier to just do snapshot updates once an hour (rsync and >> MySQL) and have some sort of script that tweaked rd.conf and kicked airplay >> whenever it noticed there was a network outage? Any DB activity during the >> outage would kill the slave replication so I'd probably have to >> script/manually repair the damage before replication continued. > > Both of these approaches contain races that will bite a user sooner or later. > > Cheers! > > > |-------------------------------------------------------------------------| > | Frederick F. Gleason, Jr. | Chief Developer | > | | Paravel Systems | > |-------------------------------------------------------------------------| > | To invent, you need a good imagination and a pile of junk. | > | -- Thomas Edison | > |-------------------------------------------------------------------------| > > _______________________________________________ > Rivendell-dev mailing list > [email protected] > http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev _______________________________________________ Rivendell-dev mailing list [email protected] http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev
