Re: [sqlite] quasi-bug related to locking, and attached databases....

[James K. Lowden]

On Mon, 27 Oct 2014 16:49:42 -0500
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), 

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....

[Nico Williams]

On Mon, Oct 27, 2014 at 5:12 PM, Simon Slavin  wrote:
> 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....

[Simon Slavin]

On 27 Oct 2014, at 9:49pm, Nico Williams  wrote:

> 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....

[Nico Williams]

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.

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....

[Stephan Beal]

On Mon, Oct 27, 2014 at 8:52 PM, Nico Williams 
wrote:

> 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....

[Nico Williams]

On Mon, Oct 27, 2014 at 3:23 AM, Hick Gunter  wrote:
> 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....

[Stephan Beal]

On Mon, Oct 27, 2014 at 11:59 AM, Stephan Beal 
wrote:

> 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....

[Stephan Beal]

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.


-- 
- 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....

[Hick Gunter]

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....

[Stephan Beal]

On Mon, Oct 27, 2014 at 11:08 AM, Stephan Beal 
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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Stephan Beal]

On Mon, Oct 27, 2014 at 10:44 AM, Hick Gunter  wrote:

> 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....

[Hick Gunter]

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....

[Stephan Beal]

On Mon, Oct 27, 2014 at 9:58 AM, Simon Slavin  wrote:

>
> 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....

[Simon Slavin]

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.

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....

[Stephan Beal]

On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter  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

Re: [sqlite] quasi-bug related to locking, and attached databases....

[Hick Gunter]

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....

[dave]

> -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....

[Stephan Beal]

On Fri, Oct 24, 2014 at 11:16 PM, Simon Slavin  wrote:

> 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....

[dave]

> -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....

[Simon Slavin]

On 24 Oct 2014, at 10:40pm, dave  wrote:

> 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....

[dave]

> -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, dave  wrote:
> 
> >  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....

[Simon Slavin]

On 24 Oct 2014, at 9:54pm, dave  wrote:

>  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

[sqlite] quasi-bug related to locking, and attached databases....

[dave]

Hi folks;
 
I may have found a locking-related bug (or not, depending on your
perspective).  In my situation, I am using a system where several databases
have been attached, and I am trying to execute a query which hangs in the
busy handler, ultimately because two locks are attempted on the same file.
 
I have distilled this down to a toy example for clarity -- it's a toy
example, so nevermind that it may look silly, it's representative of the key
features of this problem.
 
Setup of the demo db:
 
sqlite3 db1.db
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
 
Setup of the problemattic scenario:
 
sqlite3 db1.db
sqlite> attach database 'db1.db' as aux;
sqlite> .databases
seq  name file
---  ---
--
0main C:\temp\spot\db1.db
2aux  C:\temp\spot\db1.db
 
OK, nevermind if this seems unusual, this is the scenario the system I am
working with has set up beforehand (it is done under programattic control
and is not always this way depending on other circumstances.  It is part of
an attempt to give fixed symbolic names to all dbs, effectively aliasing
'main' to something deterministic).
 
So, here is the (distilled-down) query I am attempting, which is
deadlocking:
 
sqlite> insert or replace into main.dest ( name, value ) values
('allow',(select value from aux.source where name = 'allow'));
Error: database is locked
 
For this query, attempts to acquire locks on both 'main' and 'aux' are made,
but because those are really the same physical database, this fails forever.
 
Thoughts?  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)?

Thanks!
 
-dave
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users