there is no unique constraint matching given keys for referenced table "access_tokens"

[Kokokokoka]

Before creating
CREATE TABLE IF NOT EXISTS event_txn_id

create unique index if not exists access_tokens_id on access_tokens (id);
shall be made in order to progress properly.

Description

021-08-25 08:13:51,996 - synapse.storage.prepare_database - 471 - DEBUG - main - Found file: 58/19instance_map.sql.postgres (/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/schema/main/delta/58/19instance_map.sql.postgres)
2021-08-25 08:13:51,996 - synapse.storage.prepare_database - 516 - INFO - main - Applying engine-specific schema 58/19instance_map.sql.postgres
2021-08-25 08:13:51,996 - synapse.storage.SQL - 311 - DEBUG - main - [SQL] {prepare_database} CREATE TABLE IF NOT EXISTS instance_map ( instance_id SERIAL PRIMARY KEY, instance_name TEXT NOT
NULL )
2021-08-25 08:13:52,000 - synapse.storage.SQL - 337 - DEBUG - main - [SQL time] {prepare_database} 0.003949 sec
2021-08-25 08:13:52,001 - synapse.storage.SQL - 311 - DEBUG - main - [SQL] {prepare_database} CREATE UNIQUE INDEX IF NOT EXISTS instance_map_idx ON instance_map(instance_name)
2021-08-25 08:13:52,002 - synapse.storage.SQL - 337 - DEBUG - main - [SQL time] {prepare_database} 0.001419 sec
2021-08-25 08:13:52,002 - synapse.storage.SQL - 311 - DEBUG - main - [SQL] {prepare_database} INSERT INTO applied_schema_deltas (version, file) VALUES (?,?)
2021-08-25 08:13:52,002 - synapse.storage.SQL - 316 - DEBUG - main - [SQL values] {prepare_database} (58, '58/19instance_map.sql.postgres')
2021-08-25 08:13:52,003 - synapse.storage.SQL - 337 - DEBUG - main - [SQL time] {prepare_database} 0.000190 sec
2021-08-25 08:13:52,003 - synapse.storage.prepare_database - 471 - DEBUG - main - Found file: 58/19txn_id.sql (/opt/venvs/matrix-synapse/lib/python3.9/site-packages/synapse/storage/schema/main/delta/58/19txn_id.sql)
2021-08-25 08:13:52,003 - synapse.storage.prepare_database - 508 - INFO - main - Applying schema 58/19txn_id.sql
2021-08-25 08:13:52,003 - synapse.storage.SQL - 311 - DEBUG - main - [SQL] {prepare_database} CREATE TABLE IF NOT EXISTS event_txn_id ( event_id TEXT NOT NULL, room_id TEXT NOT NULL, user_id
TEXT NOT NULL, token_id BIGINT NOT NULL, txn_id TEXT NOT NULL, inserted_ts BIGINT NOT NULL, FOREIGN KEY (event_id) REFERENCES events (event_id) ON DELETE CASCADE, FOREIGN KEY (token_id) REFERENCES access_tokens (id) ON DELETE CASCADE )
2021-08-25 08:13:52,012 - synapse.storage.SQL - 333 - DEBUG - main - [SQL FAIL] {prepare_database} there is no unique constraint matching given keys for referenced table "access_tokens"

Aug 25 07:57:17 Matrix matrix-synapse[190116]: Error during initialisation:
Aug 25 07:57:17 Matrix matrix-synapse[190116]: there is no unique constraint matching given keys for referenced table "access_tokens"

Steps to reproduce

1. copy db from synapse 1.0 to 1.4
2. start server

Version information

Synapse: 1.40,
on Debian 11,
using matrix repo.
This is a migration from 1.0 version

[reivilibre]

This is odd; it doesn't seem like it should be necessary to do that: the CREATE TABLE statement for access_tokens contains a line that makes id a primary key (which implies a unique index).

Is there possibly anything else to the database you've done that could have caused this issue?

[Kokokokoka]

I don't really know the full history, but it seems that there was a migration to postges some versions ago (pre 1.0). v 1.0 was working and had no unique index, and migration to 1.41 has shown this bug.
So, I think that a safetycheck wouldn't hurt.

[reivilibre]

Please can you log in to Postgres using psql and issue the command \d access_tokens, then report back with what it says?

[Kokokokoka]

                Table "public.access_tokens"
      Column      |  Type   | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
 id               | bigint  |           | not null |
 user_id          | text    |           | not null |
 device_id        | text    |           |          |
 token            | text    |           | not null |
 valid_until_ms   | bigint  |           |          |
 puppets_user_id  | text    |           |          |
 last_validated   | bigint  |           |          |
 refresh_token_id | bigint  |           |          |
 used             | boolean |           |          |
Indexes:
    "access_tokens_device_id" btree (user_id, device_id)
    "access_tokens_id" UNIQUE, btree (id)
    "access_tokens_token_key" UNIQUE CONSTRAINT, btree (token)
Foreign-key constraints:
    "access_tokens_refresh_token_id_fkey" FOREIGN KEY (refresh_token_id) REFERENCES refresh_tokens(id) ON DELETE CASCADE
Referenced by:
    TABLE "event_txn_id" CONSTRAINT "event_txn_id_token_id_fkey" FOREIGN KEY (token_id) REFERENCES access_tokens(id) ON DELETE CASCADE

v1.41 (with create unique index if not exists access_tokens_id on access_tokens (id);)

               Table "public.access_tokens"
     Column     |  Type  | Collation | Nullable | Default
----------------+--------+-----------+----------+---------
 id             | bigint |           | not null |
 user_id        | text   |           | not null |
 device_id      | text   |           |          |
 token          | text   |           | not null |
 last_used      | bigint |           |          |
 valid_until_ms | bigint |           |          |
Indexes:
    "access_tokens_token_key" UNIQUE CONSTRAINT, btree (token)
    "access_tokens_device_id" btree (user_id, device_id)

v1.0

[callahad]

Since this fails on startup, and we've not seen other reports of it, we suspect you've somehow found yourself in a very uncommon situation.

@richvdh has promised to take a brief look at this, but unless that turns up an obvious problem, we'll probably chalk this up to unexplained weirdness.

[richvdh]

the problem here is that the v1.0.0 schema is missing an index. It should be:

synapse_1_0=# \d access_tokens
            Table "public.access_tokens"
  Column   |  Type  | Collation | Nullable | Default 
-----------+--------+-----------+----------+---------
 id        | bigint |           | not null | 
 user_id   | text   |           | not null | 
 device_id | text   |           |          | 
 token     | text   |           | not null | 
 last_used | bigint |           |          | 
Indexes:
    "access_tokens_pkey" PRIMARY KEY, btree (id)
    "access_tokens_token_key" UNIQUE CONSTRAINT, btree (token)
    "access_tokens_device_id" btree (user_id, device_id)

It's extremely unclear to me how your database could possibly be missing that index - it should have been present ever since the very early days of synapse (

synapse/synapse/storage/schema/full_schemas/16/users.sql

Line 24 in 58d8339

id BIGINT PRIMARY KEY,

), so even bugs in synapse_port_db wouldn't explain it.

I can only conclude that at some point in the distant past, there has been some misguided database administration which has caused some indexes to go missing.

I recommend comparing your database schema with that of a newly-installed synapse, but beyond that, I don't think there's much we can do to help here.