Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

Migration to postgresql is broken on table users, column shadow_banned #8384

Closed
ShadowJonathan opened this issue Sep 23, 2020 · 6 comments · Fixed by #8386
Closed

Migration to postgresql is broken on table users, column shadow_banned #8384

ShadowJonathan opened this issue Sep 23, 2020 · 6 comments · Fixed by #8386
Assignees
@clokep
Labels
z-bug (Deprecated Label) z-regression (Deprecated Label)

Comments

@ShadowJonathan
Copy link
Contributor

ShadowJonathan commented Sep 23, 2020 •
edited
Loading

Description

Migrating from sqlite to postgresql is broken, most probably since version v1.20.0, which introduced shadowbanning

Steps to reproduce

  • Boot up a new server with a database in sqlite
  • Attempt to migrate this database to postgresql
  • Observe error

Version information

  • Homeserver: v1.20.0

  • Install method: Docker

  • Platform: Debian 10

Traceback

2020-09-23 14:39:56,457 - synapse_port_db - 298 - INFO - Table group_attestations_renewals: 0/6 (rows 1-0) already ported
user_directory_stream_pos: 100% (1/1)
event_push_summary_stream_ordering: 100% (1/1)
2020-09-23 14:39:56,496 - synapse.metrics - 545 - INFO - Collecting gc 1
appservice_stream_position: 100% (1/1)
event_push_summary: 100% (20/20)
room_tags: 100% (36/36)
pushers: 100% (1/1)
event_reports: 100% (1/1)
user_filters: 100% (11/11)
room_aliases: 100% (30/30)
deleted_pushers: 100% (4/4)
room_tags_revisions: 100% (41/41)
e2e_one_time_keys_json: 100% (300/300)
local_group_membership: 100% (86/86)
2020-09-23 14:39:57,043 - synapse.metrics - 545 - INFO - Collecting gc 1
2020-09-23 14:39:57,061 - synapse.metrics - 545 - INFO - Collecting gc 2
2020-09-23 14:39:57,498 - synapse.metrics - 545 - INFO - Collecting gc 1
2020-09-23 14:39:57,592 - synapse_port_db - 195 - ERROR - Failed to insert: users
Traceback (most recent call last):
  File "./scripts/synapse_port_db", line 193, in insert_many_txn
    txn.executemany(sql, rows)
  File "/usr/local/lib/python3.7/site-packages/synapse/storage/database.py", line 215, in executemany
    self._do_execute(self.txn.executemany, sql, *args)
  File "/usr/local/lib/python3.7/site-packages/synapse/storage/database.py", line 238, in _do_execute
    return func(sql, *args)
psycopg2.errors.DatatypeMismatch: column "shadow_banned" is of type boolean but expression is of type integer
LINE 1: ... 1600800333, 0, NULL, 0, 'telegram', NULL, NULL, NULL, 0, 0)
                                                                     ^
HINT:  You will need to rewrite or cast the expression.

2020-09-23 14:39:57,604 - synapse_port_db - 636 - ERROR - 
Traceback (most recent call last):
  File "./scripts/synapse_port_db", line 622, in run
    consumeErrors=True,
twisted.internet.defer.FirstError: FirstError[#14, [Failure instance: Traceback: <class 'psycopg2.errors.DatatypeMismatch'>: column "shadow_banned" is of type boolean but expression is of type integer
LINE 1: ... 1600800333, 0, NULL, 0, 'telegram', NULL, NULL, NULL, 0, 0)
                                                                     ^
HINT:  You will need to rewrite or cast the expression.

/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:501:errback
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:568:_startRunCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:654:_runCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1475:gotResult
--- <exception caught here> ---
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1416:_inlineCallbacks
/usr/local/lib/python3.7/site-packages/twisted/python/failure.py:512:throwExceptionIntoGenerator
./scripts/synapse_port_db:386:handle_table
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:541:runInteraction
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:590:runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:250:inContext
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:266:<lambda>
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:122:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:85:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:306:_runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/compat.py:464:reraise
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:297:_runWithConnection
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:587:inner_func
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:429:new_transaction
./scripts/synapse_port_db:374:insert
./scripts/synapse_port_db:193:insert_many_txn
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:215:executemany
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:238:_do_execute
]]
Traceback (most recent call last):
  File "./scripts/synapse_port_db", line 622, in run
    consumeErrors=True,
twisted.internet.defer.FirstError: FirstError[#14, [Failure instance: Traceback: <class 'psycopg2.errors.DatatypeMismatch'>: column "shadow_banned" is of type boolean but expression is of type integer
LINE 1: ... 1600800333, 0, NULL, 0, 'telegram', NULL, NULL, NULL, 0, 0)
                                                                     ^
HINT:  You will need to rewrite or cast the expression.

/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:501:errback
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:568:_startRunCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:654:_runCallbacks
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1475:gotResult
--- <exception caught here> ---
/usr/local/lib/python3.7/site-packages/twisted/internet/defer.py:1416:_inlineCallbacks
/usr/local/lib/python3.7/site-packages/twisted/python/failure.py:512:throwExceptionIntoGenerator
./scripts/synapse_port_db:386:handle_table
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:541:runInteraction
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:590:runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:250:inContext
/usr/local/lib/python3.7/site-packages/twisted/python/threadpool.py:266:<lambda>
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:122:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/python/context.py:85:callWithContext
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:306:_runWithConnection
/usr/local/lib/python3.7/site-packages/twisted/python/compat.py:464:reraise
/usr/local/lib/python3.7/site-packages/twisted/enterprise/adbapi.py:297:_runWithConnection
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:587:inner_func
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:429:new_transaction
./scripts/synapse_port_db:374:insert
./scripts/synapse_port_db:193:insert_many_txn
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:215:executemany
/usr/local/lib/python3.7/site-packages/synapse/storage/database.py:238:_do_execute
]]
Traceback (most recent call last):
  File "./scripts/synapse_port_db", line 1078, in <module>
    sys.stderr.write(end_error)
TypeError: write() argument must be str, not FirstError
@clokep clokep added z-bug (Deprecated Label) z-regression (Deprecated Label) labels Sep 23, 2020
@anoadragon453
Copy link
Member

anoadragon453 commented Sep 23, 2020 •
edited
Loading

Also good to know would be why the synapse_port_db tests didn't catch this.

Edit: Perhaps because the database it uses needs updating.

@anoadragon453
Copy link
Member

OK, looking at the logs it looks like the bit whether a user is shadow banned is stored as a BOOLEAN:

synapse/synapse/storage/databases/main/schema/delta/58/09shadow_ban.sql

Line 18 in ac77cdb

ALTER TABLE users ADD COLUMN shadow_banned BOOLEAN;

This translates to an INTEGER of either 0 or 1 in SQLite. However, when we try to bring it over to Postgres, it complains that this column should be a boolean.

This certainly isn't the only column of type BOOLEAN that we have in our database:

synapse/synapse/storage/databases/main/schema/delta/56/stats_separated.sql

Line 152 in a7bdf98

is_federatable BOOLEAN,

or

synapse/synapse/storage/databases/main/schema/delta/56/hidden_devices.sql

Line 18 in a7bdf98

ALTER TABLE devices ADD COLUMN hidden BOOLEAN DEFAULT FALSE;

So why is it a problem with this field?

@clokep
Copy link
Member

clokep commented Sep 23, 2020

I'm guessing it needs to be added here:

synapse/scripts/synapse_port_db

Lines 69 to 92 in 2ffd678

BOOLEAN_COLUMNS = {
"events": ["processed", "outlier", "contains_url"],
"rooms": ["is_public"],
"event_edges": ["is_state"],
"presence_list": ["accepted"],
"presence_stream": ["currently_active"],
"public_room_list_stream": ["visibility"],
"devices": ["hidden"],
"device_lists_outbound_pokes": ["sent"],
"users_who_share_rooms": ["share_private"],
"groups": ["is_public"],
"group_rooms": ["is_public"],
"group_users": ["is_public", "is_admin"],
"group_summary_rooms": ["is_public"],
"group_room_categories": ["is_public"],
"group_summary_users": ["is_public"],
"group_roles": ["is_public"],
"local_group_membership": ["is_publicised", "is_admin"],
"e2e_room_keys": ["is_verified"],
"account_validity": ["email_sent"],
"redactions": ["have_censored"],
"room_stats_state": ["is_federatable"],
"local_media_repository": ["safe_from_quarantine"],
}

@clokep
Copy link
Member

clokep commented Sep 23, 2020

Edit: Perhaps because the database it uses needs updating.

This is because the database is empty, I think.

@clokep clokep self-assigned this Sep 23, 2020
@richvdh
Copy link
Member

richvdh commented Sep 23, 2020

yeah the fix is relatively easy. The fact that it got missed in the regression tests is the bigger problem here imho

@clokep clokep mentioned this issue Sep 23, 2020
Merged
@clokep
Copy link
Member

clokep commented Sep 23, 2020

The simple fix is up at #8386.

@clokep clokep mentioned this issue Sep 30, 2020
Closed
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees

@clokep clokep

Labels
z-bug (Deprecated Label) z-regression (Deprecated Label)
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Mark the shadow_banned column as boolean in synapse_port_db. matrix-org/synapse
4 participants
@clokep @anoadragon453 @richvdh @ShadowJonathan