Postgres port script fails to set correct initial values for some sequences

[krithin]

TL;DR: Uninitialized sequences

• receipts_sequence (receipts_linearized)
• account_data_sequence (room_account_data)
• device_inbox_sequence (device_inbox)

Steps to reproduce

I have synapse installed through the ubuntu package from the https://packages.matrix.org/debian/ repository, at version matrix-synapse-py3/unknown,now 1.26.0+focal1 amd64 [installed]

I ran synapse_port_db to migrate my synapse install from sqlite to postgres.

After the script was complete I updated the database section of my homeserver.yaml to point to the new postgres install, and started up synapse.

The problem was that synapse then failed to come up (and, as an aside, journalctl --since=today -u matrix-synapse was uninformative as to why it was failing). Looking at the homeserver log I saw an error message beginning with

2021-02-07 16:54:16,547 - twisted - 254 - ERROR - None -  Postgres sequence 'receipts_sequence' is inconsistent with associated
2021-02-07 16:54:16,547 - twisted - 254 - ERROR - None -  table 'receipts_linearized'.

It then went on to provide an SQL statement to run to fix that problem.

I checked (select * from receipts_sequence) before running that setval statement and the current value of the sequence was in fact 1, indicating it had never been called.

After addressing that issue I got error messages about two more sequences (account_data_sequence and device_inbox_sequence) before I was able to start up my synapse.

The printed error message said "This can happen if Synapse has been downgraded and then upgraded again, or due to a bad migration.", but I'm quite sure I've never downgraded synapse or manually run a migration against the sqlite db.

[clokep]

I suspect that we did not update the port DB script when we recently added a few sequences. See the code around

synapse/scripts/synapse_port_db

Lines 622 to 632 in 7036e24

	# Step 2. Set up sequences
	#
	# We do this before porting the tables so that event if we fail half
	# way through the postgres DB always have sequences that are greater
	# than their respective tables. If we don't then creating the
	# `DataStore` object will fail due to the inconsistency.
	self.progress.set_state("Setting up sequence generators")
	await self._setup_state_group_id_seq()
	await self._setup_user_id_seq()
	await self._setup_events_stream_seqs()
	await self._setup_device_inbox_seq()

[jm-github]

I faced the same inconsistencies with matrix-synapse 1.26.0-1~bpo10+3 on debian buster.

Synapse gave me the 3 following sql commands in the log - one at a time.

SELECT setval('receipts_sequence', (SELECT GREATEST(MAX(stream_id), 0) FROM receipts_linearized));
SELECT setval('account_data_sequence', (SELECT GREATEST(MAX(stream_id), 0) FROM room_account_data));
SELECT setval('device_inbox_sequence', (SELECT GREATEST(MAX(stream_id), 0) FROM device_inbox)); 

Then synapse started well.
But when I tried to create a room I got this error:

synapse.http.server: [POST-1655] Failed handle request via 'RoomCreateRestServlet': <SynapseRequest at 0x7f11202d6b00 method='POST' uri='/_matrix/client/r0/createRoom' clientproto='HTTP/1.1' site='8008'>
                                      Traceback (most recent call last):
                                        File "/usr/lib/python3/dist-packages/synapse/storage/persist_events.py", line 172, in handle_queue_loop
                                          ret = await per_item_callback(item)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/persist_events.py", line 325, in persisting_queue
                                          item.events_and_contexts, backfilled=item.backfilled
                                        File "/usr/lib/python3/dist-packages/synapse/storage/persist_events.py", line 537, in _persist_events
                                          backfilled=backfilled,
                                        File "/usr/lib/python3/dist-packages/synapse/storage/databases/main/events.py", line 177, in _persist_events_and_state_updates
                                          new_forward_extremeties=new_forward_extremeties,
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 664, in runInteraction
                                          **kwargs,
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 740, in runWithConnection
                                          self._db_pool.runWithConnection(inner_func, *args, **kwargs)
                                        File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 250, in inContext
                                          result = inContext.theWork()
                                        File "/usr/lib/python3/dist-packages/twisted/python/threadpool.py", line 266, in <lambda>
                                          inContext.theWork = lambda: context.call(ctx, func, *args, **kw)
                                        File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 122, in callWithContext
                                          return self.currentContext().callWithContext(ctx, func, *args, **kw)
                                        File "/usr/lib/python3/dist-packages/twisted/python/context.py", line 85, in callWithContext
                                          return func(*args,**kw)
                                        File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 306, in _runWithConnection
                                          compat.reraise(excValue, excTraceback)
                                        File "/usr/lib/python3/dist-packages/twisted/python/compat.py", line 464, in reraise
                                          raise exception.with_traceback(traceback)
                                        File "/usr/lib/python3/dist-packages/twisted/enterprise/adbapi.py", line 297, in _runWithConnection
                                          result = func(conn, *args, **kw)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 734, in inner_func
                                          return func(db_conn, *args, **kwargs)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 534, in new_transaction
                                          r = func(cursor, *args, **kwargs)
                                        File "/usr/lib/python3/dist-packages/synapse/logging/utils.py", line 71, in wrapped
                                          return f(*args, **kwargs)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/databases/main/events.py", line 379, in _persist_events_txn
                                          self._persist_event_auth_chain_txn(txn, [e for e, _ in events_and_contexts])
                                        File "/usr/lib/python3/dist-packages/synapse/storage/databases/main/events.py", line 473, in _persist_event_auth_chain_txn
                                          txn, self.db_pool, event_to_room_id, event_to_types, event_to_auth_chain,
                                        File "/usr/lib/python3/dist-packages/synapse/storage/databases/main/events.py", line 677, in _add_chain_cover_index
                                          for event_id, (c_id, seq) in new_chain_tuples.items()
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 891, in simple_insert_many_txn
                                          txn.executemany(sql, vals)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 291, in executemany
                                          self._do_execute(self.txn.executemany, sql, *args)
                                        File "/usr/lib/python3/dist-packages/synapse/storage/database.py", line 314, in _do_execute
                                          return func(sql, *args)
                                      psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "event_auth_chains_c_seq_index"
                                      DETAIL:  Key (chain_id, sequence_number)=(1, 1) already exists.

I checked all the values of the sequences and I found that user_id_seq , instance_map_instance_id_seq and event_auth_chain_id have their default initial value (1).
I'm investigating in the database structure and synapse source code to find what table.row to use

[krithin]

@jm-github you should see #9382. You need to run select setval('event_auth_chain_id', (select max(chain_id) from event_auth_chains));