SQL Statement invalid: SQL error or missing database

[nadav]

Hi All,

I have been encountering a mysterious exception (mysterious to me) while using Poco's SQLite support, and Poco as my infrastructure as a general.

The program is running several threads that access an SQLite database very often and are synchronized by SQLite itself.
Everything seems to work just fine most of time (usually days - weeks) but I do get an occasional exception:

SQL Statement invalid: SQL error or missing database: Iterator Error: trying to check if there is a next value

The backtrace of these exceptions usually leads to very simple SQL statements such as:

session << "BEGIN IMMEDIATE", now;
session << "UPDATE SET =:value", use(myValue), now;

This specific code runs numerous times before an exception occurs (if occurs at all) and I cannot think of any reason for it to fail later rather than sooner.
It is pretty obvious that this situation occurs due to some rare thread state, but I could not find any information that gives me any hint as to what this state might be.

So what I am asking is:
1) Does anyone know why this sort of exception occurs?
2) Can anyone think of a reason for such an exception to occur in the situation I have described?

additional info:
1) I am usually beginning and committing my own transactions
2) I am using a different session for each thread (all are taken from the Poco::SessionPool)
3) the session is passed between functions as a reference.

Thanks in advance,
Nadav.

[alex]

It's hard to tell what's going on wrong from the information you have supplied.

* What POCO code base are you using?
* Are you trying to access the same database from multiple threads/sessions?
* Can you provide a snapshot of code that reproduces the problem you are experiencing?

Alex

[nadav]

Hi Alex,

I'm using poco-1..3.1.
All threads are working on the same database.

There isn't a scenario to reproduce this problem, or at least I dont know it.
The system can sometimes run for weeks without any problems, but eventually it will happen.

here is a sample of code that usually fails, you should take into consideration that this code runs repeatedly thousands if not hundreds of thousands of times before the problem occurs:

void Database: :performBegin(Poco: :Data: :Session &rSession)
{
bool finish = false;
while(!finish)
{
try
{
rSession < < "BEGIN IMMEDIATE", now;
finish = true;
}
catch(Poco: :Data: :SQLiteException sqlEx)
{
Poco: :Thread: :sleep(100);
}
}
}

this function fails the most, but it is not the only one that fails.

[alex]

> I'm using poco-1..3.1.

I recommend upgrading to 1.3.2 (1.3.3 is even better choice, but it is not released yet (plan is some time in September).

> All threads are working on the same database.

I suspect one thread having the db locked when other thread(s) attempts to write. See this SQLite FAQ

Alex