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. > > 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
