Kolibri: Automatically recover from Malformed Database on disposable SQLite dbs

[benjaoming]

Observed behavior

A report has come on a Malformed Database exception for ~/.kolibri/process_cache/cache.db. This should of course not happen, but it seems inevitable that it occurs in multi-process environments and/or because of unclean shutdowns.

Manual work-around

The manual fix is to delete the database files, since they are disposable:

• ~/.kolibri/process_cache/cache.db
• ~/.kolibri/job_storage.sqlite3
• ~/.kolibri/notifications.sqlite3

On another note, why is it called cache.db when the other databases are consistently called .sq slite3?

Automatic work-around proposals

1. Catch these exceptions and delete the database, prompting for a restart of Kolibri
2. Add a sanity check, opening the database and deleting it upon failure
3. Add a new management command that refreshes these smaller disposable databases

Expected behavior

I think we can take this in two steps: Fail gracefully and add user guidance, possibly automatically purging a disposable database if harmless.

User-facing consequences

Kolibri doesn't start in rare cases

Errors and logs

$ kolibri status
Traceback (most recent call last):
  File "/usr/bin/kolibri", line 11, in <module>
    load_entry_point('kolibri==0.13.0', 'console_scripts', 'kolibri')()
  File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 542, in load_entry_point
    return get_distribution(dist).load_entry_point(group, name)
  File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 2569, in load_entry_point
    return ep.load()
  File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 2229, in load
    return self.resolve()
  File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 2235, in resolve
    module = __import__(self.module_name, fromlist=['__name__'], level=0)
  File "/usr/lib/python3/dist-packages/kolibri/utils/cli.py", line 28, in <module>
    from . import server
  File "/usr/lib/python3/dist-packages/kolibri/utils/server.py", line 17, in <module>
    from kolibri.core.deviceadmin.utils import schedule_vacuum
  File "/usr/lib/python3/dist-packages/kolibri/core/deviceadmin/utils.py", line 13, in <module>
    from kolibri.core.tasks.main import scheduler
  File "/usr/lib/python3/dist-packages/kolibri/core/tasks/main.py", line 9, in <module>
    from kolibri.core.tasks.queue import Queue
  File "/usr/lib/python3/dist-packages/kolibri/core/tasks/queue.py", line 1, in <module>
    from kolibri.core.tasks.job import Job
  File "/usr/lib/python3/dist-packages/kolibri/core/tasks/job.py", line 7, in <module>
    from kolibri.core.tasks.utils import current_state_tracker
  File "/usr/lib/python3/dist-packages/kolibri/core/tasks/utils.py", line 9, in <module>
    from kolibri.deployment.default.cache import diskcache_cache
  File "/usr/lib/python3/dist-packages/kolibri/deployment/default/cache.py", line 16, in <module>
    diskcache_cache = Cache(diskcache_location, disk_pickle_protocol=pickle_protocol)
  File "/usr/lib/python3/dist-packages/kolibri/dist/diskcache/core.py", line 487, in __init__
    sql = self._sql_retry
  File "/usr/lib/python3/dist-packages/kolibri/dist/diskcache/core.py", line 679, in _sql_retry
    sql = self._sql
  File "/usr/lib/python3/dist-packages/kolibri/dist/diskcache/core.py", line 674, in _sql
    return self._con.execute
  File "/usr/lib/python3/dist-packages/kolibri/dist/diskcache/core.py", line 661, in _con
    settings = con.execute(select).fetchall()
sqlite3.DatabaseError: database disk image is malformed

Steps to reproduce

This may be difficult, may have to create a mock test case instead.

Context

A complicated Kolibri 0.12=>0.13.0 upgrade that had multiple issues.

[jredrejo]

I think that before using this approach we should discover why the malformed problem appears. In recent versions of kolibri we have been able to get rid of it in db.sqlite3. If now we have the problem in other dbs it's a sympthon of a problem in the code, with several threads trying to write to a database that has been left incorrectly open.

[rtibbles]

I think we should do both :)

[benjaoming]

Agreeing with @jredrejo - such exceptions should never be caught in complete silence, but logged loudly. Perhaps a setting can be added to switch off this behavior such that automated stress testing can catch it. I am also wondering if this can be related to recently reported zombie processes.

[rtibbles]

My main guess for why this is happening is because the db in question is being used inside worker threads to prevent synchronous access to the main database (to try to avoid database write errors in asynchronous tasks), I would have to check the code of the library precisely, but I would assume it is using a context manager to open the database, and so should cleanup on sigint.

My assumption then is that this may be a result of a force kill, which may be harder to gracefully manage.

[indirectlylit]

the db in question is being used inside worker threads to prevent synchronous access to the main database

Does this mean we're just pushing the concurrent access issue from one DB to another?

[rtibbles]

To some extent - although the chance of synchronous writes is significantly lower on this DB, and the corruption issue means that non-essential data is being corrupted, rather than the default database.

[lyw07]

fixed in #6511 and #6523