diff options
author | Erik Johnston <erikj@jki.re> | 2016-10-27 14:48:30 +0100 |
---|---|---|
committer | GitHub <noreply@github.com> | 2016-10-27 14:48:30 +0100 |
commit | e438699c59ae5e3cfb5ba0b18a1a5806070af7b6 (patch) | |
tree | 8c695af385f0b1a3d77ad437051eaa61ef7d063c /synapse/storage | |
parent | Update changelog (diff) | |
parent | Use most recently added binding, not most recently seen user. (diff) | |
download | synapse-e438699c59ae5e3cfb5ba0b18a1a5806070af7b6.tar.xz |
Merge pull request #1183 from matrix-org/erikj/fix_email_update
Fix user_threepids schema delta
Diffstat (limited to '')
-rw-r--r-- | synapse/storage/schema/delta/37/user_threepids.sql | 31 |
1 files changed, 30 insertions, 1 deletions
diff --git a/synapse/storage/schema/delta/37/user_threepids.sql b/synapse/storage/schema/delta/37/user_threepids.sql index ef8813e72a..cf7a90dd10 100644 --- a/synapse/storage/schema/delta/37/user_threepids.sql +++ b/synapse/storage/schema/delta/37/user_threepids.sql @@ -17,7 +17,36 @@ * Update any email addresses that were stored with mixed case into all * lowercase */ -UPDATE user_threepids SET address = LOWER(address) where medium = 'email'; + + -- There may be "duplicate" emails (with different case) already in the table, + -- so we find them and move all but the most recently used account. + UPDATE user_threepids + SET medium = 'email_old' + WHERE medium = 'email' + AND address IN ( + -- We select all the addresses that are linked to the user_id that is NOT + -- the most recently created. + SELECT u.address + FROM + user_threepids AS u, + -- `duplicate_addresses` is a table of all the email addresses that + -- appear multiple times and when the binding was created + ( + SELECT lower(u1.address) AS address, max(u1.added_at) AS max_ts + FROM user_threepids AS u1 + INNER JOIN user_threepids AS u2 ON u1.medium = u2.medium AND lower(u1.address) = lower(u2.address) AND u1.address != u2.address + WHERE u1.medium = 'email' AND u2.medium = 'email' + GROUP BY lower(u1.address) + ) AS duplicate_addresses + WHERE + lower(u.address) = duplicate_addresses.address + AND u.added_at != max_ts -- NOT the most recently created + ); + + +-- This update is now safe since we've removed the duplicate addresses. +UPDATE user_threepids SET address = LOWER(address) WHERE medium = 'email'; + /* Add an index for the select we do on passwored reset */ CREATE INDEX user_threepids_medium_address on user_threepids (medium, address); |