On Thu, Sep 17, 2009 at 9:56 AM, Kelly Jones <kelly.terry.jo...@gmail.com> wrote: > On 9/17/09, Simon Slavin <slav...@hearsay.demon.co.uk> wrote: >> >> On 17 Sep 2009, at 4:54pm, Kelly Jones wrote: >> >>> I want to do multi-master sqlite3 replication by editing sqlite3.c to >>> log UPDATE/INSERT queries with timestamps, and then using another >>> program to run those queries on the other masters.
Quick comment; timestamps are not likely to be robust ... I would add a "version" column and synchronize against that. When it comes to backups, one likes reliability; I personally wouldn't be confident with a recompiled sqlite. Plus how do you distribute the new binaries to the, presumably up and running, replication system without hick-ups? Most likely you are going to change your implementation (schema changes not mentioned yet) Stephan >> Doesn't work. Consider: You have a database with three records: >> >> ID object colour >> -- ------ ------ >> 1 car red >> 2 coat black >> 3 hat green >> >> In database copy number 1 someone does >> >> UPDATE objects SET colour='green' WHERE id=1 >> >> In database copy number 2 someone does >> >> UPDATE objects SET colour='purple' WHERE colour='green' >> >> Then you try to merge the databases by running everyone else's UPDATE >> commands against each copy. Under your system copy 1 will still end >> up different copy 2. >> >> A better way is to hold just one 'master' copy of the database >> centrally, use everyone's queries to update that, then distribute >> copies of the master. But even that won't have the results you might >> expect at first glance. First you need to work out what you'd want to >> happen under those circumstances. Then work out how to implement it. > > Hmmm, good point. I always thought this was how MySQL did replication, > but maybe that's why MySQL's replication is unidirectional. Two comments: > > % 99+% of the time, there won't be two updates "at the same time". In > other words, copy 1's change will almost always propagate to copy 2 > before copy 2 does another update. > > % In my app, almost all the UPDATEs are of the form "WHERE rowid IN > (...)". I don't do global updates like "WHERE foo='bar'". > > I read your other post as well, and still think this is worth > pursuing. Is there a place I can read up on proposed solutions and why > they fail? > > I've considered other solutions (eg, triggers and iwatch/rsync), but > the query-logging one seems to have certain advantages. > > One thought: backup the db before each INSERT/UPDATE (too ugly?) + > keep the backups for 5m (or whatever). If you get an update query from > a remote master w/ a timestamp older than your own most-recent update > query, restore the backup and re-run the queries in the correct order. > > I'm trying to implement a redundant system of 4 VPSs, and want to > avoid having a single master. > > -- > We're just a Bunch Of Regular Guys, a collective group that's trying > to understand and assimilate technology. We feel that resistance to > new ideas and technology is unwise and ultimately futile. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Stephan Wehner -> http://stephan.sugarmotor.org (blog and homepage) -> http://www.thrackle.org -> http://www.buckmaster.ca -> http://www.trafficlife.com -> http://stephansmap.org -- http://blog.stephansmap.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
