Mike Orr wrote: > On Nov 8, 2007 8:06 AM, Jonathan Share <[EMAIL PROTECTED]> wrote: >> I currently have 2 database servers set up in a master-master >> replication set up and wish for the pylons app to fail over to the >> second database server when the first goes down. > > By master-master you mean each database updates the other when a > change comes in? How did you structure that? I wanted to do that for > my sites with two database servers in different states (each talking > to a local web application), but it looked like it would require major > database restructuring to keep track of which record was "more > accurate" in case one of the servers went down and then had to > automatically synched with the other. I gave up because I didn't want > it accidentally synching the wrong way and overwriting correct data.
This is a potential problem with a master-master set up. Fortunately my app has the advantage that there are very few update queries, the primary content on my site is versioned so to change it you add a new record rather than update an existing one. Past that it's user comments, user ratings and download statistics which are all inserts so we have very few updates going on. The updates that do happen are either things that are very unlikely to happen at the same time on different servers (eg. user editing a comment) so I'm happy that "last write wins" or are aggregate fields that are denormalised (download count, total rating) and so are recalculated from the normalised table so is harmless if it "synch"s the wrong way. > > When I looked at the MySQL docs, it only talked about master-slave > replication, which wouldn't help if the master goes down and the slave > has to become the master temporarily, which was the whole point of > having a second database in the first place. FWIW this was the article I used for beginning with Master-Master replication. http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html > >> I hope this is a stupid question but I'm trying to work out how to >> configure a database connection in pylons to use a database failover. > > It's not stupid, it's just... the only thing about SQLAlchemy we > maintain is the Pylons-SQLAlchemy interface. This is not an interface > issue (did the query get to SQLAlchemy? are concurrent requests > stomping on each other's connections? how do you access the model > from a unittest or standalone script?), it's an advanced database > question that requires the experts on the sqlalchemy list, not this > one. Not putting you down for asking, just point out that you'll get > a better answer there. Good point, my bad. I'll sign up to the SA mailing list and fire up the thread there next week instead. > >> I'm running Pylons 0.9.5 with SqlAlchemy 0.3 and mysqldb but a solution >> for 0.9.6 or SA 0.4 would be sufficient for pointing me in the right >> direction. >> >> In Java I would achieve this with a connection string that looks like >> the following, >> >> jdbc:mysql://db1.example.com,db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8 > > You can't set up a double engine in SQLAlchemy. You could set up two > separate engines and bind to one or the other at any time -- the > SQLAlchemy manual and "SQLAlchemy 0.4 for people in a hurry" have some > thoughts on distributed databases (parallel/sharding) -- but it's not > 'automatic' so it's not quite what you want. > > However, your idea for a double engine that automatically falls back > to the second one might be a worthwhile enhancement for SQLAlchemy, > especially if you point out that Java does it and has a reference > implementation. > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
