Re: [sqlite] Multi-master replication with updated Versioning extension
Hello! On Friday 31 July 2009 18:34:17 Ken wrote: > I've looked at your code and discussions on this list about the versioning. I > have a few questions. > > 1. How are you moving the data around from one master to another? > 2. How are you applying the changes once moved to the master? By the tcl scripts. Databases only store data and versions information. I'm planning to publish some of the tcl scripts on the next week. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
Alex, I've looked at your code and discussions on this list about the versioning. I have a few questions. 1. How are you moving the data around from one master to another? 2. How are you applying the changes once moved to the master? --- On Fri, 7/31/09, Alexey Pechnikov wrote: > From: Alexey Pechnikov > Subject: Re: [sqlite] Multi-master replication with updated Versioning > extension > To: sqlite-users@sqlite.org > Cc: "D. Richard Hipp" > Date: Friday, July 31, 2009, 8:42 AM > Hello! > > I made some changes: > hash field in actions table has always > name "checksum" (so versioning and replication logic doesn't > influence of hash algorithm) > versioning() function without second > argument now start "local" mode > history and actions tables are renamed > > Updated files is here > http://mobigroup.ru/files/sqlite-ext/versioning/ > > Now there are two problems in the realization: > the "replace" conflict resolution algorithm > for SOURCE table may produce errors - tickets 3964, 3982 > versioning_drop() function doesn't work - > ticket 4001 > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
Hello! I made some changes: hash field in actions table has always name "checksum" (so versioning and replication logic doesn't influence of hash algorithm) versioning() function without second argument now start "local" mode history and actions tables are renamed Updated files is here http://mobigroup.ru/files/sqlite-ext/versioning/ Now there are two problems in the realization: the "replace" conflict resolution algorithm for SOURCE table may produce errors - tickets 3964, 3982 versioning_drop() function doesn't work - ticket 4001 Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning?extension
On 30 Jul 2009, at 6:19pm, Jay A. Kreibich wrote: > You're not really supposed to > dump and restore a multi-master system. We're back to talking about synchronising different copies of the database again, aren't we ? Dumping and restoring a multi-master system means restoring the entire context of all the copies of the database. You have to not only restore the contents of one copy of the database, but also restore the state of all other copies. And if you're using a journaling method to manage your synchrony, restore the state of all the journals too. The only exception to this is if the dump was done when all sites agreed that they all had completely synchronised up-to-date copies of the database. And many multi-master databases are never in that state. So the question is 'what was dumped, and what state was it in' ? So the next question is how your journaling system was designed. You can design a journaling and synchronising system so that any site(s) can restore from a backup at any time, and the next time everyone synchronises they'll get their best data. But often this is not considered when the system is designed and the system loses data or worse still corrupts everyone else's. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning?extension
On Thu, Jul 30, 2009 at 08:33:10PM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > On Thursday 30 July 2009 19:47:39 Jay A. Kreibich wrote: > > Then again, given that ROWID values are signed 64 bit values, you > > could just start each master at some offset (like +0x00FF) > > and not worry about it. It would still be a good idea to force all > > the tables into an AUTOINCREMENT mode somehow. > > Before start replication databases may be copied from prototype > database as files and so they are binary equal. That still works (outside of adjustments to the sqlite_sequences table). Any replication function is going to manually insert a whole record, including the ROWID. The AUTOINCREMENT values would only be used if a master has to insert a brand-new row that otherwise doesn't exist anywhere in the cluster of masters. In that case it has to be assigned a globally unique (i.e. UUID) ROWID value. Off-setting the pool of ROWID values each master uses effectively does this. > UUID is really unique key for multi-master replication. And that's more or less what this does. Any brand new record that is created will be assigned a globally unique ROWID value, basically making that ROWID a UUID. > This is bad because ROWID without explicit field is not persistant > and may change after dump/restore. Please see illustration of the problem: Yes, this is true of almost every database system out there, including ones that generate UUIDs. You're not really supposed to dump and restore a multi-master system. If you trash a database you "restore" it by creating an empty master an syncing from a working master (or, in the case of SQLite, just copy of the file). If all your masters are trashed and you're building a new cluster, the alignment of ROWID values doesn't matter; even if they're freshly generated they're still unique across the new one-master cluster. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
On Thu, Jul 30, 2009 at 06:11:42PM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > On Thursday 30 July 2009 17:25:15 P Kishor wrote: > > > I haven't looked at your work in depth, but I am interested in this. I > > > have implemented a very simple versioning system with TRIGGERs whereby > > > every change (INSERT, UPDATE, DELETE) in a column in a table is stored > > > in a versions table along with its primary key, allowing me to go back > > > and examine any version and roll back to it, if desired. > > Yes, the primary key field is good enough for master-slave replication > but not for multi-master because each master has self sequence counter. You're saying primary key when I think you more specifically mean ROWID. Assuming you could override and force AUTOINCREMENT behavior on all tables (that might make an interesting PRAGMA), this could help with that: http://www.sqlite.org/cvstrac/tktview?tn=3563 As long as the "BY" is greater than the number of masters and each master has a "FROM" that is sequenced, they'll leap-frog over each other. Then again, given that ROWID values are signed 64 bit values, you could just start each master at some offset (like +0x00FF) and not worry about it. It would still be a good idea to force all the tables into an AUTOINCREMENT mode somehow. PRAGMA request: http://www.sqlite.org/cvstrac/tktview?tn=4002 -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
MD5 hashes can still collide. How does this implementation deal with hash collisions? - Original Message - From: "Alexey Pechnikov" To: "General Discussion of SQLite Database" Sent: Thursday, July 30, 2009 7:11 AM Subject: Re: [sqlite] Multi-master replication with updated Versioning extension > Hello! > > On Thursday 30 July 2009 17:25:15 P Kishor wrote: >> > I haven't looked at your work in depth, but I am interested in >> > this. I >> > have implemented a very simple versioning system with TRIGGERs >> > whereby >> > every change (INSERT, UPDATE, DELETE) in a column in a table is >> > stored >> > in a versions table along with its primary key, allowing me to go >> > back >> > and examine any version and roll back to it, if desired. > > Yes, the primary key field is good enough for master-slave > replication but not for > multi-master because each master has self sequence counter. And full > record > hash may be used for master-slave replication on tables without > primary keys. > > As table-independant way extension use ROWID field and md5 hash of > all fields. > So multi-master is possible with some restrictions such as > non-unique records > is denied. But may be ROWID + hash of record can help for this > situation too. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
Hello! On Thursday 30 July 2009 17:25:15 P Kishor wrote: > > I haven't looked at your work in depth, but I am interested in this. I > > have implemented a very simple versioning system with TRIGGERs whereby > > every change (INSERT, UPDATE, DELETE) in a column in a table is stored > > in a versions table along with its primary key, allowing me to go back > > and examine any version and roll back to it, if desired. Yes, the primary key field is good enough for master-slave replication but not for multi-master because each master has self sequence counter. And full record hash may be used for master-slave replication on tables without primary keys. As table-independant way extension use ROWID field and md5 hash of all fields. So multi-master is possible with some restrictions such as non-unique records is denied. But may be ROWID + hash of record can help for this situation too. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
On Thu, Jul 30, 2009 at 7:42 AM, P Kishor wrote: > Alexey, > > > On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov > wrote: >> Hello! >> >> Please see >> http://mobigroup.ru/files/sqlite-ext/versioning/ > > I haven't looked at your work in depth, but I am interested in this. I > have implemented a very simple versioning system with TRIGGERs whereby > every change (INSERT, UPDATE, DELETE) in a column in a table is stored > in a versions table along with its primary key, allowing me to go back > and examine any version and roll back to it, if desired. > > Again, without looking at your code first, what is it that you are > doing with the C libs that can't be done with just TRIGGERs and a > versions table? Ok, I get it. Your code actually creates the versioning tables and triggers automatically. Great. Will give you feedback once I try it out. By the way, DRH has create fossilscm, which is obviously using some kind of versioning. It might be worthwhile studying that and incorporating that, if relevant. > >> >> Master-slave may use ROWIDs and multy-master may use md5 hash of full record. >> >> This is test version and I'm glad to get any comments and ideas. >> >> P.S. md5 extension sources is here >> http://mobigroup.ru/files/sqlite-ext/md5/ >> >> Best regards, Alexey Pechnikov. >> http://pechnikov.tel/ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Puneet Kishor http://www.punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > Sent from Madison, WI, United States > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-master replication with updated Versioning extension
Alexey, On Thu, Jul 30, 2009 at 4:42 AM, Alexey Pechnikov wrote: > Hello! > > Please see > http://mobigroup.ru/files/sqlite-ext/versioning/ I haven't looked at your work in depth, but I am interested in this. I have implemented a very simple versioning system with TRIGGERs whereby every change (INSERT, UPDATE, DELETE) in a column in a table is stored in a versions table along with its primary key, allowing me to go back and examine any version and roll back to it, if desired. Again, without looking at your code first, what is it that you are doing with the C libs that can't be done with just TRIGGERs and a versions table? > > Master-slave may use ROWIDs and multy-master may use md5 hash of full record. > > This is test version and I'm glad to get any comments and ideas. > > P.S. md5 extension sources is here > http://mobigroup.ru/files/sqlite-ext/md5/ > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multi-master replication with updated Versioning extension
Hello! Please see http://mobigroup.ru/files/sqlite-ext/versioning/ Master-slave may use ROWIDs and multy-master may use md5 hash of full record. This is test version and I'm glad to get any comments and ideas. P.S. md5 extension sources is here http://mobigroup.ru/files/sqlite-ext/md5/ Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users