rev="post-846" No Comments
I’ve been running my own private matrix server for quite a while now, and so far it has been running smoothly, despite the warning in the documentation that you should not run it with the sqlite3 database in production.
With the release of Debian Bookworm, i decided it was time to upgrade the server.
Naturally, during such big upgrade, something has to go south: matrix-synapse is not starting anymore !
In the logs, i had this gem:
2023-06-22 12:54:08,333 - synapse.app._base - 215 - ERROR - main - Exception during startup
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/app/homeserver.py", line 352, in setup
hs.setup()
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/server.py", line 339, in setup
self.datastores = Databases(self.DATASTORE_CLASS, self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/databases/__init__.py", line 74, in __init__
prepare_database(
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 141, in prepare_database
_upgrade_existing_database(
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
module.run_upgrade(cur, database_engine, config=config)
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
cur.execute(copy_sql, (f"{hostname}",))
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.11/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id
Naturally, googling around yielded no result. So i tried to convert my sqlite3 setup to a postgresql one using the synapse_port_db tool:
# synapse_port_db --sqlite-database homeserver.db --postgres-config homeserver.yaml
2023-06-22 12:39:58,104 - synapse.config.key - 153 - WARNING - This server is configured to use 'matrix.org' as its trusted key server via the
'trusted_key_servers' config option. 'matrix.org' is a good choice for a key
server since it is long-lived, stable and trusted. However, some admins may
wish to use another server for this purpose.
To suppress this warning and continue using 'matrix.org', admins should set
'suppress_key_server_warning' to 'true' in homeserver.yaml.
--------------------------------------------------------------------------------
Preparing sqlite3...
2023-06-22 12:39:58,220 - synapse.storage.prepare_database - 120 - INFO - ('main', 'state'): Checking existing schema version
2023-06-22 12:39:58,236 - synapse.storage.prepare_database - 128 - INFO - ('main', 'state'): Existing schema is 77 (+4 deltas)
2023-06-22 12:39:58,236 - synapse.storage.databases.main - 288 - INFO - Checking database for consistency with configuration...
2023-06-22 12:39:58,237 - synapse.storage.prepare_database - 418 - INFO - Applying schema deltas for v77
2023-06-22 12:39:58,239 - synapse.storage.prepare_database - 526 - INFO - Applying schema 77/14bg_indices_event_stream_ordering.sql
2023-06-22 12:39:58,319 - synapse.storage.prepare_database - 418 - INFO - Applying schema deltas for v78
2023-06-22 12:39:58,320 - synapse.storage.prepare_database - 513 - INFO - Running 78/01_validate_and_update_profiles.py:run_upgrade
2023-06-22 12:39:58,342 - synapse.storage.prepare_database - 513 - INFO - Running 78/02_validate_and_update_user_filters.py:run_upgrade
2023-06-22 12:39:58,344 - synapse_port_db - 849 - ERROR -
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 682, in run
allow_outdated_version=True,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 626, in build_db_store
prepare_database(db_conn, engine, config=self.hs_config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 146, in prepare_database
databases=databases,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
module.run_upgrade(cur, database_engine, config=config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
cur.execute(copy_sql, (f"{hostname}",))
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 682, in run
allow_outdated_version=True,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 626, in build_db_store
prepare_database(db_conn, engine, config=self.hs_config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 146, in prepare_database
databases=databases,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
module.run_upgrade(cur, database_engine, config=config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
cur.execute(copy_sql, (f"{hostname}",))
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id
UNIQUE constraint failed: temp_user_filters.user_id
Ok, so the latest version did not work. I fired up a distinct VM on Buster and installed the corresponding matrix-synapse-py3 package, upload the homeserver.db and homeserver.yaml files to it and ran the same command.
No dice. Database seems corrupted somehow.
I ended up moving the homeserver.db file out of the way, and a new one has been created, matrix-synapse is now starting up !
That’s nice and workable, but how about my history ? Is it forever lost ?
I ended up attempting to re-use the synapse_port_db tool on this new database, and import was successful ! There must be something inconsistent in that « corrupted » DB, because it is fully workable by the sqlite3 commandline tool.
I re-ran the synapse_port_db tool again in verbose mode, and this part of the log gave a hint of where to look:
2023-06-22 14:23:23,195 - synapse.storage.SQL - 449 - DEBUG - [SQL] {prepare_database} INSERT INTO temp_user_filters ( user_id, filter_id, filter_json, full_user_id) SELECT user_id, filter_id, filter_json, '@' || user_id || ':' || ? FROM user_filters
2023-06-22 14:23:23,195 - synapse.storage.SQL - 454 - DEBUG - [SQL values] {prepare_database} ('askarel.be',)
2023-06-22 14:23:23,195 - synapse.storage.SQL - 471 - DEBUG - [SQL FAIL] {prepare_database} UNIQUE constraint failed: temp_user_filters.user_id
2023-06-22 14:23:23,196 - synapse.storage.SQL - 475 - DEBUG - [SQL time] {prepare_database} 0.000266 sec
2023-06-22 14:23:23,196 - synapse_port_db - 849 - ERROR -
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 682, in run
allow_outdated_version=True,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 626, in build_db_store
prepare_database(db_conn, engine, config=self.hs_config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 146, in prepare_database
databases=databases,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
module.run_upgrade(cur, database_engine, config=config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
cur.execute(copy_sql, (f"{hostname}",))
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id
Traceback (most recent call last):
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 682, in run
allow_outdated_version=True,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/_scripts/synapse_port_db.py", line 626, in build_db_store
prepare_database(db_conn, engine, config=self.hs_config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 146, in prepare_database
databases=databases,
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/prepare_database.py", line 514, in _upgrade_existing_database
module.run_upgrade(cur, database_engine, config=config)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/schema/main/delta/78/02_validate_and_update_user_filters.py", line 85, in run_upgrade
cur.execute(copy_sql, (f"{hostname}",))
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 417, in execute
self._do_execute(self.txn.execute, sql, parameters)
File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/database.py", line 469, in _do_execute
return func(sql, *args, **kwargs)
sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id
UNIQUE constraint failed: temp_user_filters.user_id
So i need to look into the user_filters table:
# sqlite3 homeserver.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> select * from user_filters;
frederic|0|{"room":{"state":{"lazy_load_members":true},"timeline":{"limit":20}}}|
anita|0|{"room":{"state":{"lazy_load_members":true},"timeline":{"limit":20}}}|
frederic|1|{"room":{"state":{"lazy_load_members":true},"timeline":{}}}|
frederic|2|{"room":{"state":{"lazy_load_members":true}}}|
frederic|3|{"room":{"state":{"lazy_load_members":true},"timeline":{"unread_thread_notifications":true}}}|
There are 3 extra filters in that table. Could it be that those filters are making the homeserver freak out ? I have a backup of the file, so let’s go and delete those entries:
sqlite> delete from user_filters where filter_id=3;
sqlite> delete from user_filters where filter_id=2;
sqlite> delete from user_filters where filter_id=1;
sqlite> select * from user_filters;
frederic|0|{"room":{"state":{"lazy_load_members":true},"timeline":{"limit":20}}}|
anita|0|{"room":{"state":{"lazy_load_members":true},"timeline":{"limit":20}}}|
sqlite>
Everything looks fine, let’s try importing this into our postgres database:
# synapse_port_db --sqlite-database homeserver.db --postgres-config homeserver.yaml -v
2023-06-22 14:56:51,757 - synapse.config.key - 153 - WARNING - This server is configured to use 'matrix.org' as its trusted key server via the
'trusted_key_servers' config option. 'matrix.org' is a good choice for a key
server since it is long-lived, stable and trusted. However, some admins may
wish to use another server for this purpose.
To suppress this warning and continue using 'matrix.org', admins should set
'suppress_key_server_warning' to 'true' in homeserver.yaml.
--------------------------------------------------------------------------------
Preparing sqlite3...
2023-06-22 14:56:51,811 - synapse.storage.SQL - 449 - DEBUG - [SQL] {prepare_database} BEGIN TRANSACTION
2023-06-22 14:56:51,811 - synapse.storage.SQL - 454 - DEBUG - [SQL values] {prepare_database} ()
2023-06-22 14:56:51,811 - synapse.storage.SQL - 475 - DEBUG - [SQL time] {prepare_database} 0.000029 sec
2023-06-22 14:56:51,811 - synapse.storage.prepare_database - 120 - INFO - ('main', 'state'): Checking existing schema version
2023-06-22 14:56:51,812 - synapse.storage.SQL - 449 - DEBUG - [SQL] {prepare_database} BEGIN TRANSACTION; /* Copyright 2015, 2016 OpenMarket Ltd * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ CREATE TABLE IF NOT EXISTS schema_version( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. version INTEGER NOT NULL, upgraded BOOL NOT NULL, -- Whether we reached this version from an upgrade or an initial schema. CHECK (Lock='X') ); CREATE TABLE IF NOT EXISTS schema_compat_version( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. -- The SCHEMA_VERSION of the oldest synapse this database can be used with compat_version INTEGER NOT NULL, CHECK (Lock='X') ); CREATE TABLE IF NOT EXISTS applied_schema_deltas( version INTEGER NOT NULL, file TEXT NOT NULL, UNIQUE(version, file) ); -- a list of schema files we have loaded on behalf of dynamic modules CREATE TABLE IF NOT EXISTS applied_module_schemas( module_name TEXT NOT NULL, file TEXT NOT NULL, UNIQUE(module_name, file) );
...
2023-06-22 14:57:13,179 - synapse.storage.txn - 738 - DEBUG - [TXN START] {has_completed_background_updates-2}
2023-06-22 14:57:13,179 - synapse.storage.SQL - 449 - DEBUG - [SQL] {has_completed_background_updates-2} SELECT 1 FROM background_updates
2023-06-22 14:57:13,179 - synapse.storage.SQL - 454 - DEBUG - [SQL values] {has_completed_background_updates-2} ()
2023-06-22 14:57:13,180 - synapse.storage.SQL - 475 - DEBUG - [SQL time] {has_completed_background_updates-2} 0.000084 sec
2023-06-22 14:57:13,180 - synapse.storage.txn - 842 - DEBUG - [TXN END] {has_completed_background_updates-2} 0.001567 sec
Pending background updates exist in the SQLite3 database. Please start Synapse again and wait until every update has finished before running this script.
Looks like importing works. Let’s stop the homeserver and insert our doctored database in place:
// Insert text here
Holy shit ! It works ! The previously corrupted homeserver database is now working again !