synapse_port_db not set up to handle boolean account_validity columns

[mjpieters]

Description

I've set up a trial synapse homeserver with the default sqlite3 database setup, but with account_validity enabled. Migrating this server to Postgresql fails to complete with the following error during porting:

[ ... log output elided ... ]
2019-05-31 14:00:45,455 - synapse.metrics - 372 - INFO - Collecting gc 0
push_rules_enable: 100% (2/2)
devices: 100% (14/14)
received_transactions: 7% (1000/13471)
2019-05-31 14:00:45,632 - synapse.metrics - 372 - INFO - Collecting gc 0
device_lists_outbound_last_success: 100% (594/594)
receipts_linearized: 100% (267/267)
user_threepids: 100% (2/2)
room_aliases: 100% (2/2)
2019-05-31 14:00:46,057 - synapse_port_db - 172 - ERROR - Failed to insert: account_validity
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 170, in insert_many_txn
    txn.executemany(sql, rows)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 131, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py", line 154, in _do_execute
    return func(sql, *args)
psycopg2.errors.DatatypeMismatch: column "email_sent" is of type boolean but expression is of type integer
LINE 1: ..._token) VALUES ('@foo:zopatista.com', 1566488512574, 0, NULL)
                                                                ^
HINT:  You will need to rewrite or cast the expression.

2019-05-31 14:00:46,061 - synapse_port_db - 548 - ERROR -
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 538, in run
    [self.handle_table(*res) for res in setup_res], consumeErrors=True
twisted.internet.defer.FirstError: FirstError[#93, [Failure instance: Traceback: <class 'psycopg2.errors.DatatypeMismatch'>: column "email_sent" is of type boolean but expression is of type integer
LINE 1: ..._token) VALUES ('@foo:zopatista.com', 1566488512574, 0, NULL)
                                                                ^
HINT:  You will need to rewrite or cast the expression.

/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:501:errback
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:568:_startRunCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:654:_runCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:1475:gotResult
--- <exception caught here> ---
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:1416:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py:512:throwExceptionIntoGenerator
/usr/bin/synapse_port_db:348:handle_table
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py:250:inContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py:266:<lambda>
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py:122:callWithContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py:85:callWithContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:306:_runWithConnection
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/compat.py:464:reraise
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:297:_runWithConnection
/usr/bin/synapse_port_db:136:r
/usr/bin/synapse_port_db:336:insert
/usr/bin/synapse_port_db:170:insert_many_txn
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py:131:executemany
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py:154:_do_execute
]]
Traceback (most recent call last):
  File "/usr/bin/synapse_port_db", line 538, in run
    [self.handle_table(*res) for res in setup_res], consumeErrors=True
twisted.internet.defer.FirstError: FirstError[#93, [Failure instance: Traceback: <class 'psycopg2.errors.DatatypeMismatch'>: column "email_sent" is of type boolean but expression is of type integer
LINE 1: ..._token) VALUES ('@foo:zopatista.com', 1566488512574, 0, NULL)
                                                                ^
HINT:  You will need to rewrite or cast the expression.

/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:501:errback
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:568:_startRunCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:654:_runCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:1475:gotResult
--- <exception caught here> ---
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/internet/defer.py:1416:_inlineCallbacks
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/failure.py:512:throwExceptionIntoGenerator
/usr/bin/synapse_port_db:348:handle_table
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py:250:inContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/threadpool.py:266:<lambda>
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py:122:callWithContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/context.py:85:callWithContext
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:306:_runWithConnection
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/python/compat.py:464:reraise
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:297:_runWithConnection
/usr/bin/synapse_port_db:136:r
/usr/bin/synapse_port_db:336:insert
/usr/bin/synapse_port_db:170:insert_many_txn
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py:131:executemany
/opt/venvs/matrix-synapse/lib/python3.7/site-packages/synapse/storage/_base.py:154:_do_execute
]]

Steps to reproduce

• Set up a synapse homeserver using sqlite as the database
• Enable account_validity (I used the example config in the homeserver.yaml file, and only changed the period configuration option from 6w to 12w)
• Create a new user
• Start the server, log in, poke about.
• Follow the postgres documentation steps to use the porst script, so shut down, create snapshot copy of the database, start server again, run the port script.

Version information

• Homeserver: zopatista.com (public URL https://matrix.zopatista.com)

If not matrix.org:

• Version: 0.99.5.2

• Install method: apt, from https://packages.matrix.org/debian/

• Platform: Linux (Debian Buster)

[mjpieters]

Presumably this bug can be fixed by adding an entry in the BOOLEAN_COLUMNS dictionary, e.g.

        "account_validity": ["email_sent"],

or is there more to it?

[mjpieters]

At any rate, adding that line to my copy of the /usr/bin/synapse_port_db script then running the script again fixed the issue for me, and I can verify that the account_validity rows have been ported correctly.

[richvdh]

or is there more to it?

that's all it needs

[babolivier]

Fixed in #5325, thanks for the report!