synapse_port_db fails on `room_depth` table

[drbawb]

Description

On a synapse homeserver which has previously joined #matrix-hq there is an entry in the room_depth table w/ a depth of INT_MAX. When running synapse_port_db this fails to import into a postgresql database since SQLite's integer type is 8 bytes wide if necessary, whereas PostgreSQL's integer type is fixed at 4 bytes wide.

Steps to reproduce

• Create a homeserver with SQLite database
• Join #matrix-hq and sync it
• Create a PostgreSQL database
• Follow the instructions as described in the conversion documentation.

Observe the output of synapse_port_db has failed:

2018-05-12 18:47:04,837 - synapse_port_db - 175 - ERROR - Failed to insert: room_depth
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 171, in insert_many_txn
    txn.executemany(sql, rows)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 85, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/usr/lib/python2.7/dist-packages/synapse/storage/_base.py", line 112, in _do_execute
    sql, *args
DataError: integer out of range

2018-05-12 18:47:04,840 - synapse_port_db - 562 - ERROR -
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 552, in run
    consumeErrors=True,
FirstError: FirstError[#88, [Failure instance: Traceback: <class 'psycopg2.DataError'>: integer out of range

/usr/lib/python2.7/dist-packages/twisted/internet/defer.py:498:errback
/usr/lib/python2.7/dist-packages/twisted/internet/defer.py:565:_startRunCallbacks
/usr/lib/python2.7/dist-packages/twisted/internet/defer.py:651:_runCallbacks
/usr/lib/python2.7/dist-packages/twisted/internet/defer.py:1355:gotResult
--- <exception caught here> ---
/usr/lib/python2.7/dist-packages/twisted/internet/defer.py:1297:_inlineCallbacks
/usr/lib/python2.7/dist-packages/twisted/python/failure.py:389:throwExceptionIntoGenerator
/usr/bin/synapse_port_db:356:handle_table
/usr/lib/python2.7/dist-packages/twisted/python/threadpool.py:246:inContext
/usr/lib/python2.7/dist-packages/twisted/python/threadpool.py:262:<lambda>
/usr/lib/python2.7/dist-packages/twisted/python/context.py:118:callWithContext
/usr/lib/python2.7/dist-packages/twisted/python/context.py:81:callWithContext
/usr/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:307:_runWithConnection
/usr/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:298:_runWithConnection
/usr/bin/synapse_port_db:138:r
/usr/bin/synapse_port_db:343:insert
/usr/bin/synapse_port_db:171:insert_many_txn
/usr/lib/python2.7/dist-packages/synapse/storage/_base.py:85:executemany
/usr/lib/python2.7/dist-packages/synapse/storage/_base.py:112:_do_execute
]]

The error is caused by the following entry in room_depth:

sqlite> select * from room_depth
   ...> where room_id = '!cURbafjkfsMDVwdRDQ:matrix.org';
!cURbafjkfsMDVwdRDQ:matrix.org|9223372036854775807
sqlite> 

Which cannot be imported into the following table w/ an INT_MAX of (2^31) - 1:

synapse=# \d+ room_depth;
                                  Table "public.room_depth"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
-----------+---------+-----------+----------+---------+----------+--------------+-------------
 room_id   | text    |           | not null |         | extended |              | 
 min_depth | integer |           | not null |         | plain    |              | 
Indexes:
    "room_depth_room_id_key" UNIQUE CONSTRAINT, btree (room_id)
    "room_depth_room" btree (room_id)

Version information

• Debian 9
• Synapse v0.28.1
• PostgreSQL 10.3
• SQLite 3.16.2

[ara4n]

ugh, what a mess - thank you for the excellent bug report. This is all fallout from https://matrix.org/blog/2018/05/01/security-update-synapse-0-28-1/.

The workaround is to (having first taken a backup) to update room_depth set min_depth=9223372036854775807 where min_depth>9223372036854775807 and likewise anywhere it chokes on a depth parameter. Meanwhile the full fix for the depth abuse will resolve this too, but this is still a few days away from landing.

[heftig]

The workaround is to (having first taken a backup) to update room_depth set min_depth=9223372036854775807 where min_depth>9223372036854775807

Not quite; note that the value it tries to insert is already 2⁶³-1 and not larger. The problem is that the schema uses the integer (32-bit) and not the bigint (64-bit) type.

Similarly, I cannot join #matrix on my HS because Synapse fails to set the min_depth with an identical psycopg2.DataError: integer out of range. The postgres log contains:

postgres[508]: ERROR:  integer out of range
postgres[508]: STATEMENT:  UPDATE room_depth SET min_depth = 9223372036854775807 WHERE room_id = '!cURbafjkfsMDVwdRDQ:matrix.org'

One ALTER TABLE room_depth ALTER min_depth TYPE bigint; later and it is working.

[jirutka]

@heftig I have exactly the same problem.

[thegcat]

I can confirm I had a similar problem on a DB that was created on postgresql and altering the table as described it above fixed it.

[gusttt]

I can confirm we had the same issue when running synapse_port_db using the current latest version of matrix-synapse 0.33.5.1.

The solution/workaround as described above was required to make it work:
ALTER TABLE room_depth ALTER min_depth TYPE bigint;

[BobVul]

Given that 1.5.0 recommends migration to postgres in all production instances, is manual editing of postgres column definitions the recommended way to do this?