exception handling with sqlite db errors

C

CM

I am using SQLite with Python 2.5 for an app and every now and then
get an error when trying to write to the database. So far I haven't
been careful about noting these down, but now I would like to address
them. The two errors I've noticed are:

1) "database is locked" errors (probably due to write conflicts when
using SQLite Database Browser to check and change the data while my
app was trying to write to the db; this might be a problem for users
if they too want to edit their data that way while running the app,
though that scenario is pretty unlikely).

2) Today for the first time,"sqlite3.OperationalError: attempt to
write a readonly database"--though I never set this database to be
read-only. When I tried it a second time, it worked and didn't give
this error.

What I would like is a way to innoculate my app from these errors such
that if they occur they don't cause any problems with the app. I'm
not even sure if they do cause problems, since I can't figure out a
way to reproduce the errors and see whether it causes the app to hang
or not update the GUI properly, etc.

Maybe it's not much of an issue, but I think it would be a shame if
occasional hangs/crashes could be caused by these (rare?) database
conflicts if there is a good approach for avoiding them. I guess I
could put every last write to the db in a try/except block but I
thought there should be a more general solution, since that will
require many such exceptions and seems inelegant.

Any suggestions for addressing this are appreciated.

Che
 
A

Aahz

Maybe it's not much of an issue, but I think it would be a shame if
occasional hangs/crashes could be caused by these (rare?) database
conflicts if there is a good approach for avoiding them. I guess I
could put every last write to the db in a try/except block but I
thought there should be a more general solution, since that will
require many such exceptions and seems inelegant.

Wrap all your uses of sqlite into a function that does the try/except;
you only write the code once, then. As you progress, you can also
change the code to retry operations. Here's some ugly code I wrote on
top of SQLObject:

from sqlobject.dbconnection import registerConnection
from sqlobject.sqlite.sqliteconnection import SQLiteConnection

class RetrySQLiteConnection(SQLiteConnection):
"""
Because SQLite is not really concurrent, having multiple processes
read/write can result in locked DB failures. In addition, SQLObject
doesn't properly protect operations in transations, so you can get
spurious DB errors claiming that the DB is corrupt because of
foreign key integrity failures.

This subclass retries DatabaseError and OperationalError
exceptions.
"""
MAX_RETRIES = 4
SAFE_DB_ERROR = [
'database disk image is malformed',
'file is encrypted or is not a database',
]

def _safe_db_error(self, exception):
err = str(exception).lower()
for safe_err in self.SAFE_DB_ERROR:
if safe_err in err:
return True
return False

def _check_integrity(self):
conn = self.getConnection()
try:
i = 0
while True:
i += 1
try:
cursor = conn.cursor()
query = "pragma integrity_check"
SQLiteConnection._executeRetry(self, conn, cursor, query)
result = cursor.fetchall()
if result == [('ok',)]:
return True
else:
logging.error("Bad integrity result: %s", result)
return False
except DatabaseError, e:
if i < self.MAX_RETRIES:
logging.info('integrity_check, try #%s: %s', i, e)
time.sleep(2)
else:
logging.error('integrity_check, try #%s: %s', i, e)
raise
finally:
self.releaseConnection(conn)

def _executeRetry(self, conn, cursor, query):
i = 0
while True:
i += 1
try:
return SQLiteConnection._executeRetry(self, conn, cursor, query)
except OperationalError, e:
if i < self.MAX_RETRIES:
logging.warn('OperationalError, try #%s: %s', i, e)
time.sleep(10)
else:
logging.error('OperationalError, try #%s: %s', i, e)
raise
except DatabaseError, e:
if e.__class__ is not DatabaseError:
# Don't retry e.g. IntegrityError
raise
if not self._safe_db_error(e):
# Only retry specific errors
raise
if not self._check_integrity():
raise
if i < self.MAX_RETRIES:
logging.warn('DatabaseError, try #%s: %s', i, e)
time.sleep(0.5)
else:
logging.error('DatabaseError, try #%s: %s', i, e)
raise

def conn_builder():
return RetrySQLiteConnection

registerConnection(['retrysqlite'], conn_builder)

def init():
dbpath = os.path.join(common.getSyncDataPath(), app.dbname)
connection_string = "retrysqlite:" + dbpath
global _connection
_connection = connectionForURI(connection_string)
 
C

CM

CM  said:
Maybe it's not much of an issue, but I think it would be a shame if
occasional hangs/crashes could be caused by these (rare?) database
conflicts if there is a good approach for avoiding them.  I guess I
could put every last write to the db in a try/except block but I
thought there should be a more general solution, since that will
require many such exceptions and seems inelegant.

Wrap all your uses of sqlite into a function that does the try/except;
you only write the code once, then.  As you progress, you can also
change the code to retry operations.  Here's some ugly code I wrote on
top of SQLObject:

from sqlobject.dbconnection import registerConnection
from sqlobject.sqlite.sqliteconnection import SQLiteConnection

class RetrySQLiteConnection(SQLiteConnection):
    """
    Because SQLite is not really concurrent, having multiple processes
    read/write can result in locked DB failures.  In addition, SQLObject
    doesn't properly protect operations in transations, so you can get
    spurious DB errors claiming that the DB is corrupt because of
    foreign key integrity failures.

    This subclass retries DatabaseError and OperationalError
    exceptions.
    """
    MAX_RETRIES = 4
    SAFE_DB_ERROR = [
        'database disk image is malformed',
        'file is encrypted or is not a database',
        ]

    def _safe_db_error(self,exception):
        err = str(exception).lower()
        for safe_err in self.SAFE_DB_ERROR:
            if safe_err in err:
                return True
        return False

    def _check_integrity(self):
        conn = self.getConnection()
        try:
            i = 0
            while True:
                i += 1
                try:
                    cursor = conn.cursor()
                    query = "pragma integrity_check"
                    SQLiteConnection._executeRetry(self, conn, cursor, query)
                    result = cursor.fetchall()
                    if result == [('ok',)]:
                        return True
                    else:
                        logging.error("Bad integrity result: %s", result)
                        return False
                except DatabaseError, e:
                    if i < self.MAX_RETRIES:
                        logging.info('integrity_check, try #%s: %s', i, e)
                        time.sleep(2)
                    else:
                        logging.error('integrity_check, try #%s: %s', i, e)
                        raise
        finally:
            self.releaseConnection(conn)

    def _executeRetry(self, conn, cursor, query):
        i = 0
        while True:
            i += 1
            try:
                return SQLiteConnection._executeRetry(self, conn, cursor, query)
            except OperationalError, e:
                if i < self.MAX_RETRIES:
                    logging.warn('OperationalError, try #%s: %s', i, e)
                    time.sleep(10)
                else:
                    logging.error('OperationalError, try #%s: %s', i, e)
                    raise
            except DatabaseError, e:
                if e.__class__ is not DatabaseError:
                    # Don't retry e.g. IntegrityError
                    raise
                if not self._safe_db_error(e):
                    # Only retry specific errors
                    raise
                if not self._check_integrity():
                    raise
                if i < self.MAX_RETRIES:
                    logging.warn('DatabaseError, try #%s: %s', i, e)
                    time.sleep(0.5)
                else:
                    logging.error('DatabaseError, try #%s: %s', i, e)
                    raise

def conn_builder():
    return RetrySQLiteConnection

registerConnection(['retrysqlite'], conn_builder)

def init():
    dbpath = os.path.join(common.getSyncDataPath(), app.dbname)
    connection_string = "retrysqlite:" + dbpath
    global _connection
    _connection = connectionForURI(connection_string)
--
Aahz ([email protected])           <*>        http://www.pythoncraft.com/

"...if I were on life-support, I'd rather have it run by a Gameboy than a
Windows box."  --Cliff Wells

Thanks, Aahz, I'll try to implement something along these lines.

Che
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top