sqlite: Unable to open database file

Today geary couldn’t start up, saying the database was potentially corrupted. There was an option to just rebuild the database file, but as I like my history of emails and didn’t want to download all of them again, I gave up.

At first. :)

As it turned out, there was a weird empty geary.db-journal file right alongside the database, and it was owned by root. As geary (and sqlite) couldn’t access it, it couldn’t do any database modifications. (According to https://sqlite.org/tempfiles.html, that file is used for “rollback journals”.)

In the end, I just deleted the file (geary.db-journal), and geary worked again.

Here’s my approximate debugging session:

# start geary, see an error

# hm...
$ geary --debug
unable to open database [...] 'CREATE TABLE CorruptionCheckTable (text_col TEXT)'

# hm, let's try it manually
$ sqlite3 ~/.local/share/geary/<my-account>/geary.db
sqlite> CREATE TABLE CorruptionCheckTable (text_col TEXT);
unable to open database

# oops?  no idea what's going on, let's do something else/more important

# ...

# search for "sqlite create table error unable to open database file" on the web

# find http://fredericiana.com/2014/11/29/sqlite-error-open-database-file/

# file permissions?!
$ ls -la ~/.local/share/geary/<my-account>/
-rw-r--r-- 1 lu   lu   374161408 Feb  2 14:43 geary.db
-rw-r--r-- 1 root root 0         Feb  1 10:18 geary.db-journal

# why is there an empty root-owned file here?

# don't know, let's delete it
$ sudo rm ~/.local/share/geary/<my-account>/geary.db-journal

# and it works again