New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

synapse_port_db fails on `room_depth` table #3214

Open
drbawb opened this Issue May 12, 2018 · 5 comments

Comments

Projects
None yet
6 participants
@drbawb
Copy link

drbawb commented May 12, 2018

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

This comment has been minimized.

Copy link
Member

ara4n commented May 12, 2018

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

This comment has been minimized.

Copy link

heftig commented May 14, 2018

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

This comment has been minimized.

Copy link

jirutka commented May 17, 2018

@heftig I have exactly the same problem.

@thegcat

This comment has been minimized.

Copy link
Contributor

thegcat commented May 30, 2018

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

This comment has been minimized.

Copy link

gusttt commented Oct 1, 2018

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;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment