Stopping daemon during sqlite DB setup results in corruption

[SunilMohanAdapa]

Description

When using sqlite database, asking Synapse to stop during database setup results in database corruption.

Steps to reproduce

• Install matrix-synapse using Debian package: apt install matrix-synapse. Provide the server name during installation.
• Synapse starts running and logs a message that it is Preparing database 'master'.
• At this instant, stop Synapse by running systemctl stop matrix-synapse.
• Start matrix-synapse again. This is results in matrix-synapse exiting after logging Preparing database 'master'.

The systemd service continuously tries to start the service and fails. This results in irrecoverable situation for the user (without editing/deleting the database).

020-09-14 13:06:08,689 - root - 296 - WARNING - None- ***** STARTING SERVER *****
2020-09-14 13:06:08,689 - root - 297 - WARNING - None- Server /usr/lib/python3/dist-packages/synapse/app/homeserver.py version 1.19.1
2020-09-14 13:06:08,689 - root - 298 - INFO - None- Server hostname: testing.example
2020-09-14 13:06:08,690 - root - 299 - INFO - None- Instance name: master
2020-09-14 13:06:08,690 - synapse.app.homeserver - 375 - INFO - None- Setting up server
2020-09-14 13:06:08,690 - synapse.server - 248 - INFO - None- Setting up.
2020-09-14 13:06:08,691 - synapse.storage.databases - 50 - INFO - None- Preparing database 'master'...
2020-09-14 13:06:16,507 - root - 296 - WARNING - None- ***** STARTING SERVER *****
2020-09-14 13:06:16,507 - root - 297 - WARNING - None- Server /usr/lib/python3/dist-packages/synapse/app/homeserver.py version 1.19.1
2020-09-14 13:06:16,507 - root - 298 - INFO - None- Server hostname: testing.example
2020-09-14 13:06:16,507 - root - 299 - INFO - None- Instance name: master
2020-09-14 13:06:16,508 - synapse.app.homeserver - 375 - INFO - None- Setting up server
2020-09-14 13:06:16,508 - synapse.server - 248 - INFO - None- Setting up.
2020-09-14 13:06:16,510 - synapse.storage.databases - 50 - INFO - None- Preparing database 'master'...
[repeats continuously]

Analysis & Possible Fixes

In the error situation, if I run echo "select * from schema_version;" | sqlite3 /var/lib/matrix-synapse/homeserver.db then it shows empty information. This means that table schema_version exists but is empty. Sometimes the table does not exist and running the command throws an error. Due to missing schema version information, it is assumed that the database is not yet setup. The schema setup code does not work if the table it is trying to create already exists and fails.

1. It appears that for at least the sqlite3 database backend, the schema operations are not being run as part of a transaction and interrupting the process ends up with some operations run and others not. Running all operations of single setup/upgrade step will keep the database consistent. I don't know if sqlite3 supports transactions for schema changes.
2. At least for the initial setup, it is possible to perform the operations on an temporary database before moving it to the actual path. This will result in an atomic database setup operation that can be interrupted without any bad consequences.
3. When stopping the daemon, systemd first sends a TERM signal. This TERM signal can be handled and Synapse can exit after finishing important operations such as schema upgrade instead of quitting immediately. This approach does not protect form DB corruptions caused by power outages or explicit kills with KILL signal/

Version information

• Homeserver: Yes

• Version: 1.19.1-1

• Install method: apt install matrix-synapse (from Debian unstable)

• Platform: systemd nspawn container with FreedomBox/Debian Unstable.

[erikjohnston]

Thanks for the detailed report! I believe this is #6467, which should be fixed by #8265. That fix is part of the v1.20.0 RCs, so hopefully this should be fixed in v1.20.0.

I'm closing this on that assumption, but feel free to re-open if that's not the case