[sqlite] Corrupted database
> >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 have the original database. What other tests could I do to look for evidence? It appears (so far) that the database I reconstructed from the dump (minus an index request) contains all the original data, but that's hard to prove. >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 It's likely in this case that the apple/microsoft cooperation on SMB is ultimately at fault because it is breaking some guarantee that sqlite is depending on. Their file sharing is known to be crap. It's still worthwhile to try to get to the bottom of it as a preliminary step to fixing it.
[sqlite] Corrupted database
> >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 have the original database. What other tests could I do to look for evidence? It appears (so far) that the database I reconstructed from the dump (minus an index request) contains all the original data, but that's hard to prove. >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 It's likely in this case that the apple/microsoft cooperation on SMB is ultimately at fault because it is breaking some guarantee that sqlite is depending on. Their file sharing is known to be crap. It's still worthwhile to try to get to the bottom of it as a preliminary step to fixing it.
[sqlite] Corrupted database
> >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 have the original database. What other tests could I do to look for evidence? It appears (so far) that the database I reconstructed from the dump (minus an index request) contains all the original data, but that's hard to prove. >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 It's likely in this case that the apple/microsoft cooperation on SMB is ultimately at fault because it is breaking some guarantee that sqlite is depending on. Their file sharing is known to be crap. It's still worthwhile to try to get to the bottom of it as a preliminary step to fixing it.
[sqlite] Corrupted database
> >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 have the original database. What other tests could I do to look for evidence? It appears (so far) that the database I reconstructed from the dump (minus an index request) contains all the original data, but that's hard to prove. >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 It's likely in this case that the apple/microsoft cooperation on SMB is ultimately at fault because it is breaking some guarantee that sqlite is depending on. Their file sharing is known to be crap. It's still worthwhile to try to get to the bottom of it as a preliminary step to fixing it.
[sqlite] Corrupted database
> >The only safe thing to do is to drop the index and remake it. Or do to >something which does that (e.g. VACUUM). As I said, the first step in my repair was to drop the offending index.
[sqlite] Corrupted database
> >The only safe thing to do is to drop the index and remake it. Or do to >something which does that (e.g. VACUUM). As I said, the first step in my repair was to drop the offending index.
[sqlite] Corrupted database
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. 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. -- 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
[sqlite] Corrupted database
If you can .dump it, can you also use .read to restore it? And if so, how damaged does it appear to be? My databases, with a corrupt index, couldn't be restored directly, but the duplicate entries could be seen, cleaned up, and then the restore succeeded. Or (more conveniently) remove the index creation from the .dump, restore, use queries to find and remove duplicates, then reinstate the index. My thinking is that the kind of corruption I've had should at least be a different error code, and that a pragma to drop the index could allow repair without the extreme of dumping and editing the dump file.
[sqlite] Corrupted database
I'd be interested if you could characterize the corruption. For example, can use still use .dump to dump the database, and if so what kind of damage is there? The cases I've encountered recently, the "corruption" was only a few duplicated records, which broke the uniqueness constraint on an index. Interestingly, and perhaps alarmingly, if the index hadn't existed, no corruption would have been detected, although the internal events that allowed the duplicate entries would still be an undetected error.
[sqlite] Corrupted database
I'd be interested if you could characterize the corruption. For example, can use still use .dump to dump the database, and if so what kind of damage is there? The cases I've encountered recently, the "corruption" was only a few duplicated records, which broke the uniqueness constraint on an index. Interestingly, and perhaps alarmingly, if the index hadn't existed, no corruption would have been detected, although the internal events that allowed the duplicate entries would still be an undetected error.
[sqlite] recurrent failure mode
> >You might want to read my message on the topic from the list archives, >dated Sat, 31 Jan 2015. In this case, no concurrent or multiple users are involved. It's just one client and the database. There's still plenty of scope for the networked file system to do things that make sqlite fail.
[sqlite] recurrent failure mode
> >Can you provide the schema (the CREATE TABLE and any CREATE INDEX commands) >for that table ? CREATE TABLE preference_table ( preferenceSet text,/* name of this preference group */ preferenceName text, /* a preference in this group */ preferenceValue text /* sort order of this k...; CREATE UNIQUE INDEX preferenceindex on preference_table(preferenceSet,preferenceName); >Do you have any multi-access things going on ? Two or more computers, >applications, processes or threads trying to access the database at the same >time ? No, but it would be normal for the database to be on a different computer than the sqlite client, and be using whatever networked file system is common. The culprit clients seem to be macs, we're still seeking more information about the specifics. >Does your application check the result code returned from all sqlite3_ calls ? > Not just the ones inserting rows, but also those opening and closing the >file, setting PRAGMAs, or anything else it does with the sqlite3_ library ? Yes. It all goes through a common interface function which is careful about checking. As I said in the original message, this is something that has been working without problems for a few years, the only thing that's changing is the network and OS environment it's deployed in. My hypothesis is that a new failure mode in the file system is tickling a sqlite bug. Based on the evidence available now, a transaction that is trying to insert 4 records fails, and is retried, resulting in 8 records which can't be indexed.
[sqlite] recurrent failure mode
We're experiencing a new, recurrent failure mode in an old (ie; not recently
changed) sqlite application. This may be associated with buggy networked
file system implementations (thanks to apple and/or microsoft)
The apparent problem is that indexes on a small table become corrupted
by not being unique. Except for the non-uniqueness of the index keys,
there's no apparent damage.
The facile explanation would be that a transaction to insert a new
record was executed twice, but the indexes were incorrectly maintained.
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','scrollPos','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','nFill','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','placeInBW','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','DB_Subset','');
I suppose that this might be a sqlite bug if the "insert records" step
and the "maintain indexes" step were separated by a disk error and the
rollback of the failed transaction was incomplete.
[sqlite] recurrent failure mode
We're experiencing a new, recurrent failure mode in an old (ie; not recently
changed) sqlite application. This may be associated with buggy networked
file system implementations (thanks to apple and/or microsoft)
The apparent problem is that indexes on a small table become corrupted
by not being unique. Except for the non-uniqueness of the index keys,
there's no apparent damage.
The facile explanation would be that a transaction to insert a new
record was executed twice, but the indexes were incorrectly maintained.
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','scrollPos','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','nFill','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','placeInBW','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda
Richardson','DB_Subset','');
I suppose that this might be a sqlite bug if the "insert records" step
and the "maintain indexes" step were separated by a disk error and the
rollback of the failed transaction was incomplete.
Re: [sqlite] sqlite3 tool bug
> >The has been in shell.c since 3.8.6. We are on 3.8.8. Why >not upgrade? >-- Here in the real world, when everything is working, we ask "why upgrade". ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
> >The has been in shell.c since 3.8.6. We are on 3.8.8. Why >not upgrade? >-- Here in the real world, when everything is working, we ask "why upgrade".
Re: [sqlite] sqlite3 tool bug
> > >OK. Dave, please try this patch at let us know if it works better for >you: https://www.sqlite.org/src/info/80541e8b94b7 > It needs #include to compile in my sources. With that, it seems to fix the problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
> > >OK. Dave, please try this patch at let us know if it works better for >you: https://www.sqlite.org/src/info/80541e8b94b7 > It needs #include to compile in my sources. With that, it seems to fix the problem.
Re: [sqlite] sqlite3 tool bug summary
> > .once '| sqlite3 new.db' > .dump .Once is not a command in the version of sqlite3 I use. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug summary
> > .once '| sqlite3 new.db' > .dump .Once is not a command in the version of sqlite3 I use.
Re: [sqlite] sqlite3 tool bug summary
> >> But that doesn't explain the difference between redirecting to a file >> and redirecting to a pipe. using .output file works using > to direct stdout to a file works and produces the same file as .output using .read file works using < file does not work. using | to shortcut > and < doesn't work. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug summary
> >> But that doesn't explain the difference between redirecting to a file >> and redirecting to a pipe. using .output file works using > to direct stdout to a file works and produces the same file as .output using .read file works using < file does not work. using | to shortcut > and < doesn't work.
Re: [sqlite] sqlite3 tool bug
> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe data. Saying "it probably will work" is not very satisfactory. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3 F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 po.sqlite .dump | sqlite3 po2.sqlite Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share\e ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe data. Saying "it probably will work" is not very satisfactory. -- I did a little more experimentation, and found that the problem is on the input side of the pipe. Perhaps there is some windows conditioning that ought to be done by sqlite, on STDIN, to make it into a binary data source ? succeeds: cat < pipe.txt > pipe-out.txt sqlite3 database.sqlite .read pipe-out.txt fails: sqlite3 database.sqlite < pipe.txt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe data. Saying "it probably will work" is not very satisfactory. -- I did a little more experimentation, and found that the problem is on the input side of the pipe. Perhaps there is some windows conditioning that ought to be done by sqlite, on STDIN, to make it into a binary data source ? succeeds: cat < pipe.txt > pipe-out.txt sqlite3 database.sqlite .read pipe-out.txt fails: sqlite3 database.sqlite < pipe.txt
[sqlite] sqlite3 tool bug
> >Not, at least, when your database contains string data with unusual >characters that Windows feels like it should translate for you... Who can guarantee what characters are used in all their text strings, much less guarantee what unnamed transformations windows is helpfully doing to pipe data. Saying "it probably will work" is not very satisfactory.
Re: [sqlite] sqlite3 tool bug
> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on windows. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 tool bug
> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on windows. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on windows.
[sqlite] sqlite3 tool bug
> >The pipe method works fine for me on Linux. > >I'm guess this is a case of the windows command-line shell doing some >character translations in the pipe, rather than just shipping the >bytes through the pipe unaltered. Ouch. That basically means the "pipe" method shouldn't ever be used on windows.
Re: [sqlite] sqlite3 tool bug
> >Rather than the full database, can you show us the full schema of this >database, including triggers? It's a very simple database, no triggers or coalitions. The problem is most likely a buffer overrun because of a very long literal string field. I've sent a minimal sample to drh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
> >Rather than the full database, can you show us the full schema of this >database, including triggers? It's a very simple database, no triggers or coalitions. The problem is most likely a buffer overrun because of a very long literal string field. I've sent a minimal sample to drh
Re: [sqlite] sqlite3 tool bug
I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3 F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 po.sqlite .dump | sqlite3 po2.sqlite Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share\e ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 tool bug
I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3 F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 po.sqlite .dump | sqlite3 po2.sqlite Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share\e ...
[sqlite] sqlite3 tool bug
I have a class of database for which using sqlite3 to create a copy via the "pipe" method fails. Using an explicit intermediate file seems to work ok. I can supply a sample database to anyone interested in investigating. -- F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version 3.7.3 F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 po.sqlite .dump | sqlite3 po2.sqlite Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share\e ...
Re: [sqlite] damaged database recovery
> >Try doing: sqlite3 old-database .dump | sqlite3 fixed-database > >Then verify that "fixed-database" still contains all of your data. This doesn't work on these databases, even undamaged ones. I think it's a buffer size problem with sqlite3. The databases contain some rather long text strings. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] damaged database recovery
> >Try doing: sqlite3 old-database .dump | sqlite3 fixed-database > >Then verify that "fixed-database" still contains all of your data. This doesn't work on these databases, even undamaged ones. I think it's a buffer size problem with sqlite3. The databases contain some rather long text strings.
Re: [sqlite] damaged database recovery
The likely cause of corruption is that this is probably a database being accessed on a networked disk. -- sqlite> select * from preference_table where preferenceset='foo'; sqlite> drop index preferenceindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 24: [drop index preferenceindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite> pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... sqlite> reindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 3: [reindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite> pragma integrety_check; sqlite> pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] damaged database recovery
The likely cause of corruption is that this is probably a database being accessed on a networked disk. -- sqlite> select * from preference_table where preferenceset='foo'; sqlite> drop index preferenceindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 24: [drop index preferenceindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite> pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page... sqlite> reindex; (11) database corruption at line 52020 of [2677848087] (11) statement aborts at 3: [reindex;] (11) database corruption at line 46321 of [2677848087] (11) database corruption at line 46359 of [2677848087] Error: database disk image is malformed sqlite> pragma integrety_check; sqlite> pragma quick_check; *** in database main *** On tree page 5399 cell 16: Rowid 25142 out of order (max larger than parent max of 25141) On tree page 5520 cell 9: Rowid 25211 out of order (previous was 25745) On tree page...
Re: [sqlite] damaged database recovery
> > >> it wasn't possible to drop the index in question > >what happened when you tried ? Were you using your own code or the SQLite >shell tool ? sqlite shell tool. Same complaint, "database corrupted". >My guess is that you actually have file-level corruption which just happened >to corrupt data in an index page. Could have just as easily been a table page >and you would have had more trouble recovering your data. Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. However, since we're dealing with an "impossible" error, it's hard to say definitively. >A good approach for your situation might have been to use the SQLite shell >tool to .dump your database to a text file, then to use .read to create a new >database from those commands. But it may or may not have worked from your >particular corrupt database. That's exactly what I did do. .read initially failed with a complaint about the non-unique indexes (although once again, no indication which index). A process of elimination identified the index, which eventually allowed me to remove the duplicates, so .read could succeed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] damaged database recovery
> > >> it wasn't possible to drop the index in question > >what happened when you tried ? Were you using your own code or the SQLite >shell tool ? sqlite shell tool. Same complaint, "database corrupted". >My guess is that you actually have file-level corruption which just happened >to corrupt data in an index page. Could have just as easily been a table page >and you would have had more trouble recovering your data. Of course that's possible, but .dump produced what superficially appeared to be a perfectly consistent text file. However, since we're dealing with an "impossible" error, it's hard to say definitively. >A good approach for your situation might have been to use the SQLite shell >tool to .dump your database to a text file, then to use .read to create a new >database from those commands. But it may or may not have worked from your >particular corrupt database. That's exactly what I did do. .read initially failed with a complaint about the non-unique indexes (although once again, no indication which index). A process of elimination identified the index, which eventually allowed me to remove the duplicates, so .read could succeed.
Re: [sqlite] damaged database recovery
I have a case of a damaged database, where the only damage appears to be that somehow the index uniqueness constraint is violated. As long as the operations don't touch the index, the db operates without complaint. I was eventually able to construct a copy with good indexes, but 1) the generic error 11 "database corrupt" could have been more specific. It would have been handy to know that the complaint was about duplicate indexes, and which index, or even which table was involved. 2) it wasn't possible to drop the index in question. Or even the whole table containing the index. If I could have dropped the offending index, I could have removed the duplicates and recreated the index without requiring major surgery. 3) maybe I missed something - there was an easier way? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] damaged database recovery
I have a case of a damaged database, where the only damage appears to be that somehow the index uniqueness constraint is violated. As long as the operations don't touch the index, the db operates without complaint. I was eventually able to construct a copy with good indexes, but 1) the generic error 11 "database corrupt" could have been more specific. It would have been handy to know that the complaint was about duplicate indexes, and which index, or even which table was involved. 2) it wasn't possible to drop the index in question. Or even the whole table containing the index. If I could have dropped the offending index, I could have removed the duplicates and recreated the index without requiring major surgery. 3) maybe I missed something - there was an easier way?
Re: [sqlite] HELP sqlite3 used in vxworks has someproblem ï¼
There's a class of errors that affect lots of programs, where backup and/or antivirus software have a file open "unexpectedly", which causes routine operations such as delete and rename to fail unexpectedly. I once promoted a fix for sqlite that combatted these problems by retrying such unexpected failures instead of reporting the error immediately. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] HELP sqlite3 used in vxworks has someproblem ï¼
There's a class of errors that affect lots of programs, where backup and/or antivirus software have a file open "unexpectedly", which causes routine operations such as delete and rename to fail unexpectedly. I once promoted a fix for sqlite that combatted these problems by retrying such unexpected failures instead of reporting the error immediately. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] more OSX locking
I've tracked down the difference in behavior (between former sqlite
and modern sqlite) to this #if which disappeared in the current version
of sqlite.
aMap[] = {
{ "hfs",&posixIoMethods },
{ "ufs",&posixIoMethods },
{ "afpfs", &afpIoMethods },
#ifdef SQLITE_ENABLE_AFP_LOCKING_SMB
{ "smbfs", &afpIoMethods },
#else
{ "smbfs", &flockIoMethods },
#endif
{ "webdav", &nolockIoMethods },
{ 0, 0 }
};
This undocumented conditional is now gone; the table looks like this:
aMap[] = {
{ "hfs",&posixIoMethods },
{ "ufs",&posixIoMethods },
{ "afpfs", &afpIoMethods },
{ "smbfs", &afpIoMethods },
{ "webdav", &nolockIoMethods },
{ 0, 0 }
};
and, interestingly, the flockIoMethods are effectively gone from
sqlite, as this was the only reference to them.
To put this back into context from my earlier messages, the
afpIOMehtod of locking does not work in OSX 10.4 (it works
in the current OSX version 10.6).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OSX locking
I'm getting a little closer to pointing the finger at sqlite. I rebuilt my application using an archived binary from march 2009, and it works in the cases where a modern binary fails. It appears that some change in sqlite locking strategy between 2009 and now is not completely compatible with osx 10.4.11 At 01:46 PM 3/2/2011, Dave Dyer wrote: >The configuration I'm interested in has: > > Database resident on a windows 7 file system > sqlite binary based on sqlite 3.7.3 > > If the host is OSX 10.6, it works. > If the host is OSX 10.4, it fails. > >I swear it used to work in 10.4 too, but lacking a time >machine, it's hard to prove. I'm certain it worked a year ago with >the then-current version of OSX 10.4 and sqlite 3.6.11 > >using a debugger, I traced the source of the problem to afpSetLock, which >is called with identical context on both OSX 10.4 and 10.6, but fails >in 10.4. It seems odd that afpSetLock should be used for this >remotely mounted file system, but it is the same for both the successful >and unsuccessful cases. > >I've built a version with SQLITE_ENABLE_LOCKING_STYLE=0, which eliminates >afpSetLock, and in that case it fails in "unixLock" on both operating >systems. > >Note also that the same binary and an identical database work fine >if the database is on a local file system. > >Is there any change in sqlite locking strategy that can explain this? Or >alternatively, am I just being screwed by some Apple bug. I'm sure that >Apple has been messing with the remote file system support. > >___ >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
[sqlite] OSX locking
The configuration I'm interested in has: Database resident on a windows 7 file system sqlite binary based on sqlite 3.7.3 If the host is OSX 10.6, it works. If the host is OSX 10.4, it fails. I swear it used to work in 10.4 too, but lacking a time machine, it's hard to prove. I'm certain it worked a year ago with the then-current version of OSX 10.4 and sqlite 3.6.11 using a debugger, I traced the source of the problem to afpSetLock, which is called with identical context on both OSX 10.4 and 10.6, but fails in 10.4. It seems odd that afpSetLock should be used for this remotely mounted file system, but it is the same for both the successful and unsuccessful cases. I've built a version with SQLITE_ENABLE_LOCKING_STYLE=0, which eliminates afpSetLock, and in that case it fails in "unixLock" on both operating systems. Note also that the same binary and an identical database work fine if the database is on a local file system. Is there any change in sqlite locking strategy that can explain this? Or alternatively, am I just being screwed by some Apple bug. I'm sure that Apple has been messing with the remote file system support. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] proxy locking
Suppose you have a sqlite DB which might be accessed over a network, and might also be accessed locally. My understanding is that it would be disastrous if one client used proxy locking and the other did not - there would effectively be no lock. The "prefer proxy locking" logic has a strange mode where it uses the runtime environment to override the default behavior, which is to use proxy locking only for remote file systems. I think this is too important to be left to a runtime environment variable. If the application opens the database with SQLITE_OPEN_AUTOPROXY then proxy locking should be use unconditionally. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] improvement for the shell
The standard shell behaves very badly if you accidentally
select some BLOB data or excessively long strings. I recommend
this change:
#define MAX_STRING_SIZE 200
static void cautious_print_string(FILE *f,char *str)
{ char buffer[MAX_STRING_SIZE+3];
int i;
int exit = 0;
for(i=0; !exit && (i=127)) { ch = '.'; };
break;
}
buffer[i]=ch;
}
if(i>=sizeof(buffer)) // filled the max buffer
{ i = sizeof(buffer)-1;
buffer[i--] = 0;
buffer[i--] = '.';
buffer[i--] = '.';
buffer[i--] = '.';
}
fprintf(f, "%s", buffer);
}
and substitute
cautious_print_string(p->out,z);
for the fprintf in the display loop.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
Analysis and possible solution to getting unexpected SQLITE_CANTOPEN
errors from sqlite3_step
In pagerSharedLock, there is a test for the journal file
acessability immediatly followed by an attempt to open it.
If the journal file goes away between these two actions, sqlite3OsOpen
will return SQLITE_CANTOPEN which becomes an irreversable error. On the
other hand, if the journal file had just been missing according to
sqlite3OsAccess, that would have caused a SQLITE_BUSY which is
retryable.
I propose treating CANTOPEN the same as BUSY
rc = sqlite3OsAccess(pVfs,pPager->zJournal,SQLITE_ACCESS_EXISTS,&res);
if( rc==SQLITE_OK ){
if( res ){
int fout = 0;
int f = SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_JOURNAL;
assert( !pPager->tempFile );
rc = sqlite3OsOpen(pVfs, pPager->zJournal, pPager->jfd, f, &fout);
...
if(rc==SQLITE_CANTOPEN) { rc = SQLITE_BUSY; }
There's also the harmless inefficiency that sqlite3OsOpen can return a
readonly file even though it was requested to provide a read/write file.
pagerSharedLock closes it and treats this as CANTOPEN. Both of these
are conditions where the state of the file system is changing unexpectededly,
but it seems like the right thing to go with what is true rather than what
was predicted by sqlite3OsAccess.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
I've collected a little more information about this problem. At the low level sqlite3_step is going through "pagerSharedLock" and ultimately to unixOpen, which is returning unix errno=2 for the journal file. -- presumably at this point, the journal file is known to exist, so error 2 ought to be impossible. (I'm a little curious about the logic in unixOpen, which if a read/write open fails, tries a readonly open instead. I'm unsure how this is supposed to be acceptable. In any case, it's what's going on here - a readwrite open returns 2, so it tries a readonly open which also returns 2.) The other data point is that I'm sure something has changed in OSX as of 10.4. We used to test for the existence of the main database by opening it for reading, before asking sqlite to open it. This stopped working when the database was already open by a different user. so we switched to using an indirect test for the existance of the database. -- In other words, maybe this is all a "screwed by apple" issue. I haven't seen any indication what apple actually changed that broke the open test. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
I've collected a little more information about this problem. At the low level sqlite3_step is going through "pagerSharedLock" and ultimately to unixOpen, which is returning unix errno=2 for the journal file. -- presumably at this point, the journal file is known to exist, so error 2 ought to be impossible. (I'm a little curious about the logic in unixOpen, which if a read/write open fails, tries a readonly open instead. I'm unsure how this is supposed to be acceptable. In any case, it's what's going on here - a readwrite open returns 2, so it tries a readonly open which also returns 2.) The other data point is that I'm sure something has changed in OSX as of 10.4. We used to test for the existence of the main database by opening it for reading, before asking sqlite to open it. This stopped working when the database was already open by a different user. so we switched to using an indirect test for the existance of the database. -- In other words, maybe this is all a "screwed by apple" issue. I haven't seen any indication what apple actually changed that broke the open test. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
> > > 2. If ignoring #1, prove that the network disk implementation is correct >before blaming SQLite. Not trying to blame anyone here, just to gather information and ultimately find a reliable solution. > 3. Watch out for other background tag-a-longs causing weird behaviour on >Windows. I'm well aware of this. In this case, the problem seems to be associated with macs as clients, with either macs or pcs as servers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
> >If the previous process to access the database was in the middle of a write >but crashed before the write could complete, then your "read-only" process >will still need to write to the database in order to clean up the mess left >behind by the prior process before it can start its query. So perhaps your >"read-only" process is unable to open the rollback journal. > >Another possibility is that your SELECT needs to create some transient >intermediate tables to store intermediate results. Such tables are normally >created in /var/tmp (or wherever else it is appropriate to create temporary >files on your system) and then deleted at the end of the SELECT. If you >are out of space on /var/tmp or if you do not have write permission on >/var/tmp, you could get an SQLITE_CANTOPEN error. Neither of these fits the scenario, which is multiple readers contending for access to a networked disk. The process receiving the SQLITE_CANTOPEN is only reading, and the error occurs randomly in one of many similar requests. There is no transaction. SQLITE_CANTOPEN appears to be recoverable only by retrying the query (or many by sqlite3_reset?). It seems out of spec that a simple query should fail randomly in the absence of a disk error. If this were an update query, the overall process ought to be prepared to do a retry, but a select query ought to just return SQLITE_BUSY until the data is available. Correct? It is likely that some low level and unexpected failure on a disk operation is occurring. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
> >If the previous process to access the database was in the middle of a write >but crashed before the write could complete, then your "read-only" process >will still need to write to the database in order to clean up the mess left >behind by the prior process before it can start its query. So perhaps your >"read-only" process is unable to open the rollback journal. > >Another possibility is that your SELECT needs to create some transient >intermediate tables to store intermediate results. Such tables are normally >created in /var/tmp (or wherever else it is appropriate to create temporary >files on your system) and then deleted at the end of the SELECT. If you >are out of space on /var/tmp or if you do not have write permission on >/var/tmp, you could get an SQLITE_CANTOPEN error. Neither of these fits the scenario, which is multiple readers contending for access to a networked disk. The process receiving the SQLITE_CANTOPEN is only reading, and the error occurs randomly in one of many similar requests. There is no transaction. SQLITE_CANTOPEN appears to be recoverable only by retrying the query (or many by sqlite3_reset?). It seems out of spec that a simple query should fail randomly in the absence of a disk error. If this were an update query, the overall process ought to be prepared to do a retry, but a select query ought to just return SQLITE_BUSY until the data is available. Correct? It is likely that some low level and unexpected failure on a disk operation is occurring. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] errors after sqlite3_step
Ok, I'll settle for a list of the errors that can be returned by the standard codebase, and particularly what SQLITE_CANTOPEN means when the database clearly was already open. Also, this is coming from a SELECT query, so there should be no question of opening a transaction file. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] errors after sqlite3_step
There ought to be a definitive list of what errors can occur after sqlite3_step, and which (if any) are recoverable. In the case in point, I sqlite_prepare_v2 returns sqlite_ok, and the initial call to sqlite_step returns sqlite_cantopen This doesn't make any sense to me, and certainly isn't mentioned as a possibility in the C api. I seems to be fatal though. This error is associated with a lock contention scenario. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] java SQLite Wrapper
The java sqlite wrapper is actually a pretty amazing piece of work. As I understand it from reading the docs: The original sqlite.c is compiled for the mips processor and unix operating system. The mips binary is translated into java byte codes, and structured into a java class, which are executed by the jvm. Hence the 100% pure java aspect. Of course, the JIT in the JVM translates this into native code. The entire plugin is packaged with a virtual unix OS, including file i/o, memory allocation, and so on. There are a relatively small number of these "system calls" to support. The strangest thing about this is the mapping of "mips" memory into java memory, which is not ideal or super efficient. Therefore the overall performance of the wrapper is significantly worse than the original C. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
> >Alternatively use Process Monitor from sysinternals.com which will show >arguments and return codes without any need to run a debugger. Even better, use FILEMON (from sysinternals.com) for clues. It's wonderful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More Mac problems - building 3.6.23
Attempting to build 2.6.23 command shell under osx 10.4.11 doesn't work because of unlinked symbol gethostuuid, which seems to be associated with the SQLITE_ENABLE_LOCKING_STYLE option Compiling with SQLITE_ENABLE_LOCKING_STYLE=0 links, and works on native disks, but fails immediately on networked disks with "disk I/O error" from somewhere. Advice? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corruption problem with attached macintosh database
> >Have you tried with this turned on: > > http://www.sqlite.org/compile.html#enable_locking_style Enabling this causes link errors under my version of OSX, due to the absence of "gethistuuid". I see some traffic on the web that suggests this comes in a later OSX. (I use 10.4.11) Also, this seems to be an API-free option that "does the right thing", but there is no apparent way to tell what option it is selecting, or for that matter, what locking is used under any circumstances. It would be useful to have a specific piece of information, "locking using strategy x" which I could derive and perhaps verify dissonant locking strategies as the source of the problem now, and if it ought to be fixed by enabling the fancier locking code. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
We're not talking about reliability or robustness of networked file systems - those caveats are valid of course - but not relevant for the case in point. The case in point is a simple sequence of operations that you can execute one at a time, as slowly as you like, and results in a corrupt database every time. -- The "different locking strategies" explanation sounds plausible to me, but its curious that the main database, which is accessed the same way, is working fine. Something different (and buggy) seems to be happening with attached databases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] more on mac database corruption
> >AFAIK it's a general rule: don't use SQLite with database somewhere on >network shared file system, otherwise bad things can happen. That's definitely not the general rule. Generally, you can open a sqlite database from multiple processes and all of them can modify at will without corrupting the database. Of course, if they're all modifying the same records, there's uncertainty what the final state will be, but the database is still intact and consistent. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corruption problem with attached macintosh database
> > >What journaling mode are you using? whatever is default. I compile mu own sqlite static libraries, but I don't customize the settings. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] more on mac database corruption
I was able to reproduct the problem using a trivial set of commands to the standard sqlite command tool: On the Mac: gorp:~/2010 yeartech/yearbook tools/resource davedyer$ /applications/utilities/sqlite3-shell actiontool2.sqlite SQLite version 3.6.10 with the Encryption Extension sqlite> attach database 'indexer.sqlite' as indexer; sqlite> begin transaction; On the PC: M:\2010 yeartech\yearbook tools\resource>sqlite3 actiontool2.sqlite sqlite> attach database 'indexer.sqlite' as indexer; sqlite> begin transaction; sqlite> delete from indexer.preference_table; sqlite> insert into indexer.preference_table select * from preference_table; sqlite> commit; On the Mac: sqlite> delete from indexer.preferences_table; SQL error: no such table: indexer.preferences_table sqlite> delete from indexer.preference_table; sqlite> insert into indexer.preference_table select * from preference_table; SQL error: database disk image is malformed sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] corruption problem with attached macintosh database
> >Could you help us by adding any of the following details ? > >What OS is the Mac running ? OSX 10.4.11 for me, but also snow leopard. >What OS is the PC running ? Windows 2003 server for me, but also XP (note the file systems are all mac file systems) >What protocol is being used to access the Mac file share ? Presumably windows standard file sharing protocol over tcp >Is the file-sharing host accessing the database as a shared file, or as a file >on its hard disk ? The mac acting as file host is accessing the file as a local file. >Does this happen without any data-changing instructions ? In other words can >I get this fault using only _open, ATTACH and lots of SELECT commands until >something falls over ? Or even just repeated _open, ATTACH and _close until >something falls over ? No data on this question. The purpose of this querty setup is to copy some data into an auxialiary database. >> Note that the main database, which is updated periodically, doesn't have >> a corruption problem, > >Are both 'main' and 'auxiliary' on in the same folder, being accessed the same >way ? Yes. And significantly, there is no corruption problem with simultaneous updates to the main database. >> and that this is only a problem with databases resident >> on a mac. The same scenario, with databases resident on the PC disk, works >> fine. > >When the databases are on the PC disk, what protocol is the Mac using to >access them ? Presumably the same. Before recent versions of OSX this kind of access used "samba" server. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] more on mac database corruption
I was able to reproduce the problem using a trivial set of commands to the standard sqlite command tool: On the Mac: gorp:~/2010 yeartech/yearbook tools/resource davedyer$ /applications/utilities/sqlite3-shell actiontool2.sqlite SQLite version 3.6.10 with the Encryption Extension sqlite> attach database 'indexer.sqlite' as indexer; sqlite> begin transaction; On the PC: M:\2010 yeartech\yearbook tools\resource>sqlite3 actiontool2.sqlite sqlite> attach database 'indexer.sqlite' as indexer; sqlite> begin transaction; sqlite> delete from indexer.preference_table; sqlite> insert into indexer.preference_table select * from preference_table; sqlite> commit; On the Mac: sqlite> delete from indexer.preferences_table; SQL error: no such table: indexer.preferences_table sqlite> delete from indexer.preference_table; sqlite> insert into indexer.preference_table select * from preference_table; SQL error: database disk image is malformed sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] corruption problem with attached macintosh database
I think there is a locking problem that leads to corrupt databases under quite reproducable conditions. The conditions are: database resident on a mac file sustem mounted as a drive letter on a pc the "main" database is open and shared by sqlite running on both computers. the "auxiliary" database is attached and updated simultaneously from both computers> Under these circumstances, both applications typically get "error 11, disk image is corrupted" errors while attempting to update the auxiliary database. Note that the main database, which is updated periodically, doesn't have a corruption problem, and that this is only a problem with databases resident on a mac. The same scenario, with databases resident on the PC disk, works fine. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] odd behavior for sqlite/mac
I collected a little more data on this problem by powering up an old PPC mac, which is using a hardwired network connection instead of airport. The behavior is the same - when there is contention for database access, the mac puts itself to sleep. I really don't see any scenario where this can be the correct or intended behavior, but it's still unclear if it is sqlite or mac OSX that is misbehaving. -- At 04:57 PM 3/19/2010, Dave Dyer wrote: >I have an application which uses a sqlite database shared across a network. >Under conditions where the database is resident on a PC, and I share it from >a mac, and the mac/pc are both contending for access to the database, >my mac sometimes turns off it's monitor and enters system "sleep" mode. >It wakes up again when I hit any key. > >This is DEFINITELY associated with the sqlite datbase being active. > >I sometimes can get to a debugger, and find a stack trace pointing to >"pager_wait_on_lock". This tickled a vague suspicion that some rare >path through sqlite's mac file system implementation was triggering >system sleep instead of application sleep. > >Any thoughts or similar experiences? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] odd behavior for sqlite/mac
I have an application which uses a sqlite database shared across a network. Under conditions where the database is resident on a PC, and I share it from a mac, and the mac/pc are both contending for access to the database, my mac sometimes turns off it's monitor and enters system "sleep" mode. It wakes up again when I hit any key. This is DEFINITELY associated with the sqlite datbase being active. I sometimes can get to a debugger, and find a stack trace pointing to "pager_wait_on_lock". This tickled a vague suspicion that some rare path through sqlite's mac file system implementation was triggering system sleep instead of application sleep. Any thoughts or similar experiences? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE3_CANTOPEN
There is a known problem, at least with NTFS, that deleting the
journal file fails unexpectedly, resulting in sqlite3_cantopen.
I believe I'm the one who originally reported this problem,
but the installed solution isn't quite what I recommended.
I'm not sure what OS/File System you are using, but something
analogous may be going on for you.
In "winDelete", the delete code in the released version is
if( isNT() ){
do{
DeleteFileW(zConverted);
}while( ( ((rc = GetFileAttributesW(zConverted)) !=
INVALID_FILE_ATTRIBUTES)
|| ((error = GetLastError()) == ERROR_ACCESS_DENIED))
&& (++cnt < MX_DELETION_ATTEMPTS)
&& (Sleep(100), 1) );
The code I"m using is
if( isNT() ){
do{
rc = DeleteFileW(zConverted);
if(rc==0)
{ long attr = GetFileAttributesW(zConverted);
if(attr==0x) { rc=1; } // ok as long as
sombody deleted it
}
}while( rc==0
&& (cnt++ < MX_DELETION_ATTEMPTS)
&& (Sleep(100), 1) );
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on a Windows Network
>I have heard problems with SQLite and NFS but I have no idea if a standard >Windows shared drive uses NFS or not. Am I o.k. to use SQLite??? It's ok for low intensity uses. You'll get "database locked" errors if there is too much contention for the database. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete with an "exists" clause
>I realize your example may not reflect the specific needs of your >application, ... Yea, my actual application required a query too complex to be put in an ordinary "where" clause. I think I've figured it out - think of the "exists" clause as sort of an implied join with the table to be partially deleted. if the selection query is similar to: select * from image left join attribute on image.uid=attribute.value I suppose if SQL had anything like a consistent syntax, I would expect to write this as something like: delete from image left join attribute on image.uid=attribute.value SQL doesn't allow this, but this "exists" clause is what you can use instead. delete from image where exists (select * from attribute on attribute.value = image.uid) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] delete with an "exists" clause
This little program deletes all rows. Is this a bug, or perhaps I misunderstand how delete with an exists clause is supposed to work. drop table if exists dummy; create table dummy ( var int ); insert into dummy (var) values (1); insert into dummy (var) values (2); insert into dummy (var) values (3); delete from dummy where exists (select * from dummy where var='2'); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] advice about opening an encrypted database
using the standard sqlite encryption option: If I open a database I expect to be encrypted, and call sqlite_key to establish the expected key, how should I verify that the database is now open for business? Ie that the key was correct. Similarly, if I open a database might or might not be encrypted, how can I definitively determine that is the case, so I can ask the client for a password. It's not really satisfactory to just wait for some downstream query to fail, or to make a dummy query and assume that if it fails the reason is the lack of proper encryption keys. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A bit OT: Good CVS client for Windows
At 01:28 PM 2/26/2009, J. R. Westmoreland wrote: >Can someone please give me a suggestion for a good client for CVS that runs >under Windows? Use tortoise cvs. It's wonderful. http://www.tortoisecvs.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
> >strcpy(result->>ip_address,(const char*)pszData); Strcpy is one of those evil "standard" c functions that no one should every use, because it copies an unlimited and unknown amount of data into an obviously finite buffer. If some joker puts a little extra data in a sqlite IP address, and you've got a bug that could take forever to find. Standard strncpy is also pretty evil, because it doesn't guarantee a null at the end. I know you're all too smart to be so sloppy, and this little lecture is totally redundant and unnecessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strcpy and sqlite3_column_text
> >strcpy(result->>ip_address,(const char*)pszData); Strcpy is one of those evil "standard" c functions that no one should every use, because it copies an unlimited and unknown amount of data into an obviously finite buffer. If some joker puts a little extra data in a sqlite IP address, and you've got a bug that could take forever to find. Standard strncpy is also pretty evil, because it doesn't guarantee a null at the end. I know you're all too smart to be so sloppy, and this little lecture is totally redundant and unnecessary. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] journal files
How does sqlite distinguish between a journal file left over from a crash, and a journal file that some other process is still using? .. and if the answer is "try to open it yourself", then what about the race condition where process A has finished, and is about to delete the journal, but hasn't succeeded yet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] more on networked file access failures
I tracked the initial failure into sqlite3WinDelete, which is
used to delete journal files. There is a semi-kludge to retry
the deletion, based on the ad-hoc observation that sometimes the
delete fails because some other process has the file open.
Two observations:
(1) the retry count of 3 seems low. Retrys are rare,
If this process fails it causes a serious failure,
so we shouldn't be nervous about delaying before
throwing in the towel.
(2) the problem in my application seems to be not the number of
retries, but the call to GetFileAttributesW. I don't recall any
discussion what function this should be performing, but if
GetFileAttributesW fails with -1, no retries are attempted.
I fixed the problem (for my app) by removing the GetFileAttributesW
clause entirely.
#define MX_DELETION_ATTEMPTS 3
static int sqlite3WinDelete(const char *zFilename){
int cnt = 0;
int rc;
void *zConverted = convertUtf8Filename(zFilename);
if( zConverted==0 ){
return SQLITE_NOMEM;
}
SimulateIOError(return SQLITE_IOERR_DELETE);
if( isNT() ){
do{
rc = DeleteFileW(zConverted);
}while( rc==0
// && GetFileAttributesW(zConverted)!=0x
&& (cnt++ < MX_DELETION_ATTEMPTS)
&& (Sleep(100), 1) );
}else{
#if OS_WINCE
return SQLITE_NOMEM;
#else
do{
rc = DeleteFileA(zConverted);
}while( rc==0
// && GetFileAttributesA(zConverted)!=0x
&& (cnt++ < MX_DELETION_ATTEMPTS )
&& (Sleep(100), 1) );
#endif
}
sqliteFree(zConverted);
OSTRACE2("DELETE \"%s\"\n", zFilename);
return rc!=0 ? SQLITE_OK : SQLITE_IOERR_DELETE;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] locking errors with networked share database file
I'm continuing to experiment with deliberate lock contention, using a sqlite database open on a network share. Using two computers, and two handed mouse technique to induce both to write to the same database at the same time, I sometimes get SQLITE_IOERR rather that SQLITE_LOCKED, and when this occurs, a pause followed by a retry always fails, even when the other computer accessing the database no longer has it open. I stepped into the failure a ways, and discovered it is coming from sqlite3PagerAcquire In all the cases I have encountered, it is the computer using the network share, not the computer hosting the file, which gets the unrecoverable error. It seems like this scenario ought to be recoverable by a simple retry, and that this must be a sqlite bug, or perhaps a failure in the low level networked file access. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network shares
In modern working environments, network shares are not an abnormality. Sqlite Network shares work by default for pcs. If sqlite network shares don't work by default for macs, it looks like sqlite is broken or macs are broken. I suppose that's why apple made it unbroken by default in the software they ship. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac file locking
> >It only works on a Mac. The build fails on other posix platforms. Then of course, it should only be enabled on Macs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac file locking
> >Probably this: > > http://www.sqlite.org/compile.html#enable_locking_style > >Compilation option "SQLITE_ENABLE_LOCKING_STYLE". Compiling the mac version with this flad defined seems to have fixed my problem. Is there any reason this shouldn't be on by default? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac file locking
> >Yes, I've noticed and reported this same problem. It appears that the >SQLite bundled in Mac OS X has a special flag set to allow opening >database files on networked volume, and that works great. So what's the name of this special flag? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] mac file locking
I'm experimenting with using networked files as sqlite databases (yes, I'm aware this is a questionable practice) If the client is running on a mac, and the database file is not a local disk, "database locked" error is returned immediately. I assume this means that the file lock mechanism failed, since there are no existing users sharing the database. Does anyone have information about this? On a related topic, XCODE seems to not be up to debugging the single file release - it gets very confused about where breakpoints are located. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing an image to the database as a BLOB
You ought to think very carefully before storing anything resembling raw hbitmap data into your database. You will be embedding obscure dependencies on windows (vs macos or unix), arcane hardware requirements such as row lengths being a multiple of 16 or 32 bytes, or whether the rows are scanned from top to bottom or bottom to top. Also, you will not get the benefit of any compression. A better idea might be to build a memory image of some well known image format suitable for your purposes, and store that using a blob. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question about sqlite3_step
Is it ever possible for subsequent calls to sqlite_step to return either a different number of column values or a different set of columns? In other words, if I'm doing something based on the column names, can I check only the first step, and assume the rest are the same? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] file system interface?
While discussing possible sqlite applications today, it occurred to me that you could present a sqlite database as a mountable file system. Some BLOB objects would be presented as files, with attributes such as directory names and file dates stored as ancillary fields. One of the major impediments to using sqlite to store images, for example, is that you can't view or manipulate them using your standard image tools. Has anyone done somthing like this? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] simultaneous transactions
I don't understand why the following transaction behavior is correct or necessary. The question involves two simultaneous transactions on the same database Process 1 Process 2 BEGIN BEGIN insert... insert... fails "locked" end also fails "locked" It seems that the end of a transaction can fail even if all the intermediate actions succeeded. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] recognizing a sqlite database?
Is there an officially sanctioned magic number test to recognize a sqlite database? I'd like to allow a user to browse for a file, and if it happens to be a sqlite database to take a separate path. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite on VPS configurations
Is there any suspicion that sqlite might fail on virtual unix server configurations? Among other things that might be odd in this kind of environment, perhaps the relationship between disk locks and physical activity might be tenuous, and there might be genuine multiprocessors. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Longest "real" SQL statement
> >The use of sqlite3_bind_blob() for this is MUCH faster, and >surely requires less code. May I ask for more detail on why >you chose to generate SQL statements with huge blob literals? I'm also doing sqlite networked, so all the bits have to be encoded and transmitted anyway. Not using blobs maintains a uniform set of procedures for local and networked sqlite. -- ie; I can link with real sqlite, or my network stub, with no change in the main code. For sure, some additional hair could specialize the usage so the local could be more effecient, but that's for the future. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Longest "real" SQL statement
I've recently been storing images in Sqlite datbases, and for various reasons don't want to use the normal "blob" mechanisms. Consequently, a 10mb JPG image file would be encoded as slightly larger than 10mb sqlite "insert xx" command. The size of the commands required by this method is limited only by the size of images I think is reasonable to store as pseudo-blobs. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Transaction journal corrupted by antivirus
Slightly off-topic for this list, but antivirus and firewall software is a plague that is bad and going to get worse. In effect, antivirus software is a security guard that randomly shoots "suspicious" members of the public. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Still getting "Insertion failed because database is full." errors
I think it is also the case that flash cards write pretty slowly, and there is a finite buffer of pending writes. Your unexpected write failure may be because you're writing too fast. Depending on the driver and access mode, the writes might throw this error rather than block your process. You might also contemplate what this mode of write does to the ACID characteristic of the database - bits that are written to the card may not actually get to permanant storage if the card loses power. PS: I'm no expert on this - it's just surmise from general knowlege of flash cards and operating systems. Someone who actually writes flash drivers would be a better source. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite server
I've revived the sqlite server demo at http://www.it77.de/sqlite/sqlite.htm and updated the API to sqlite 3. This project defines a very simple sqlite server which operates local sqlite databases, and a simple client which links with a lightweight library containing a plug-compatible subset of the full sqlite C api. Essentially, if you have a simple enough program which uses sqlite, you can link with this library instead and connect to a remote sqlite database. There are many reasons why this simple client/server pair is ONLY a demo, and not suitable to be deployed outside of your sandbox; but there are also plenty of reasons why a networked sqlite might be preferable to a standard networked database such as mysl. -- OK, that's the announcement, now for the question. I'm planning to make this demo available, but if a show of hands indicates enough immediate interest, this could be set up at sourceforge or elsewhere as a real project. Please send ddyer-sqlite at real-me.net a message (no need to flood the mailing list) if you (a) would like a copy to play with (b) would be interested in participating in an open source project to make a deployable network database. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: journal - "Unable to open the database file"
> >OS designers could think about providing a capability of tagging a file as >private and making it immune to interference from gratuitous activity. Of course, if such a facility existed it would be exploited by viruses to keep the antivirus forces at bay. Quite a conundrum. This kind of thing isn't just a database problem. All kinds of routine operations can fail unexpectedly valid but very temporary reasons, and the OS solution is to throw the crap back to every single application to handle. It's not good for reliability. - To unsubscribe, send email to [EMAIL PROTECTED] -
