SQLite to Postgres migration script has errors

[tfreedman]

I tried to migrate my database from SQLite to Postgres earlier today, and ran into various issues relating to duplicate keys violating various constraints, causing the import script to crash. A couple examples are below, but running the script multiple times will generally find different invalid rows.

FirstError: FirstError[#21, [Failure instance: Traceback: <class 'psycopg2.IntegrityError'>: duplicate key value violates unique constraint "room_aliases_room_alias_key"
DETAIL:  Key (room_alias)=(#facebook_puppetStatusRoom:tylerfreedman.com) already exists.

FirstError: FirstError[#13, [Failure instance: Traceback: <class 'psycopg2.IntegrityError'>: duplicate key value violates unique constraint "events_event_id_key"
DETAIL:  Key (event_id)=($1465749830388074iiiIY:matrix.org) already exists.

Validating the SQLite confirms that there's nothing wrong with the DB:

/var/lib/matrix-synapse$ sudo sqlite3 homeserver.db.snapshot
SQLite version 3.11.0 2016-02-15 17:29:24
sqlite> PRAGMA integrity_check;
ok

I'm mostly just interested in getting the thing to Postgres for performance reasons, so any kind of update to the script that'll allow me to continue importing instead of just crashing would be greatly appreciated.

Also, someone should (eventually) make the constraints on both databases identical (where possible), so that valid SQLite files aren't rejected by Postgres.

[edited by @richvdh 2018/02/28 to format logs sanely]

[ara4n]

I'm looking into a similar problem for ash, which is full of:

Dec 05 06:41:07 helios python[523]: IntegrityError: null value in column "state_group" violates not-null constraint
Dec 05 06:41:07 helios python[523]: DETAIL:  Failing row contains ($15124736261133468acSTH:matrix.org, null).

...from a DB which was originally a sqlite->postgres migration.

[ara4n]

...actually, this is a red herring; ash's problems are due to _persist_events_txn trying to insert null state groups for reasons, not DB corruption (filed as #2730)

[ndarilek]

Wonder if these are related? This is what I hit today:

2018-02-01 17:56:21,827 - synapse_port_db - 172 - ERROR - Failed to insert: sent_transactions
Traceback (most recent call last):
  File "/usr/local/bin/synapse_port_db", line 168, in insert_many_txn
    txn.executemany(sql, rows)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 85, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py", line 112, in _do_execute
    sql, *args
IntegrityError: duplicate key value violates unique constraint "sent_transactions_pkey"
DETAIL:  Key (id)=(449226) already exists.

2018-02-01 17:56:21,837 - synapse_port_db - 555 - ERROR - 
Traceback (most recent call last):
  File "/usr/local/bin/synapse_port_db", line 539, in run
    consumeErrors=True,
FirstError: FirstError[#43, [Failure instance: Traceback: <class 'psycopg2.IntegrityError'>: duplicate key value violates unique constraint "sent_transactions_pkey"
DETAIL:  Key (id)=(449226) already exists.

/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:500:errback
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:567:_startRunCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:653:_runCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1442:gotResult
--- <exception caught here> ---
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1384:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:408:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:196:setup_table
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1384:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:408:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:627:_setup_sent_transactions
/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py:250:inContext
/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py:266:<lambda>
/usr/local/lib/python2.7/dist-packages/twisted/python/context.py:122:callWithContext
/usr/local/lib/python2.7/dist-packages/twisted/python/context.py:85:callWithContext
/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:307:_runWithConnection
/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:298:_runWithConnection
/usr/local/bin/synapse_port_db:135:r
/usr/local/bin/synapse_port_db:624:insert
/usr/local/bin/synapse_port_db:168:insert_many_txn
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:85:executemany
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:112:_do_execute
]]
Traceback (most recent call last):
  File "/usr/local/bin/synapse_port_db", line 539, in run
    consumeErrors=True,
FirstError: FirstError[#43, [Failure instance: Traceback: <class 'psycopg2.IntegrityError'>: duplicate key value violates unique constraint "sent_transactions_pkey"
DETAIL:  Key (id)=(449226) already exists.

/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:500:errback
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:567:_startRunCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:653:_runCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1442:gotResult
--- <exception caught here> ---
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1384:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:408:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:196:setup_table
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1384:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:408:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:627:_setup_sent_transactions
/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py:250:inContext
/usr/local/lib/python2.7/dist-packages/twisted/python/threadpool.py:266:<lambda>
/usr/local/lib/python2.7/dist-packages/twisted/python/context.py:122:callWithContext
/usr/local/lib/python2.7/dist-packages/twisted/python/context.py:85:callWithContext
/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:307:_runWithConnection
/usr/local/lib/python2.7/dist-packages/twisted/enterprise/adbapi.py:298:_runWithConnection
/usr/local/bin/synapse_port_db:135:r
/usr/local/bin/synapse_port_db:624:insert
/usr/local/bin/synapse_port_db:168:insert_many_txn
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:85:executemany
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:112:_do_execute

Really curious if there's a fix. I'm having forward extremity issues once again, and the SQL in #1760 doesn't seem to work on SQLite.

Thanks!

[dos1701]

I found the same issue yesterday night:
bug2.log
And I found it's related to an incorrect handling of a a sequence, a simple select setval fixed it.
I'll try to take a look at the code of the sqlite->postgres script to see if i can fix it there.

[richvdh]

@dos1701 your problem is #3050 which is a separate problem (and fixed in synapse 0.27.3)

[richvdh]

this seems sufficiently old that it doesn't seem useful any more