SQLite Migration not working

[throny]

Describe the bug When having a fresh install with the playbook (after running setup-all) trying to migrate a old SQLite database fails with the following message:

TASK [matrix-postgres : Import SQLite database into Postgres] ************************************************************************************************************************************
fatal: [matrix.leftist.eu]: FAILED! => changed=true
  cmd:
  - docker
  - run
  - --rm
  - --name=matrix-synapse-migrate
  - --log-driver=none
  - --user=999:1004
  - --cap-drop=ALL
  - --network=https-proxy
  - --entrypoint=python
  - --mount
  - type=bind,src=/matrix/synapse/config,dst=/data
  - --mount
  - type=bind,src=/matrix/synapse/config,dst=/matrix-media-store-parent/media-store
  - --mount
  - type=bind,src=/tmp/homeserver.db,dst=/homeserver.db
  - docker.io/matrixdotorg/synapse:v1.62.0
  - /usr/local/bin/synapse_port_db
  - --sqlite-database
  - /homeserver.db
  - --postgres-config
  - /data/homeserver.yaml
  delta: '0:00:07.795488'
  end: '2022-07-06 13:13:15.323879'
  msg: non-zero return code
  rc: 5
  start: '2022-07-06 13:13:07.528391'
  stderr: |-
    2022-07-06 11:13:13,507 - 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.
    --------------------------------------------------------------------------------
    2022-07-06 11:13:13,591 - synapse.storage.prepare_database - 115 - INFO - ('main', 'state'): Checking existing schema version
    2022-07-06 11:13:13,595 - synapse.storage.prepare_database - 119 - INFO - ('main', 'state'): Existing schema is 72 (+2 deltas)
    2022-07-06 11:13:13,595 - synapse.storage.databases.main - 329 - INFO - Checking database for consistency with configuration...
    2022-07-06 11:13:13,595 - synapse.storage.prepare_database - 411 - INFO - Applying schema deltas for v72
    2022-07-06 11:13:13,596 - synapse_port_db - 786 - ERROR -
    Traceback (most recent call last):
      File "/usr/local/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 632, in run
        self.sqlite_store = self.build_db_store(
      File "/usr/local/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 597, in build_db_store
        prepare_database(db_conn, engine, config=self.hs_config)
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 136, in prepare_database
        _upgrade_existing_database(
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 413, in _upgrade_existing_database
        cur.execute("DELETE FROM schema_version")
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 353, in execute
        self._do_execute(self.txn.execute, sql, *args)
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 395, in _do_execute
        return func(sql, *args, **kwargs)
    sqlite3.OperationalError: attempt to write a readonly database
    Traceback (most recent call last):
      File "/usr/local/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 632, in run
        self.sqlite_store = self.build_db_store(
      File "/usr/local/lib/python3.9/site-packages/synapse/_scripts/synapse_port_db.py", line 597, in build_db_store
        prepare_database(db_conn, engine, config=self.hs_config)
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 136, in prepare_database
        _upgrade_existing_database(
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/prepare_database.py", line 413, in _upgrade_existing_database
        cur.execute("DELETE FROM schema_version")
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 353, in execute
        self._do_execute(self.txn.execute, sql, *args)
      File "/usr/local/lib/python3.9/site-packages/synapse/storage/database.py", line 395, in _do_execute
        return func(sql, *args, **kwargs)
    sqlite3.OperationalError: attempt to write a readonly database
    attempt to write a readonly database
  stderr_lines: <omitted>
  stdout: Preparing sqlite3...
  stdout_lines: <omitted>

I thought it might be a filesystem issue for the .db file itself and set it to 777 temporarily and played around with ownership (matrix:matrix, root:root). I guess thats not the problem here and the postgres database is meant.

To Reproduce My vars.yml file looks like this (I am trying to run it behind the nginx-proxy by jwilder):

---
# The bare domain name which represents your Matrix identity.
# Matrix user ids for your server will be of the form (`@user:<matrix-domain>`).
#
# Note: this playbook does not touch the server referenced here.
# Installation happens on another server ("matrix.<matrix-domain>").
#
# If you've deployed using the wrong domain, you'll have to run the Uninstalling step,
# because you can't change the Domain after deployment.
#
# Example value: example.com
matrix_domain: 'XXX'

# This is where Matrix services
matrix_server_fqn_matrix: 'XXX'

# This is where you access the Element web UI from (if enabled via `matrix_client_element_enabled: true`; enabled by default).
# This and the Matrix FQN (see above) are expected to be on the same server.
#
# Feel free to use `element.matrix.YOUR_BASE_DOMAIN`, if you'd prefer that.
matrix_server_fqn_element: 'XXX'

# The Matrix homeserver software to install.
# See `roles/matrix-base/defaults/main.yml` for valid options.
matrix_homeserver_implementation: synapse

# docker already installed
matrix_docker_installation_enabled: true

# A secret used as a base, for generating various other secrets.
# You can put any string here, but generating a strong one is preferred (e.g. `pwgen -s 64 1`).
matrix_homeserver_generic_secret_key: 'XXX'

# Disable generation and retrieval of SSL certs
matrix_ssl_retrieval_method: none

# Configure Nginx to only use plain HTTP
matrix_nginx_proxy_https_enabled: false

# Don't bind any HTTP or federation port to the host
matrix_nginx_proxy_container_http_host_bind_port: ''
matrix_nginx_proxy_container_federation_host_bind_port: ''

# Trust the reverse proxy to send the correct `X-Forwarded-Proto` header as it is handling the SSL connection.
matrix_nginx_proxy_trust_forwarded_proto: true

# Trust and use the other reverse proxy's `X-Forwarded-For` header.
matrix_nginx_proxy_x_forwarded_for: '$proxy_add_x_forwarded_for'

# Disable Coturn because it needs SSL certs
# (Clients can, though exposing IP address, use Matrix.org TURN)
matrix_coturn_enabled: false

# All containers need to be on the same Docker network as nginx-proxy
matrix_docker_network: 'https-proxy'

matrix_nginx_proxy_container_extra_arguments:
  - '--environment "VIRTUAL_HOST=`{{ matrix_server_fqn_matrix }}`,`{{ matrix_server_fqn_element }}`,`{{ matrix_server_fqn_dimension }}`,`{{ matrix_server_fqn_jitsi }}`"'
  - '--environment "VIRTUAL_PORT=8080"'
  - '--environment "LETSENCRYPT_HOST=`{{ matrix_server_fqn_matrix }}`,`{{ matrix_server_fqn_element }}`,`{{ matrix_server_fqn_dimension }}`,`{{ matrix_server_fqn_jitsi }}`"'

# change federation to 443
matrix_synapse_http_listener_resource_names: ["client","federation"]
matrix_federation_public_port: 443
matrix_synapse_federation_port_enabled: false

# A Postgres password to use for the superuser Postgres user (called `matrix` by default).
#
# The playbook creates additional Postgres users and databases (one for each enabled service)
# using this superuser account.
matrix_postgres_connection_password: 'XXX'

# mail settings
matrix_mailer_sender_address: "XXX"
matrix_mailer_relay_use: true
matrix_mailer_relay_host_name: "XXX"
matrix_mailer_relay_host_port: 587
matrix_mailer_relay_auth: true
matrix_mailer_relay_auth_username: "XXX"
matrix_mailer_relay_auth_password: "XXX"

Running ansible-playbook -i inventory/hosts setup.yml --extra-vars='server_path_homeserver_db=/tmp/homeserver.db' --tags=import-synapse-sqlite-db

Expected behavior Successfully import the SQLite to Postgres.

Matrix Server:

• OS: Ubuntu 20.04 LTS
• Architecture amd64

Additional context Update: I forgot to set a secret for Postgres the first time I ran setup-all so I thought I just might uninstall and reinstall again, just to be sure there was nothing broken during the first installation of Postgres (as I was pointed to the manual section). However the /usr/local/bin/matrix-remove-all removed all dangling networks and images not running, not just only the ones affiliated with Matrix, which is unfortunate.

After all, same error as above still applies with a completely fresh installation.

Edit: It seems this might be a general problem: https://github.com/spantaleev/matrix-docker-ansible-deploy/issues/775

[throny]

As my PR fixed the issue with the read/write-issue of the bind-mount, I encountered the next one.

It seems that after a fresh install the user and database synapse is present.

When running import-synapse-sqlite-db it gets removed, probably as part of the "Ensure postgres data is wiped out" step? I personally am not that familiar with Ansible playbooks so I might require some help here. I guess this is a remaining bug after switching to multi-user?

Edit: disabled the wipe-part and it seems it worked.

[spantaleev]

Your problem most likely is that your SQLite database is from an older version of Synapse (compared to the one that the playbook uses; v1.62.0 as of right now). So before the Synapse importer script tries to import it, it wants to do some migrations on it.

You can work around this by ensuring that you either provide an up to date version (by upgrading your other Synapse installation first and taking the SQLite file after that) or by "downgrading" the Synapse version that the playbook uses (we have a variable in roles/matrix-synapse/defaults/main.yml that you can override via your vars.yml file).

[spantaleev]

Your PR removing the necessity for this workaround may be good though

The synapse user is present, because the matrix-postgres role prepares a database and users for each component in the playbook.

During (Postgres) dump importing, we try to exclude user creation statements to prevent conflicts. What is the trouble with the user being present? Does synapse refuse to import your SQLite database?

[throny]

During (Postgres) dump importing, we try to exclude user creation statements to prevent conflicts. What is the trouble with the user being present? Does synapse refuse to import your SQLite database?

It refused the password for the user synapse from the synapse homeserver.yaml so I had a look at the postgres DB and the synapse user wasn't present after running the migration task. That's why assumed that it might got removed when running the task as it wipes the postgres data folder. I disabled the wipe check and it worked for me :)

[spantaleev]

So it managed to import it alright, but then the password for the synapse user ended up incorrect?

Perhaps a new --tags=setup-all after the import may help.

[throny]

Sorry for being that unprecise. Steps to reproduce:

1. running setup-all without starting
2. running import-synapse-sqlite-db with the applied fix of the PR Outcome: FATAL: password authentication failed for user "synapse" so the DB file was not imported Having a look at the database users:

Then I wiped everything and ran the setup-all again and commented out the task Ensure postgres data is wiped out in roles/matrix-postgres/tasks/import_synapse_sqlite_db.yml This made the import successful. I ran setup-all after that and I guess it did some migrations. I was able to start successfully after doing so.