to_device messages get lost due to to_device stream id not being saved

[dbkr]

In schema delta 35, synapse added an entry into the device_max_stream_id table (https://github.com/matrix-org/synapse/blob/develop/synapse/storage/data_stores/main/schema/delta/35/device_stream_id.sql). Full schema 54 has now been cut which means this schema delta file isn't being run for new installs as of 1.0 (when full schema 54 was released) so it looks like these synapse installs never get an entry in device_max_stream_id. It's written to by an UPDATE clause so no entry is created if one doesn't already exist.

It doesn't look like it gets created anywhere else. If not, this means the device stream token resets any time synapse is restarted which causes to_device messages to go missing, causing Unable To Decrypt errors.

Same bug may apply to appservice_stream as that appears to do the same thing

[erikjohnston]

We probably want to go and audit all tables that expect to have a row in them.

The easiest fix for this is probably to upsert rather than update. Now that we have native upsert support that should not have too much of a performance penalty.

[anoadragon453]

A fix for this is available here: #6375 It fixes the schema instead of using an UPSERT.

Closing this one and made another issue for investigating related tables: #6381

[anoadragon453]

Err, actually I'll wait until that PR is closed huh.

[anoadragon453]

This should be fixed for this specific instance by #6555

appservice_stream and others is covered by

synapse/synapse/storage/data_stores/main/schema/full_schemas/54/stream_positions.sql

Line 2 in 8568704

INSERT INTO appservice_stream_position (stream_ordering) SELECT COALESCE(MAX(stream_ordering), 0) FROM events;