On 2015-03-16 08:35 PM, Dave Dyer wrote: > I have some addition evidence that there is an underlying problem, > exacerbated by some failure in SMB file sharing. > > In this instance, there is a set of duplicated records that did not > directly cause an indexing error, but which could have been created > if a transaction failed (presumably due to a file i/o error), was > incorrectly unwound, and then repeated. > > - Details - > > Using the sqlite3 tool, starting with the damaged database; > I dropped the indexes that had directly caused the complaint > queried to find the duplicated records > deleted the duplicated records > tried to recreate the indexes (expecting this would succeed). > It did not. I got a "database is malformed" error. > > I take this as evidence that there was some actual damage to the > database, not just cleanly duplicated records with a bad index.
This is unfortunately an assumption and not evidence of any sort. How do you know the database is "healthy" in total now that you have deleted a couple of records? Did you run an integrity check which passed before attempting to recreate the indices? > I did a full dump of the original database, removed the bad index > request, created a new database from the dump, repeated the duplicate > record removal, and successfully created the index. > > This "fully repaired" database turned out to contain a duplicated set of > records which did not cause an indexing problem, but which should not have > occurred, and was consistent with a duplicated transaction. If this had > been caused by a program error - ie; I really inserted the records twice, > the database would not have been really damaged, and the shortcut repair I > tried first would have succeeded. Another assumption I'm afraid. If a transaction failed, the script might call for any amount of things based on your ON CONFLICT control settings. If you are logging the DB activity and a rollback was logged that would get us closer to evidence. Also, one has to assume that SQLite has full access and control over the file and locking mechanisms to assume that any damage must be due SQLite not rolling back a transaction correctly - which incidentally is directly proved to not be the case by the very next statement: > In this case, the client is a mac running os 10.7.5, the file > server is a PC running OS 8 server, and the sharing is via SMB Do you still have a copy of the originally damaged Database? I believe a closer look to it will reveal more corruption than the assumed. I do however think you are on the right track with thinking that the duplicated transaction (or failure of rollback or program error or mishandling of a duplicate insert fail that cause the transaction to be either committed twice or not failed correctly or resubmitted without clear prior failure or success) is the culprit here, and I am fairly certain this can happen in the setup described above as per the http://www.sqlite.org/howtocorrupt.html
