20180528141303_fix_accounts_unique_index.rb 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112
  1. class FixAccountsUniqueIndex < ActiveRecord::Migration[5.2]
  2. class Account < ApplicationRecord
  3. # Dummy class, to make migration possible across version changes
  4. has_one :user, inverse_of: :account
  5. def local?
  6. domain.nil?
  7. end
  8. def acct
  9. local? ? username : "#{username}@#{domain}"
  10. end
  11. end
  12. class StreamEntry < ApplicationRecord
  13. # Dummy class, to make migration possible across version changes
  14. belongs_to :account, inverse_of: :stream_entries
  15. end
  16. disable_ddl_transaction!
  17. def up
  18. if $stdout.isatty
  19. say ''
  20. say 'WARNING: This migration may take a *long* time for large instances'
  21. say 'It will *not* lock tables for any significant time, but it may run'
  22. say 'for a very long time. We will pause for 10 seconds to allow you to'
  23. say 'interrupt this migration if you are not ready.'
  24. say ''
  25. say 'This migration will irreversibly delete user accounts with duplicate'
  26. say 'usernames. You may use the `rake mastodon:maintenance:find_duplicate_usernames`'
  27. say 'task to manually deal with such accounts before running this migration.'
  28. 10.downto(1) do |i|
  29. say "Continuing in #{i} second#{i == 1 ? '' : 's'}...", true
  30. sleep 1
  31. end
  32. end
  33. duplicates = Account.connection.select_all('SELECT string_agg(id::text, \',\') AS ids FROM accounts GROUP BY lower(username), lower(domain) HAVING count(*) > 1').to_hash
  34. duplicates.each do |row|
  35. deduplicate_account!(row['ids'].split(','))
  36. end
  37. remove_index :accounts, name: 'index_accounts_on_username_and_domain_lower' if index_name_exists?(:accounts, 'index_accounts_on_username_and_domain_lower')
  38. safety_assured { execute 'CREATE UNIQUE INDEX CONCURRENTLY index_accounts_on_username_and_domain_lower ON accounts (lower(username), lower(domain))' }
  39. remove_index :accounts, name: 'index_accounts_on_username_and_domain' if index_name_exists?(:accounts, 'index_accounts_on_username_and_domain')
  40. end
  41. def down
  42. raise ActiveRecord::IrreversibleMigration
  43. end
  44. private
  45. def deduplicate_account!(account_ids)
  46. accounts = Account.where(id: account_ids).to_a
  47. accounts = accounts.first.local? ? accounts.sort_by(&:created_at) : accounts.sort_by(&:updated_at).reverse
  48. reference_account = accounts.shift
  49. say_with_time "Deduplicating @#{reference_account.acct} (#{accounts.size} duplicates)..." do
  50. accounts.each do |other_account|
  51. if other_account.public_key == reference_account.public_key
  52. # The accounts definitely point to the same resource, so
  53. # it's safe to re-attribute content and relationships
  54. merge_accounts!(reference_account, other_account)
  55. elsif other_account.local?
  56. # Since domain is in the GROUP BY clause, both accounts
  57. # are always either going to be local or not local, so only
  58. # one check is needed. Since we cannot support two users with
  59. # the same username locally, one has to go. 😢
  60. other_account.user&.destroy
  61. end
  62. other_account.destroy
  63. end
  64. end
  65. end
  66. def merge_accounts!(main_account, duplicate_account)
  67. [Status, Mention, StatusPin, StreamEntry].each do |klass|
  68. klass.where(account_id: duplicate_account.id).in_batches.update_all(account_id: main_account.id)
  69. end
  70. # Since it's the same remote resource, the remote resource likely
  71. # already believes we are following/blocking, so it's safe to
  72. # re-attribute the relationships too. However, during the presence
  73. # of the index bug users could have *also* followed the reference
  74. # account already, therefore mass update will not work and we need
  75. # to check for (and skip past) uniqueness errors
  76. [Favourite, Follow, FollowRequest, Block, Mute].each do |klass|
  77. klass.where(account_id: duplicate_account.id).find_each do |record|
  78. begin
  79. record.update_attribute(:account_id, main_account.id)
  80. rescue ActiveRecord::RecordNotUnique
  81. next
  82. end
  83. end
  84. end
  85. [Follow, FollowRequest, Block, Mute].each do |klass|
  86. klass.where(target_account_id: duplicate_account.id).find_each do |record|
  87. begin
  88. record.update_attribute(:target_account_id, main_account.id)
  89. rescue ActiveRecord::RecordNotUnique
  90. next
  91. end
  92. end
  93. end
  94. end
  95. end