catching sqlite errors...what to know

[C M]

I'm working on a database program and have found three different kinds
of SQLite errors now and then, only 1 of which is known as to what is
causing it:

1) locked database (due to using SQLite Database Browser to change a
database but not Saving it)
2) sqlite3.OperationalError: SQL logic error or missing database
(happens very rarely. No idea why)
3) sqlite3.OperationalError: disk I/O error (also happens very rarely.
No idea why)

So, because these errors, if not handled, lock up my application, I
have to go through the entire program and find every read or write to
it and make contingencies for these--or other--errors. To assist
that, I've put all database access in a wrapper function so that all
accesses go through that function and I can catch exceptions.
However, I still have to take a lot of time to go through all the code
and make sure the code can handle whatever that wrapper returns when
there is an error. This will take a lot of time.

Therefore, to make sure I'm approaching this task right, what I'd like
to know from this list are answers to a few relevant questions...

- What might be (likely) causing errors (2) and (3)--and how could
they be prevented?

- What other common errors should I be alert for (though these are the
only ones I've ever seen)?

- I'd like to be able to potentially save a great deal of work by
testing that there is an unlocked connection to a working database as
a first step in each module *before* a lot of code runs, and if it
fails, then just stop before I have to back out of a lot of GUI code,
etc. How "risky" is that shortcut? (that is, how much chance is there
of a database error happening anyway once I've done that initial
check?)

- Any pointers to design principles for database applications (future
ones, for me) that make managing possible errors most efficient?

Thanks,
Che

[Simon Slavin]

Post by C M
1) locked database (due to using SQLite Database Browser to change a
database but not Saving it)
2) sqlite3.OperationalError: SQL logic error or missing database
(happens very rarely. No idea why)
3) sqlite3.OperationalError: disk I/O error (also happens very rarely.
No idea why)

You already know the cause of number (1). I bet the same thing is causing (2) and (3). One of the problems with reporting them here is that they are not SQLite errors (i.e. not one of the error codes listed here

<http://www.sqlite.org/c3ref/c_abort.html>

) but seem to be errors generated by a framework or API you're using.

If you're using SQLite Database Browser just to make trivial changes and do the odd SELECT, could you try using the SQLite shell tool instead, just for debugging purposes ? The shell tool was written by the same team who write SQLite itself, and we understand every little thing it does. If you get any errors while using it they will understand exactly what caused them. For instance knowing what might cause a locked database while using the shell tool is very simple: a BEGIN without a ROLLBACK or a COMMIT. It's far harder to figure out when a GUI might have the database locked.

Lastly, to dramatically reduce the number of (1)s you get, use this call

<http://www.sqlite.org/c3ref/busy_timeout.html>

and set the timeout to a few seconds. The default timeout is zero, meaning that any locks of the database by a different process will immediately result in an error, which isn't what most programmers want most of the time.

Simon.

[C M]

On Sun, Jul 1, 2012 at 12:12 PM, Simon Slavin <slavins-***@public.gmane.org> wrote:

Thanks for the help. Sorry to not get back sooner.

Post by Simon Slavin

Post by C M
1) locked database (due to using SQLite Database Browser to change a
database but not Saving it)
2) sqlite3.OperationalError: SQL logic error or missing database
(happens very rarely. No idea why)
3) sqlite3.OperationalError: disk I/O error (also happens very rarely.
No idea why)

You already know the cause of number (1). I bet the same thing is causing (2) and (3).

I don't think a locked database (1) is causing (3), because I have
observed the disk I/O error when the database is clearly not locked.
It is also intermittent. I suspect it might be a real problem with my
hard disk, which is old and nearly filled. (Which is actually useful,
since I'd love to have a solution to this issue for users with
similarly decrepit hard drives!).

Post by Simon Slavin
One of the problems with reporting them here is that they are not SQLite errors (i.e.
not one of the error codes listed here
<http://www.sqlite.org/c3ref/c_abort.html>
) but seem to be errors generated by a framework or API you're using.

Yes, these are the errors generated by the sqlite3 module in Python
(formerly, I think, called pysqlite). There is probably a mapping out
there somewhere between SQlite errors and these, and so I'll try to
find it.

Thanks.
Che

[Roger Binns]

Post by C M
So, because these errors, if not handled, lock up my application,

Your message indicates you are using Python. You should be using
transactions and context managers. This ensures that things either
complete or don't, not some half way state.


http://pysqlite.readthedocs.org/en/latest/sqlite3.html#using-the-connection-as-a-context-manager

The errors always turn into exceptions. You should follow best practises.

http://docs.python.org/howto/doanddont.html#except
http://eli.thegreenplace.net/2008/08/21/robust-exception-handling/

http://www.doughellmann.com/articles/how-tos/python-exception-handling/index.html

Finally you should have a test suite for your code. The good news is that
it is fairly easy to cause some of the errors. For example if you would
like a database to be locked then open it in a new connection and execute
"BEGIN EXCLUSIVE".

Roger

[C M]

Thanks for the help, just getting back now.

Post by Roger Binns
Your message indicates you are using Python. You should be using
transactions and context managers. This ensures that things either
complete or don't, not some half way state.

I thought SQlite never did anything in a half way state anyway? I
thought that if something failed to be written it was rolled back?
(Pardon if I'm mangling the terminology or misunderstanding it badly).

Post by Roger Binns
Finally you should have a test suite for your code. The good news is that
it is fairly easy to cause some of the errors. For example if you would
like a database to be locked then open it in a new connection and execute
"BEGIN EXCLUSIVE".

Any idea how to produce the other errors? Like the Disk I/O error
given by Python's sqlite3 module?

Thanks again,
Che

[Kees Nuyt]

Post by C M
I thought SQlite never did anything in a half way state anyway? I
thought that if something failed to be written it was rolled back?
(Pardon if I'm mangling the terminology or misunderstanding it badly).

Unless you use things like
PRAGMA synchronous=OFF;
PRAGMA journal_mode=OFF;

http://www.sqlite.org/pragma.html#toc

or do something else that can cause corruption.

http://www.sqlite.org/howtocorrupt.html