Modifying the database inside of a backup to remove a duplicate key tag so that it doesn't fail when restoring

merefield · August 26, 2023, 6:02am

I don’t think that’s the immediate conclusion you can/should be drawing. The issue should be straightforward:

The reason that index won’t build is you have at least two entries in tags table which resolve to the same thing when you take the lower case of their name. That’s what the error message is telling you.

So I think you still need to find the related entries in that single table that clash when going through that transform.

app/models/tag.rb

1209efb63


      
          #  created_at         :datetime         not null
          #  updated_at         :datetime         not null
          #  pm_topic_count     :integer          default(0), not null
          #  target_tag_id      :integer
          #  description        :string
          #  public_topic_count :integer          default(0), not null
          #  staff_topic_count  :integer          default(0), not null
          #
          # Indexes
          #
          #  index_tags_on_lower_name  (lower((name)::text)) UNIQUE
          #  index_tags_on_name        (name) UNIQUE
          #

Also, Posts aren’t tagged, Topics are.

Before you delete the duplicate(s) note their ids because you will need to delete the related rows from the topic_tags table too (something you could have taken care of quickly if you’d performed all this maintenance online by simply restarting the container btw instead of destroying the instance!!).

github.com/discourse/discourse

app/models/topic_tag.rb

1209efb63


      
              end
            end
          end
          
          # == Schema Information
          #
          # Table name: topic_tags
          #
          #  id         :integer          not null, primary key
          #  topic_id   :integer          not null
          #  tag_id     :integer          not null
          #  created_at :datetime         not null
          #  updated_at :datetime         not null
          #
          # Indexes
          #
          #  index_topic_tags_on_topic_id_and_tag_id  (topic_id,tag_id) UNIQUE
          #

Topic		Replies	Views
SQL Error during import Self-hosting	18	2702	May 3, 2015
Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes) Self-hosting	12	4975	January 6, 2020
Problems while updating from 3.0.0 to 3.0.1 Self-hosting	16	1471	June 14, 2023
Problem with restore Discourse from backup (3.4, quite large DB) Product Support	17	572	January 14, 2025
Error restoring backup "key is duplicated" Self-hosting	7	1983	August 30, 2017

Modifying the database inside of a backup to remove a duplicate key tag so that it doesn't fail when restoring

Related topics