Database conversion to Postgres fails due to string literal with NUL characters

[aaronraimist]

I am attempting to convert my Synapse database from SQLite to Postgres. When running the port script I recieved the following error:

Traceback (most recent call last):
  File "/usr/local/bin/synapse_port_db", line 518, in run
    consumeErrors=True,
FirstError: FirstError[#11, [Failure instance: Traceback: <type 'exceptions.ValueError'>: A string literal cannot contain NUL (0x00) characters.
/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:1357:gotResult
--- <exception caught here> ---
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1299:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:393:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:250:handle_table
/usr/local/lib/python2.7/dist-packages/twisted/internet/defer.py:1299:_inlineCallbacks
/usr/local/lib/python2.7/dist-packages/twisted/python/failure.py:393:throwExceptionIntoGenerator
/usr/local/bin/synapse_port_db:387:handle_search_table
/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:125:r
/usr/local/bin/synapse_port_db:374:insert
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:83:executemany
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:110:_do_execute
]]

How can I go about debugging and fixing this?

[VShell]

I'm having exactly the same issue (with handle_search_table as in the traceback here) - wondering if there's some way I can delete the table's data, and regenerate it once it's loaded into postgres, seeing as it's a search index which should be able to be regenerated from other data?

[mpiscaer]

First I hit the #2287 issue, after applying the patch. I get this problem. I droped the database. Then try to migrate the database. But this issue stays. What can I do to solve this?

[aaronraimist]

Just to update this issue, it is still present with the latest v0.23.0 release candidates although it is now a slightly different line number. @erikjohnston @ara4n Any idea what I can do about this?

Traceback (most recent call last):
  File "/usr/local/bin/synapse_port_db", line 536, in run
    consumeErrors=True,
FirstError: FirstError[#13, [Failure instance: Traceback: <type 'exceptions.ValueError'>: A string literal cannot contain NUL (0x00) characters.
/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:251:handle_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:407:handle_search_table
/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:126:r
/usr/local/bin/synapse_port_db:394:insert
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:87:executemany
/usr/local/lib/python2.7/dist-packages/synapse/storage/_base.py:114:_do_execute
]]

[richvdh]

In order to help diagnose this, it might help if you could share logs of the failing command (possibly with -v).

[aaronraimist]

Here is part of the log. I tried to run it and output the logs to a file but for some reason that didn't work so I copied this from the terminal scrollback. I'll try to run it again but let me know if this helps at all. I didn't see any private info in here but let me know if there could be.

https://gist.github.com/aaronraimist/e4b312c8d8dd44fc03787eae659a1bd7

[richvdh]

sorry, I missed the previous references to handle_search_table which makes it obvious which table is the problem. I've made a patch on the rav/port_db_fixes branch. Could you try that and see if it helps?

[aaronraimist]

This definitely fixed the db port script. Using that patch, the script didn't give any error message and seems to have completed normally. I think my server is running off postgres now!

I'll just leave this issue open to make sure that patch lands in the next release in case anyone else is having this problem.

[richvdh]

I've merged the patch so will close this now.