Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

I did some tests today and demonstrated that this problem goes away if the main 
thread is forced to sleep while the helper thread is running. Since the threads 
are operating on distinct databases, I don't think this should be necessary.

If I've misunderstood the level of thread safety provided by Sqlite, please 
tell me. Otherwise, I think this may be a Sqlite bug. Perhaps some confusion is 
resulting from the fact that my "distinct" databases actually share much in 
common (a schema, and one of the databases previously had the same name as the 
first but was renamed).

I'd appreciate everyone's thoughts on this... I don't really want either thread 
to have to wait and it doesn't seem like they should have to.

Also, I do plan to upgrade my version of Sqlite to whatever the latest is on 
the site (assuming I don't have the latest version already). I've only put that 
off because I'm dealing with a fairly brittle (and critical) app here, and I 
want to tread lightly.


From: Beau Wilkinson
Sent: Tuesday, May 05, 2009 9:02 AM
To: General Discussion of SQLite Database
Subject: RE: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson <b...@mtllc.us> wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

Thanks for the reply. SQLITE_MISUSE would make sense in the scenario you 
describe. But it is difficult for me to see how I could be calling prepare with 
an unopened or closed connection. I am basically in the process of executing a 
series of statements against an open database that is exclusive to the thread. 
Everything succeeds until a random point. Is the database being closed somehow, 
and I am not realizing it? Or are my threads interacting in a way I've not 
considered?


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, May 04, 2009 5:18 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

Beau Wilkinson <b...@mtllc.us> wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle
to sqlite3_prepare.

Igor Tandetnik



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

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Igor Tandetnik]

Beau Wilkinson  wrote:
> Nevertheless, I am getting some very puzzling errors. In particular,
> there are cases where sqlite3_prepare() is the first call to cause an
> error, typically SQLITE_MISUSE.

You are passing a bad (never opened, already closed) connection handle 
to sqlite3_prepare.

Igor Tandetnik 



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

[sqlite] sqlite3_prepare returns SQLITE_MISUSE

[Beau Wilkinson]

I have inherited a large Sqlite project recently. The project is 
multi-threaded, but has thus far kept Sqlite calls in a single thread. 
Recently, I have complicated this somewhat by adding a second thread that deals 
with other, essentially independent databases. No connections, statements, etc. 
are shared. Each thread operates on independent .DB files.



Nevertheless, I am getting some very puzzling errors. In particular, there are 
cases where sqlite3_prepare() is the first call to cause an error, typically 
SQLITE_MISUSE. Here are more details:



The main thread (i.e. the one that has always interacted with Sqlite) 
periodically closes its DB file, renames it, and moves on to open and write to 
a new database using the same file name as the original file that was just 
renamed. The renaming is done at the file level, using a Windows API call. 
After all of this is complete (from the "close" of the first database to the 
"open" for the new one), a new thread is created by the first thread. This 
second thread then opens the renamed .DB file, and creates a third .DB file 
with yet another name. This thread then uses SELECT and INSERT to copy a subset 
of the second .DB file's data into this third .DB file.



For example, suppose thread 1 is writing Info.db. After some time, it will 
close this database, rename Info.db to be Info.0415Mar090040.db using an API 
call, and then re-create Info.db, open it, and start writing it. After doing 
this, it will create a second thread that will open Info.0415Mar090040, create 
a new database called Info.0415Mar090040.Incident.db, and copy a subset of the 
former to the latter using SQL commands.



So, each database should be used exclusively by a single thread. Assuming each 
thread calls sqlite3_prepare, sqlite3_bind, sqlite3_finalize, etc. in the 
correct sequence, then everything should always work regardless of how the 
threads are time-sliced.



Unfortunately, this is not the behavior I have observed. Instead, things seem 
to work most of the time, but behavior differs from run to run. This makes me 
suspect some kind of threading problem. So, my questions are:



1) Is my basic threading model workable? For example, if there's some kind of 
database name string stored internally that makes (using my example above) 
Info.db and Info.0415Mar090040.db to be really "the same" in some way, even 
after the latter is renamed, then that would explain my problems. In that case, 
I would presumably need to make a renamed copy of the database using Sqlite 
calls.



2) How can sqlite3_prepare() ever return SQLITE_MISUSE for a statement that was 
just successfully finalized? Specifically, how can the following ever reach the 
second call to exit(1)?:



// (Done using pStmt against dbSrc)

rc = _sqlite3_finalize(pStmt);



if(SQLITE_OK != rc)

{

 exit(1);

}



sprintf(szQuery,"SELECT * FROM ACCESSORIES");



rc = _sqlite3_prepare(dbSrc,szQuery, -1, , 0);



if(SQLITE_OK == SQLITE_MISUSE)

{

 exit(1);

 //How can this ever happen? If there was a problem with

 //the finalize call, then the program should have exited then.

 //Does this imply that the threads, and their databases, are

 //really connected somehow?

}



3) How am I supposed to troubleshoot this? Presumably *pStmt was in an 
inappropriate state, and it would be nice to know what state that was. 
Alternately, I guess SQLITE_MISUSE would be the appropriate error is dbSrc were 
actually closed, and I would like to develop the troubleshooting skills to 
detect whether this is the case or not. (It doesn't seem to be in this bug, 
i.e. dbSrc seems to be open).



4) Is there an update I should apply? The version of Sqlite3.h that I am 
including contains the defines SQLITE_VERSION as 3.4.0, but I also seem to be 
missing a few things, such as sqlite_prepare_v2(), sqlite3_threadsafe(), and 
sqlite3_stmt _status(). So, I am not at all sure that I'm using exactly what I 
should be for v3.4.0. (And I think that some of the functions I mentioned might 
help my basic problem.)



Many thanks in advance for your help. To some extent, I can and will continue 
debugging this (and perhaps answering the above questions) on my own. But at 
this point I'm really overwhelmed and far behind my schedule, and really want 
some help with Sqlite. If I start complaining about it, no doubt management 
will request I use something more "full-featured," and I think that would be a 
shame.


The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named. If you 
are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited. If you have received this e-mail in error, please 
immediately notify the sender and delete any copies