Homeserver errors out an startup while applying the database schema 57

[Terbium-135]

Description

Not sure why this happened. No need to put much effort into this. I might as well remove the database and start fresh

2020-01-17 11:36:31,932 - twisted - 192 - INFO - None - Redirected stdout/stderr to logs
2020-01-17 11:36:31,933 - root - 257 - WARNING - None - ***** STARTING SERVER *****
2020-01-17 11:36:31,933 - root - 258 - WARNING - None - Server /opt/synapse/synapse/synapse/app/homeserver.py version 1.8.0 (b=develop,59dc87c61)
2020-01-17 11:36:31,934 - root - 259 - INFO - None - Server hostname: mensa-dashboard.ddns.net
2020-01-17 11:36:31,935 - synapse.app.homeserver - 340 - INFO - None - Setting up server
2020-01-17 11:36:31,935 - synapse.server - 240 - INFO - None - Setting up.
2020-01-17 11:36:31,990 - synapse.storage.data_stores - 48 - INFO - None - Preparing database 'master'...
2020-01-17 11:36:31,997 - synapse.storage.prepare_database - 304 - INFO - None - Upgrading schema to v56
2020-01-17 11:36:32,000 - synapse.storage.prepare_database - 304 - INFO - None - Upgrading schema to v57
2020-01-17 11:36:32,003 - synapse.storage.prepare_database - 373 - INFO - None - Running script 57/local_current_membership.py
2020-01-17 11:36:32,022 - twisted - 192 - ERROR -  - Traceback (most recent call last):
2020-01-17 11:36:32,023 - twisted - 192 - ERROR -  -   File "/usr/lib/python3.7/runpy.py", line 193, in _run_module_as_main
2020-01-17 11:36:32,025 - twisted - 192 - ERROR -  -     "__main__", mod_spec)
2020-01-17 11:36:32,026 - twisted - 192 - ERROR -  -   File "/usr/lib/python3.7/runpy.py", line 85, in _run_code
2020-01-17 11:36:32,027 - twisted - 192 - ERROR -  -     exec(code, run_globals)
2020-01-17 11:36:32,028 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/app/homeserver.py", line 639, in <module>
2020-01-17 11:36:32,029 - twisted - 192 - ERROR -  -     main()
2020-01-17 11:36:32,030 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/app/homeserver.py", line 634, in main
2020-01-17 11:36:32,031 - twisted - 192 - ERROR -  -     hs = setup(sys.argv[1:])
2020-01-17 11:36:32,032 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/app/homeserver.py", line 343, in setup
2020-01-17 11:36:32,033 - twisted - 192 - ERROR -  -     hs.setup()
2020-01-17 11:36:32,034 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/server.py", line 242, in setup
2020-01-17 11:36:32,035 - twisted - 192 - ERROR -  -     self.datastores = DataStores(self.DATASTORE_CLASS, self)
2020-01-17 11:36:32,036 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/storage/data_stores/__init__.py", line 52, in __init__
2020-01-17 11:36:32,038 - twisted - 192 - ERROR -  -     db_conn, engine, hs.config, data_stores=database_config.data_stores,
2020-01-17 11:36:32,039 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/storage/prepare_database.py", line 85, in prepare_database
2020-01-17 11:36:32,040 - twisted - 192 - ERROR -  -     data_stores=data_stores,
2020-01-17 11:36:32,040 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/storage/prepare_database.py", line 376, in _upgrade_existing_database
2020-01-17 11:36:32,041 - twisted - 192 - ERROR -  -     module.run_upgrade(cur, database_engine, config=config)
2020-01-17 11:36:32,042 - twisted - 192 - ERROR -  -   File "/opt/synapse/synapse/synapse/storage/data_stores/main/schema/delta/57/local_current_membership.py", line 71, in run_upgrade
2020-01-17 11:36:32,043 - twisted - 192 - ERROR -  -     cur.execute(sql, (config.server_name,))
2020-01-17 11:36:32,044 - twisted - 192 - ERROR -  - ValueError: unsupported format character ''' (0x27) at index 312

Steps to reproduce

• Clone developer branch
• Restarting homeserver with synctl restart

Expected the database to get updated to schema 57

Version information

• Version:
  {
  "python_version": 3.7.3,
  "server_version": 1.8.0 (b=develop,59dc87c61)
  }

• Install method:
  git clone

• Platform:
  Debian 10, ARM64

[u1-liquid]

synapse/synapse/storage/data_stores/main/schema/delta/57/local_current_membership.py

Lines 54 to 71 in 1dee1e9

	if current_state_membership_up_to_date:
	sql = """
	INSERT INTO local_current_membership (room_id, user_id, event_id, membership)
	SELECT c.room_id, state_key AS user_id, event_id, c.membership
	FROM current_state_events AS c
	WHERE type = 'm.room.member' AND c.membership IS NOT NULL AND state_key like '%' || ?
	"""
	else:
	# We can't rely on the membership column, so we need to join against
	# `room_memberships`.
	sql = """
	INSERT INTO local_current_membership (room_id, user_id, event_id, membership)
	SELECT c.room_id, state_key AS user_id, event_id, r.membership
	FROM current_state_events AS c
	INNER JOIN room_memberships AS r USING (event_id)
	WHERE type = 'm.room.member' and state_key like '%' || ?
	"""
	cur.execute(sql, (config.server_name,))

It seems state_key like '%' || ? causing this problem (syntax error), proposed PR what I changed.

[Terbium-135]

Issue solved by 722b4f3
Closing
Much appreciated!

[erikjohnston]

(This was introduced in #6655)