CovSonar crashes with: sqlite3.OperationalError: attempt to write a readonly database

[fischer-hub]

Hi,

we ran into an issue where covsonar crashes with :

Traceback (most recent call last):
  File ".../sc2-autopilot2/lib/covsonar/sonar.py", line 466, in <module>
    dbm.check_db_compatibility()
  File ".../sc2-autopilot2/lib/covsonar/lib/sonardb.py", line 1302, in check_db_compatibility
    dbver = self.get_db_version()
  File ".../sc2-autopilot2/lib/covsonar/lib/sonardb.py", line 1299, in get_db_version
    return self.cursor.execute('pragma user_version').fetchone()['user_version']
sqlite3.OperationalError: attempt to write a readonly database

when trying any covsonar operation on our gisaid.db database (even just the info command). However, copying the datasbase to another file or renaming the DB makes it accessible again, with both files having the same read / write permissions so the database is not really readonly I guess:

-rwxr-x---  1 svc-linux-mf1 domänen-benutzer 709595131904 May 23 11:05 gisaid_backup.db*
-rwxr-x---  1 svc-linux-mf1 domänen-benutzer 710255894528 May 25 03:14 gisaid_copy.db*
-rwxr-x---  1 svc-linux-mf1 domänen-benutzer 710255894528 May 25 04:26 gisaid.db*

Since it doesn't seem to be a read permission issue we are not really sure whats going on..

Maybe you have some idea how to fix this?

[stephan-fuchs]

You're encountering the SQLite error attempt to write a readonly database, even though your file permissions appear to be correct. Given that file permission doesn't seem to be the issue, and the problem is resolved by creating a copy of the database file, it seems plausible that the problem is caused by a database lock.

SQLite uses locking to prevent concurrent access to the database by multiple processes. Typically, these locks are released once a transaction is completed. However, under certain circumstances, a lock might not be properly released. This can occur for a variety of reasons, including a process crashing before it can release its lock, or multiple threads within a process trying to acquire a lock concurrently. If another process or thread tries to access the database while it's locked, SQLite will return an error.

In covSonar, the database establishes a write connection only during the data import process. To minimize issues such as database locks, concurrent write connections are explicitly disallowed. However, if a system crash occurs during the data import, while the integrity of the data is preserved, the lock on the database may persist. In such cases, a persistent journal file would likely be present in the same directory as the database, contributing to the sqlite3.OperationalError: attempt to write a readonly database error.

SQLite creates a journal file as part of its ACID compliance to ensure data consistency, even in the event of an interruption during a write operation. If a journal file isn't cleaned up properly after the write operation, it can cause persistent database lock and other issues with subsequent attempts to write to the database.

Here's what you can do:

1. Check for the Journal File: Look for a file in the same directory as your database file with the same name and a -journal extension. For example, if your database is gisaid.db, the journal file will be gisaid.db-journal.

2. Safe Removal of the Journal File: If the -journal file exists, it could be locking your database. You might want to delete this file, but proceed with caution. Only remove it if you're certain that no other processes are accessing the database, as deleting the journal file while a process is still using it can lead to data corruption. Ensure that you have a backup of your database before deleting the journal file.

3. Try executing the sonar optimize command: This command rebuilds the database, repacking it into a minimal amount of disk space. Note that this operation can take a while on large databases and locks the database file for the duration.

Please exercise caution when handling database and journal files. Always maintain backups and ensure your actions won't interrupt an active process or lead to data loss.

[fischer-hub]

Hi,

removing the -journal file fixed the issue!

Thanks!