Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, 27 Oct 2014 16:49:42 -0500 Nico Williamswrote: > If it's not too much to ask for then SQLite3 ought to: a) check for > duplicates by canonicalized path (but keep in mind that this can be > difficult to do portably, or without obnoxious length limitations on > Windows), The name is not the file. Even Windows has hard links these days. :-( > then b) check for duplicates by st_dev/st_ino where available. Not portable, but at least reliable. :-) The Googles reveal the BY_HANDLE_FILE_INFORMATION structure returned by GetFileInformationByHandle (http://msdn.microsoft.com/en-us/library/windows/desktop/aa364952 (v=vs.85).aspx) includes "a unique identifier that is associated with a file". Why that value couldn't be returned in st_ino by fstat(2) would seem to to rest less on technical and more on commercial concerns. The "File ID" discussion (Remarks, http://msdn.microsoft.com/en-us/library/windows/desktop/aa363788 (v=vs.85).aspx) is facinating in a morbid way. On reflection, I don't think a file uniqueness function is something SQLite need provide in any form. The application supplies the filename of the database to attach. It is capable of calling fstat or GetFileInformationByHandle as appropriate if required, without help from SQLite. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 5:12 PM, Simon Slavinwrote: > On 27 Oct 2014, at 9:49pm, Nico Williams wrote: >> If it's not too much to ask for then SQLite3 ought to: a) check for >> duplicates by canonicalized path (but keep in mind that this can be >> difficult to do portably, or without obnoxious length limitations on >> Windows), then b) check for duplicates by st_dev/st_ino where >> available. > > Checking for cannonical path would seem to be important to improving this > functionality. There's a function to do this under OS X (resolve links, then > do "stringByStandardizingPath") and Windows ("PathCchCanonicalize"). BSD has > "realpath(3)". It should be possible for most VFSes. Yes, but there's portability concerns. For example, PathCchCanonicalize() is not available before Windows 8 / Windows Server 2012. (Also, use PathCchCanonicalizeEx() instead...) (Windows also has an inode number on NTFS, as well as hardlinks, which is nice.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On 27 Oct 2014, at 9:49pm, Nico Williamswrote: > On Mon, Oct 27, 2014 at 3:17 PM, Stephan Beal wrote: >> That's conceptually the same problem we're trying to solve here: keep the >> public db names stable, regardless of where/how they're actually attached. > > Yes, I think that's desirable. > > If it's not too much to ask for then SQLite3 ought to: a) check for > duplicates by canonicalized path (but keep in mind that this can be > difficult to do portably, or without obnoxious length limitations on > Windows), then b) check for duplicates by st_dev/st_ino where > available. Checking for cannonical path would seem to be important to improving this functionality. There's a function to do this under OS X (resolve links, then do "stringByStandardizingPath") and Windows ("PathCchCanonicalize"). BSD has "realpath(3)". It should be possible for most VFSes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 3:17 PM, Stephan Bealwrote: > That's conceptually the same problem we're trying to solve here: keep the > public db names stable, regardless of where/how they're actually attached. Yes, I think that's desirable. If it's not too much to ask for then SQLite3 ought to: a) check for duplicates by canonicalized path (but keep in mind that this can be difficult to do portably, or without obnoxious length limitations on Windows), then b) check for duplicates by st_dev/st_ino where available. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 8:52 PM, Nico Williamswrote: > Well, it could do something to detect duplicates, but it may not be > easy (or even possible) to portably detect that two DB files are the > same file. The same is true for the application, of course, but it > seems reasonable [to me anyways] to put this burden on the > application. > It doesn't seem unreasonable, i agree, and i have no doubts that our use case is an odd one. (Background: we're migrating the fossil monolothic app to a library API, and that's where the history of these DBs, and how they are handled, originates.) > Still, I can see how it could be useful for SQLite3 to detect dup DB > ATTACHes. Suppose you had a two-DB application, but since there's no > schema conflicts you later decide that it'd be easier (e.g., easier to > backup, sync, manage) to merge the two DBs into one. But you still > have older code that wants two DBs... > That's conceptually the same problem we're trying to solve here: keep the public db names stable, regardless of where/how they're actually attached. Background: my initial feature request (from a thread in July) was the ability to assign an alias to any given db handle, such that "myalias" would work just like "main" or "attachedName", but ATTACH was suggested and turned out to work like a charm until Dave went and broke it with a self-locking query (and then, bless his heart, went and debugged it to its conclusion) ;). > The query specifies that the destination db be locked for write and the > source db for read; which translates to two locks that cannot coexist on > one underlying db file. > > And two journals, and so on. > Oh, good point - hadn't thought of that. On a related note, as of an hour or so away we've migrated to a "" (initially ":memory:", but "" (which was new to me) seems a better fit) plus (pragma temp_store=file), and we're attaching the other DBs as needed. There's one particular use case which has hypothetically has minor breakage if the power goes out at one particular point in one specific cross-attached-db transaction, but because (as i understand it) each individual attached DB is guaranteed to be consistent within itself, the damage (if any) for our case is harmless (record ID mismatches in the "more transient" of the DBs) and would be recoverable by a routine scan which client apps do anyway, so it's unlikely that a user would ever even see it. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 3:23 AM, Hick Gunterwrote: > SQLite treats each attached database as a separate entity. Attaching the same > file twice is just asking for problems. Well, it could do something to detect duplicates, but it may not be easy (or even possible) to portably detect that two DB files are the same file. The same is true for the application, of course, but it seems reasonable [to me anyways] to put this burden on the application. Still, I can see how it could be useful for SQLite3 to detect dup DB ATTACHes. Suppose you had a two-DB application, but since there's no schema conflicts you later decide that it'd be easier (e.g., easier to backup, sync, manage) to merge the two DBs into one. But you still have older code that wants two DBs... > The query specifies that the destination db be locked for write and the > source db for read; which translates to two locks that cannot coexist on one > underlying db file. And two journals, and so on. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:59 AM, Stephan Bealwrote: > On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter wrote: > >> TEMP tables get created in database temp; which is located in "a file" or >> "in memory" depending on the SQLITE_TEMP_STORE preprocessor symbol and the >> pragma temp_store. >> > > Which reveals my ignorance on the topic ;). IIRC we aren't using a > specific temp store - we're using whatever's compiled in by default. > > So... maybe paying for a :memory: handle we "don't really use" won't be as > painful as i first thought. Just add a pragma call to ensure that we're > using disk instead of memory for temp store. > Follow-up: injecting a :memory: db as the first-opened DB turned out to be a very small change (because the code was set up for that at one point), and it turns out that using ATTACH for all three of our library-level DBs gives us three or four minor features/benefits we didn't have before. e.g. it was impossible to close one of the three DBs in one particular (and as yet hypothetical) use case, but we can now attach/detach each one at will without regard for the others or which one was opened first (as that role is now taken by the :memory: placeholder). All in all, making that change was a win. Sidebar: it turns out there are some contexts where fossil does not allow db.table qualification (namely (REFERENCES D.T) and (CREATE INDEX ... ON D.T(F)) do not allow it), but that's a topic for another thread if/when it becomes problematic (so far it's only a hypothetical problem, and not one worth losing any sleep over). @Gunter: Vielen Dank for clarifying where TEMP tables go: that misunderstanding was why i migrated away from this setup in the first place. (Und schoenen Gruss aus Muenchen!) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunterwrote: > TEMP tables get created in database temp; which is located in "a file" or > "in memory" depending on the SQLITE_TEMP_STORE preprocessor symbol and the > pragma temp_store. > Which reveals my ignorance on the topic ;). IIRC we aren't using a specific temp store - we're using whatever's compiled in by default. So... maybe paying for a :memory: handle we "don't really use" won't be as painful as i first thought. Just add a pragma call to ensure that we're using disk instead of memory for temp store. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
TEMP tables get created in database temp; which is located in "a file" or "in memory" depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 11:44 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 11:08 AM, Stephan Beal <sgb...@googlemail.com> wrote: > - TEMP tables get created in the MAIN db (assuming my memory of the > docs is correct), which means we can (though accidental misuse or > carelessness) end up filling up RAM with temporary tables (which we > use regularly to process large data amounts). This is my biggest concern with > this approach. > In fact, it seems impossible to use any db _except_ the main one for temp tables: sqlite> .databases seq name file --- --- -- 0main /home/portal/tmp/bar.db 2foo /home/portal/tmp/foo.db sqlite> create temp table foo.baz(z); Error: temporary table name must be unqualified Which rules out use of a :memory: db has the local "main" - we make use of temp tables with arbitrarily large data sets. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 11:08 AM, Stephan Bealwrote: > - TEMP tables get created in the MAIN db (assuming my memory of the docs > is correct), which means we can (though accidental misuse or carelessness) > end up filling up RAM with temporary tables (which we use regularly to > process large data amounts). This is my biggest concern with this approach. > In fact, it seems impossible to use any db _except_ the main one for temp tables: sqlite> .databases seq name file --- --- -- 0main /home/portal/tmp/bar.db 2foo /home/portal/tmp/foo.db sqlite> create temp table foo.baz(z); Error: temporary table name must be unqualified Which rules out use of a :memory: db has the local "main" - we make use of temp tables with arbitrarily large data sets. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 10:44 AM, Hick Gunterwrote: > How about always referencing all tables via attached db names? That way, > "main" is never referenced, neither explicitly nor implicitly, and is > therefore never locked. > It's looking more and more as if that's what we'll have to do, but i really wanted to avoid that because... - Paying for N+1 db handles instead of the N handles we're really using. - TEMP tables get created in the MAIN db (assuming my memory of the docs is correct), which means we can (though accidental misuse or carelessness) end up filling up RAM with temporary tables (which we use regularly to process large data amounts). This is my biggest concern with this approach. Opening a "dummy main" DB in the filesystem isn't a viable option - this is library-level code for which we don't have a directory/location which "belongs to us" which we can pollute with temp DBs (other than $TEMP/$TMP, of course, but opening a db there as a main db would be a horribly ugly kludge). @Dave: i'll research what the side effects of such a change would be. (It'll likely break more docs than code.) In the mean time, i think the workaround is to simply leave off the db names (since we know we don't have table name collisions). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
How about always referencing all tables via attached db names? That way, "main" is never referenced, neither explicitly nor implicitly, and is therefore never locked. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 09:43 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter <h...@scigames.at> wrote: > SQLite treats each attached database as a separate entity. Attaching > the same file twice is just asking for problems. > > The query specifies that the destination db be locked for write and > the source db for read; which translates to two locks that cannot > coexist on one underlying db file. > That's the thing - if we leave out the explicit DB names then it works as expected (or against expectations, depending on one's world view). It's only when adding the explicit db name qualification that it locks. i agree, attaching an opened DB is a huge kludge, but the problem is (summarized): - this app (libfossil) managed 3 different databases. Which one of those gets opened first is unknown/unknowable, and there is no requirement than any of them get opened, or maybe only a subset will. fossil(1) has the same setup but juggles the dbs somewhat differently and does not expose any interface to the outside world, so this has so far remained an "internal" problem with no effect on clients. - sqlite automatically names the first db "main," and provides to way to change that. - end effect is: client code must know which order the dbs were opened in order to know which name is correct for each logical DB. This is a painfully leaky abstraction, though. - a couple months back Simon suggested ATTACHing the db to itself so that we can effectively alias "main" to the well-known name we have specified for that db instance. It worked like a charm until Dave discovered this weird locking behaviour. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 9:58 AM, Simon Slavinwrote: > > On 27 Oct 2014, at 8:43am, Stephan Beal wrote: > > > - a couple months back Simon suggested ATTACHing the db to itself so that > > we can effectively alias "main" to the well-known name we have specified > > for that db instance. It worked like a charm until Dave discovered this > > weird locking behaviour. > > The suggestion I made originally was that sqlite3_open() should open a > dummy database -- which doesn't need to contain any tables -- as 'main'. > That way any attached database would never be 'main' too. However this > solution was rejected in your case because introducing a new file into your project 'costs' a lot of work. > That was a separate option which i had looked at beforehand, but it's far from ideal. If we can't get around this locking problem, that might be only option, but it would require a relatively painful transition of both code and documentation. The fact that sqlite behaves one way if we fully qualify the tables and another if we do not implies that the infrastructure is internally there for sqlite to figure out "is this the same DB as that one, and if so, don't lock it." While i'm generally hesitant to say, "bug," it behaving in two different ways for what is semantically the same code is arguably a bug (but _which_ of the two behaviours is correct is of course debatable). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On 27 Oct 2014, at 8:43am, Stephan Bealwrote: > - a couple months back Simon suggested ATTACHing the db to itself so that > we can effectively alias "main" to the well-known name we have specified > for that db instance. It worked like a charm until Dave discovered this > weird locking behaviour. The suggestion I made originally was that sqlite3_open() should open a dummy database -- which doesn't need to contain any tables -- as 'main'. That way any attached database would never be 'main' too. However this solution was rejected in your case because introducing a new file into your project 'costs' a lot of work. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunterwrote: > SQLite treats each attached database as a separate entity. Attaching the > same file twice is just asking for problems. > > The query specifies that the destination db be locked for write and the > source db for read; which translates to two locks that cannot coexist on > one underlying db file. > That's the thing - if we leave out the explicit DB names then it works as expected (or against expectations, depending on one's world view). It's only when adding the explicit db name qualification that it locks. i agree, attaching an opened DB is a huge kludge, but the problem is (summarized): - this app (libfossil) managed 3 different databases. Which one of those gets opened first is unknown/unknowable, and there is no requirement than any of them get opened, or maybe only a subset will. fossil(1) has the same setup but juggles the dbs somewhat differently and does not expose any interface to the outside world, so this has so far remained an "internal" problem with no effect on clients. - sqlite automatically names the first db "main," and provides to way to change that. - end effect is: client code must know which order the dbs were opened in order to know which name is correct for each logical DB. This is a painfully leaky abstraction, though. - a couple months back Simon suggested ATTACHing the db to itself so that we can effectively alias "main" to the well-known name we have specified for that db instance. It worked like a charm until Dave discovered this weird locking behaviour. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems. The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file. -Ursprüngliche Nachricht- Von: dave [mailto:d...@ziggurat29.com] Gesendet: Sonntag, 26. Oktober 2014 00:36 An: 'General Discussion of SQLite Database'; sqlite-...@sqlite.org Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal > Sent: Saturday, October 25, 2014 3:32 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and attached > databases ... > > Simon, FYI: this is the "'main' db aliasing" problem i brought up a > couple months ago, which we solved by using your suggestion: re-attach > the db directly after opening it, so that (as Dave said) all DBs have > well-known internal names regardless of what order they get opened in. > > Reproduced here with 3.8.6 on Linux/x64: > > sqlite> insert or replace into main.dest ( name, value ) values > ('allow',(select value from aux.source where name = 'allow')); > Error: database is locked Really, it's a fundamental problem irrespective of 'self attached databases', it happens any time you attach a database more than once. I wish I realized that when I first reported it. E.g.: C:\Documents and Settings\person>sqlite3 db2.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table dest ( name text, value text ); create table source sqlite> ( name text, value text ); insert into source ( name, value ) sqlite> values ( 'allow', 'yes' ); .exit C:\Documents and Settings\person>sqlite3 db1.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> attach database 'db2.db' as dest; attach database 'db2.db' as sqlite> src; .datavases unknown command or invalid arguments: "datavases". Enter ".help" for help sqlite> .databases seq name file --- --- -- 0main C:\Documents and Settings\person\db1.db 2dest C:\Documents and Settings\person\db2.db 3src C:\Documents and Settings\person\db2.db sqlite> insert or replace into dest.dest (name, value) values ('allow',(select value from src.source where name = 'allow' )); SQL error: database is locked I would imagine that a possible clean fix would be for the pager to have a 'lock count', locking only when it goes from 0 to 1. If that is actually workable, then all the rest of sqlite can blythely carry on with no modification. -dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal > Sent: Saturday, October 25, 2014 3:32 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and > attached databases ... > > Simon, FYI: this is the "'main' db aliasing" problem i > brought up a couple > months ago, which we solved by using your suggestion: re-attach the db > directly after opening it, so that (as Dave said) all DBs > have well-known > internal names regardless of what order they get opened in. > > Reproduced here with 3.8.6 on Linux/x64: > > sqlite> insert or replace into main.dest ( name, value ) values > ('allow',(select value from aux.source where name = 'allow')); > Error: database is locked Really, it's a fundamental problem irrespective of 'self attached databases', it happens any time you attach a database more than once. I wish I realized that when I first reported it. E.g.: C:\Documents and Settings\person>sqlite3 db2.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table dest ( name text, value text ); sqlite> create table source ( name text, value text ); sqlite> insert into source ( name, value ) values ( 'allow', 'yes' ); sqlite> .exit C:\Documents and Settings\person>sqlite3 db1.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> attach database 'db2.db' as dest; sqlite> attach database 'db2.db' as src; sqlite> .datavases unknown command or invalid arguments: "datavases". Enter ".help" for help sqlite> .databases seq name file --- --- -- 0main C:\Documents and Settings\person\db1.db 2dest C:\Documents and Settings\person\db2.db 3src C:\Documents and Settings\person\db2.db sqlite> insert or replace into dest.dest (name, value) values ('allow',(select value from src.source where name = 'allow' )); SQL error: database is locked I would imagine that a possible clean fix would be for the pager to have a 'lock count', locking only when it goes from 0 to 1. If that is actually workable, then all the rest of sqlite can blythely carry on with no modification. -dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On Fri, Oct 24, 2014 at 11:16 PM, Simon Slavinwrote: > Which version of SQLite are you using ? > What operating system are you using (including which version) ? > What formats are the volumes those files are stored on ? > Simon, FYI: this is the "'main' db aliasing" problem i brought up a couple months ago, which we solved by using your suggestion: re-attach the db directly after opening it, so that (as Dave said) all DBs have well-known internal names regardless of what order they get opened in. Reproduced here with 3.8.6 on Linux/x64: sqlite> insert or replace into main.dest ( name, value ) values ('allow',(select value from aux.source where name = 'allow')); Error: database is locked now without the db names: sqlite> insert or replace into dest ( name, value ) values ('allow',(select value from source where name = 'allow')); -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Friday, October 24, 2014 4:45 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and > attached databases ... > > Not gonna ask for this since you've now given enough info for > someone to reproduce and test the issue. They might come > back asking for further information but I'll leave that up to > the experts. > > Simon. Alas, but I'm going to offer it, haha. OK, it happens on Linux, too: login as: person person@192.168.173.137's password: Access denied person@192.168.173.137's password: Last login: Thu Oct 23 22:24:23 2014 [person@localhost ~]$ which sqlite3 /usr/bin/sqlite3 [person@localhost ~]$ sqlite3 db1.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> create table dest ( name text, value text ); sqlite> create table source ( name text, value text ); sqlite> insert into source ( name, value ) values ( 'allow', 'yes' ); sqlite> .exit [person@localhost ~]$ sqlite3 db1.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> attach database 'db1.db' as aux; sqlite> .databases seq name file --- --- -- 0main /home/person/db1.db 2aux /home/person/db1.db sqlite> insert or replace into main.dest ( name, value ) values ...> ('allow',(select value from aux.source where name = 'allow')); SQL error: database is locked sqlite> Looking forward to hearing back from folks. Maybe I should have put this thread on 'dev', too, so I am dong so now. (Dev q.v. thread on users for context -- it's short) -dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On 24 Oct 2014, at 10:40pm, davewrote: > Later I can try on linux, but I don't have it at my fingertips just now. I > don't mind trying with other versions of sqlite if you think it's helpful, > but I suspect it's been there forever. Not gonna ask for this since you've now given enough info for someone to reproduce and test the issue. They might come back asking for further information but I'll leave that up to the experts. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin ... > On 24 Oct 2014, at 9:54pm, davewrote: > > > Can locking be made more clever to know about aux being an alias > > for main, and effectively translate the query shown to it's > functional > > equivalent of: > >insert or replace into main.dest ( name, value ) values > ('allow',(select > > value from main.source where name = 'allow')); > > which does work (or both to 'aux' as well)? > > To help us consider this, please give us the following information: > > Which version of SQLite are you using ? > What operating system are you using (including which version) ? > What formats are the volumes those files are stored on ? > > Simon. Sure; I've duplicated the problem in 3.8.6 and 3.8.7. I have duplicated the problem on Windows; XP, 7, and 8.1 The file systems are all NTFS. Later I can try on linux, but I don't have it at my fingertips just now. I don't mind trying with other versions of sqlite if you think it's helpful, but I suspect it's been there forever. Also, I can read code and step in the debugger (which I did to find the locking issue, and how it arose, up to this point). Let me know! -dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quasi-bug related to locking, and attached databases....
On 24 Oct 2014, at 9:54pm, davewrote: > Can locking be made more clever to know about aux being an alias > for main, and effectively translate the query shown to it's functional > equivalent of: >insert or replace into main.dest ( name, value ) values ('allow',(select > value from main.source where name = 'allow')); > which does work (or both to 'aux' as well)? To help us consider this, please give us the following information: Which version of SQLite are you using ? What operating system are you using (including which version) ? What formats are the volumes those files are stored on ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users