[sqlite] “btreeInitPage() returns error code 11”
Hi all, I?ve got a corrupt SQLite database, and I?m trying to figure out what produced the corruption. A ?pragma integrity_check? on it yielded the following. Does this trip anyone?s ?danger sense? of what may have happened? Thank you! -FG SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma integrity_check; *** in database main *** Page 10628: btreeInitPage() returns error code 11 On tree page 8410 cell 103: Child page depth differs Page 10656: btreeInitPage() returns error code 11 Page 10686: btreeInitPage() returns error code 11 Page 10718: btreeInitPage() returns error code 11 Page 10641: btreeInitPage() returns error code 11 On tree page 10487 cell 32: Child page depth differs Page 10683: btreeInitPage() returns error code 11 On page 10487 at right child: invalid page number 10732 Page 10642: btreeInitPage() returns error code 11 On tree page 10489 cell 32: Child page depth differs Page 10684: btreeInitPage() returns error code 11 On page 10489 at right child: invalid page number 10733 Page 10643: btreeInitPage() returns error code 11 On tree page 10491 cell 32: Child page depth differs Page 10685: btreeInitPage() returns error code 11 On page 10491 at right child: invalid page number 10734 Page 10640: btreeInitPage() returns error code 11 On tree page 6039 cell 19: Child page depth differs Page 10691: btreeInitPage() returns error code 11 On tree page 6039 cell 21: Child page depth differs Page 10692: btreeInitPage() returns error code 11 On tree page 5411 cell 36: Child page depth differs On tree page 5411 cell 37: Child page depth differs Page 10606: btreeInitPage() returns error code 11 On tree page 10314 cell 82: Child page depth differs Page 10607: btreeInitPage() returns error code 11 Page 10614: btreeInitPage() returns error code 11 Page 10616: btreeInitPage() returns error code 11 Page 10621: btreeInitPage() returns error code 11 Page 10627: btreeInitPage() returns error code 11 Page 10629: btreeInitPage() returns error code 11 Page 10634: btreeInitPage() returns error code 11 Page 10639: btreeInitPage() returns error code 11 Page 10645: btreeInitPage() returns error code 11 Page 10649: btreeInitPage() returns error code 11 Page 10652: btreeInitPage() returns error code 11 Page 10657: btreeInitPage() returns error code 11 Page 10662: btreeInitPage() returns error code 11 Page 10664: btreeInitPage() returns error code 11 Page 10669: btreeInitPage() returns error code 11 Page 10673: btreeInitPage() returns error code 11 Page 10675: btreeInitPage() returns error code 11 Page 10680: btreeInitPage() returns error code 11 Page 10690: btreeInitPage() returns error code 11 Page 10694: btreeInitPage() returns error code 11 Page 10699: btreeInitPage() returns error code 11 Page 10701: btreeInitPage() returns error code 11 Page 10705: btreeInitPage() returns error code 11 Page 10710: btreeInitPage() returns error code 11 Page 10712: btreeInitPage() returns error code 11 Page 10716: btreeInitPage() returns error code 11 Page 10723: btreeInitPage() returns error code 11 On tree page 10314 cell 110: invalid page number 10725 On page 10314 at right child: invalid page number 10730 Page 10611: btreeInitPage() returns error code 11 On tree page 10409 cell 39: Child page depth differs Page 10617: btreeInitPage() returns error code 11 Page 10623: btreeInitPage() returns error code 11 Page 10630: btreeInitPage() returns error code 11 Page 10636: btreeInitPage() returns error code 11 Page 10646: btreeInitPage() returns error code 11 Page 10653: btreeInitPage() returns error code 11 Page 10658: btreeInitPage() returns error code 11 Page 10665: btreeInitPage() returns error code 11 Page 10670: btreeInitPage() returns error code 11 Page 10677: btreeInitPage() returns error code 11 Page 10687: btreeInitPage() returns error code 11 Page 10695: btreeInitPage() returns error code 11 Page 10702: btreeInitPage() returns error code 11 Page 10707: btreeInitPage() returns error code 11 Page 10713: btreeInitPage() returns error code 11 Page 10720: btreeInitPage() returns error code 11 On page 10409 at right child: invalid page number 10726 Page 10612: btreeInitPage() returns error code 11 On tree page 10411 cell 39: Child page depth differs Page 10618: btreeInitPage() returns error code 11 Page 10624: btreeInitPage() returns error code 11 Page 10631: btreeInitPage() returns error code 11 Page 10637: btreeInitPage() returns error code 11 Page 10647: btreeInitPage() returns error code 11 Page 10654: btreeInitPage() returns error code 11 Page 10659: btreeInitPage() returns error code 11 Page 10666: btreeInitPage() returns error code 11 Page 10671: btreeInitPage() returns error code 11 Page 10678: btreeInitPage() returns error code 11 Page 10688: btreeInitPage() returns error code 11 Page 10696: btreeInitPage() returns error code 11 Page 10703: btreeInitPage() returns error code 11 Page 10708: btreeInitPage() returns error
[sqlite] hard links and SQLite
On 11 Jan 2016 9:06 PM, Rowan Worth wrote: > On 12 January 2016 at 03:00, Felipe Gasper wrote: > >> On 11 Jan 2016 1:45 PM, Scott Hess wrote: >> >>> >>> As far as preventing the other process from using it before the schema >>> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, >>> the schema does not exist. If you create the schema as a transaction, >>> that >>> will be atomic. >>> >> >> But in order for that SELECT to avert TOCTTOU errors, we?d have to do >> BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. >> > > * if it returns SQLITE_OK and at least one row, the schema has been created > and it can proceed Agreed. > * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet Sure; however, by the time you do the next action it?s possible that something else will be creating the schema. This is the condition that I?m trying to avoid. > * if it returns SQLITE_BUSY, the schema is in the process of being created > (or there's some other EXCLUSIVE transaction in progress, or a transaction > is being committed at this very moment, or an in-progress write transaction > has spilled sqlite's memory cache) Agreed. > > As Scott also hinted at, hard linking DB files is dangerous because > connections against each of the links will use different -journal files. In > the event that your schema creation process (or the machine its running on) > crashes halfway through COMMIT, connecting to the permanent database will > cause corruption to be observed (because it's not aware of the other > journal file and thus can't rollback the partial transaction). I suppose we could have each connection: BEGIN EXCLUSIVE LOCK Check for schema: if OK, then ROLLBACK; else, create and COMMIT. Just seems a bit funny to do a LOCK at the begin of each and every connection, but I suppose not too bad since every write does an exclusive lock in the first place. > This may also be possible if another process simply connects to the > permanent DB at just the wrong time (ie. halfway through the schema > creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained > across hardlinks but I haven't checked if this is specified by the standard > or file-system dependent. Yeah, we had quite a few corruptions. I think only from the DBs that we create this way, but it was odd that none of our folks could reproduce the failure. > Do your different connections actually run different code? Or are you > potentially in the situation where two threads are trying to create the > same DB at the same time (each via their own .tmp file)? Same code, just different processes. We?ve just noted over the years with race conditions that that ?if it can fail, it will?, so we try to be as bulletproof as we can. -FG
[sqlite] hard links and SQLite
On 11 Jan 2016 1:45 PM, Scott Hess wrote: > > As far as preventing the other process from using it before the schema > exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0, > the schema does not exist. If you create the schema as a transaction, that > will be atomic. But in order for that SELECT to avert TOCTTOU errors, we?d have to do BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation. That would seem to get expensive? -FG
[sqlite] hard links and SQLite
On 11 Jan 2016 1:30 PM, Bernardo Sulzbach wrote: >> What have folks here done to avoid the race condition originally described? >> I?ve seen some documentation of SQLite and hard links but > > Your message appears to be truncated. Oops! Nothing earth-shattering ? I meant to put ?but didn?t think the avisos about links quite sounded like my situation?. > > What if you move the hard link creation part to right after the commit? The problem is that we didn?t actually see the corruption in internal testing and have never recreated it ourselves ? so, I have no way of knowing now whether anything really fixes the issue. We actually did implement out solution like that originally; the problem was that we were doing data import also prior to the hard-link creation, so for DBs with lots of data we were crashing servers. I didn?t consider, though, that schema creation might be more error-prone than importing data? Interesting. -FG
[sqlite] hard links and SQLite
Hi all, To prevent race conditions where a 2nd process accesses a newly-created SQLite file before the creator process can set up the schema, I?ve implemented logic like this: - create SQLite file as a .tmp beside permanent location - BEGIN EXCLUSIVE LOCK - hard-link the temp file to the permanent location - create schema - COMMIT - unlink temp file This prevents the problem because the permanent file never exists unlocked prior to the schema existence. The 2nd process would either fail to open the DB because it?s locked or open it and see the schema. I?m seeing some corruption in our SQLite files now but have had a hard time tracking down the cause. Could it be because of the above logic? What have folks here done to avoid the race condition originally described? I?ve seen some documentation of SQLite and hard links but -FG
[sqlite] SQLite, fork(), and exec()
Hi everyone, Does SQLite need to have nothing at all to do with fork()? Even when the fork() immediately precedes an exec(), and the exec()ed command has nothing to do with SQLite? Or is it only a problem if the child process *uses* SQLite? -F
[sqlite] insert or increment?
Hello, I?m looking for logic like this: INSERT data ON CONFLICT oldrow.col1 += oldrow.col2 Does SQLite have anything that would make this simpler than: 1) UPDATE 2) if 0 rows updated, then INSERT Thank you! ?? -FG
[sqlite] SQLITE_OPEN_EXCLUSIVE
"Violent agreement". :-) Sent from my android device. -Original Message- From: Simon Davies <simon.james.dav...@gmail.com> To: General Discussion of SQLite Database Sent: Sun, 15 Mar 2015 9:08 AM Subject: Re: [sqlite] SQLITE_OPEN_EXCLUSIVE On 15 March 2015 at 12:54, Felipe Gasper wrote: > This seems to support the idea that O_EXCL is SQLITE_OPEN_EXCLUSIVE..? "The SQLITE_OPEN_EXCLUSIVE flag is always used in conjunction with the SQLITE_OPEN_CREATE flag, which are both directly analogous to the O_EXCL and O_CREAT flags of the POSIX open() API. The SQLITE_OPEN_EXCLUSIVE flag, when paired with the SQLITE_OPEN_CREATE, is used to indicate that file should always be created, and that it is an error if it already exists. It is not used to indicate the file should be opened for exclusive access." I think that is pretty explicit. Regards, Simon ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_EXCLUSIVE
This seems to support the idea that O_EXCL is SQLITE_OPEN_EXCLUSIVE..? Sent from my android device. -Original Message- From: Simon Davies <simon.james.dav...@gmail.com> To: General Discussion of SQLite Database Sent: Sun, 15 Mar 2015 4:16 AM Subject: Re: [sqlite] SQLITE_OPEN_EXCLUSIVE On 15 March 2015 at 06:06, Felipe Gasper wrote: > Hi all, > > Does the SQLITE_OPEN_EXCLUSIVE flag exist as analogous to O_EXCL in > C open()? i.e., is this how I can say, ?open a database, but only if it?s a > new database?? https://www.sqlite.org/capi3ref.html#sqlite3vfsxopen > Thank you! > > -Felipe Gasper > Houston, TX Regards, Simon ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_EXCLUSIVE
That approach isn't race-safe, though. Why does SQLite not allow open via O_EXCL? -FG Sent from my android device. -Original Message- From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database Sent: Sun, 15 Mar 2015 7:44 AM Subject: Re: [sqlite] SQLITE_OPEN_EXCLUSIVE On 15 Mar 2015, at 6:06am, Felipe Gasper wrote: > is this how I can say, ?open a database, but only if it?s a new database?? There is no mode in SQLite which does that. You can tell it to do the opposite (only open an existing file, return an error if no file exists) but not the way you asked about. You will have to use file operations to find out whether the file already exists. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_EXCLUSIVE
Hi all, Does the SQLITE_OPEN_EXCLUSIVE flag exist as analogous to O_EXCL in C open()? i.e., is this how I can say, ?open a database, but only if it?s a new database?? Thank you! -Felipe Gasper Houston, TX
