Problems while updating from 3.0.0 to 3.0.1

[Massi]

Hi,

today I updated our Discourse app to 3.0.1. The Update failed trying to process the postgres migration.
Specifically here:

2023-01-27 04:50:48.628 UTC [483] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
2023-01-27 04:50:48.628 UTC [483] discourse@discourse DETAIL:  Key (name)=(e-mail) already exists.
2023-01-27 04:50:48.628 UTC [483] discourse@discourse STATEMENT:  UPDATE tags t
	SET public_topic_count = x.topic_count
	FROM (
	  SELECT
	    COUNT(topics.id) AS topic_count,
	    tags.id AS tag_id
	  FROM tags
	  INNER JOIN topic_tags ON tags.id = topic_tags.tag_id
	  INNER JOIN topics ON topics.id = topic_tags.topic_id AND topics.deleted_at IS NULL AND topics.archetype != 'private_message'
	  INNER JOIN categories ON categories.id = topics.category_id AND NOT categories.read_restricted
	  GROUP BY tags.id
	) x
	WHERE x.tag_id = t.id
	AND x.topic_count <> t.public_topic_count;
	
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Some tags seemed to be have duplicates. I connected to postgres and corrected the tags. The migration and update ran through afterwards

My question is, have I handled it correctly messing around in the DB? I only changed the name of the duplicate tags.
I can’t say how the duplicate tags were created. They were inserted round the last 2 to 3 years.
I am not sure if this situation might occur again.

[B-iggy]

I have the same issue

[Ed_S]

Did you get exactly the same symptom?

ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(e-mail) already exists.

[B-iggy]

Yes. It’s also because of duplicated tags / index_tags_on_name.
In my case the tag is just named different.

Currently trying to figure out:

• how to upgrade ruby to 3.0.0 cause it complains web-push is at 2.6.7, so I can’t even enter rails c, to do fancy commands to fix tags
• how to delete the tag savely from the postgres database but no clue yet

[Ed_S]

Please post the fragment of the log which shows the error in your case!

If anyone is ever going to find out how these duplicate key values came into existence, they will appreciate more detail.

Sorry can’t help with database repair.

For the ruby problem, I think you need to do a command line upgrade (*). See for example

Failed upgrade from 2.9.0.beta14 to 3.1.0.beta

This is happen to me to in my production. From 2.9.0.beta14 (2b9fa41a6e) to 3.1.0.beta1 Fetching gem metadata from https://rubygems.org/......... Fetching https://github.com/rails/sprockets web-push-3.0.0 requires ruby version >= 3.0, which is incompatible with the current version, 2.7.6 Docker Manager: FAILED TO UPGRADE #<RuntimeError: RuntimeError> /var/www/discourse/plugins/docker_manager/lib/docker_manager/upgrader.rb:209:in `run' /var/www/discourse/plugins/docker_manager/lib/docker_manage…

(*) Edit: but see below, upgrade fails with the database as it is. Oops.

[B-iggy]

I, [2023-01-27T07:46:34.317438 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2023-01-27 07:46:45.663 UTC [584] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
2023-01-27 07:46:45.663 UTC [584] discourse@discourse DETAIL:  Key (name)=(hws-connect) already exists.
2023-01-27 07:46:45.663 UTC [584] discourse@discourse STATEMENT:  UPDATE tags t
        SET public_topic_count = x.topic_count
        FROM (
          SELECT
            COUNT(topics.id) AS topic_count,
            tags.id AS tag_id
          FROM tags
          INNER JOIN topic_tags ON tags.id = topic_tags.tag_id
          INNER JOIN topics ON topics.id = topic_tags.topic_id AND topics.deleted_at IS NULL AND topics.archetype != 'private_message'
          INNER JOIN categories ON categories.id = topics.category_id AND NOT categories.read_restricted
          GROUP BY tags.id
        ) x
        WHERE x.tag_id = t.id
        AND x.topic_count <> t.public_topic_count;

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(hws-connect) already exists.

This error comes up 3 times, because the tag seems to be found in 3 topics.

I regret turning tags on if I knew this would happen and why there wasn’t a warning about that on the frontend already.
I just want to delete them and finally get my forum up again

[B-iggy]

@Ed_S

I think you need to do a command line upgrade

I can rebuild the app as often as I want. I get the error and it aborts. Thus ruby stays not upgraded.
I’m basically stuck.

[Massi]

Hi,

maybe do a select with like:
SELECT * FROM tags WHERE name LIKE '%hws-connect%'

You should find multiple tags with the same name. That was the issue in my case.

[B-iggy]

I did a select:

my question is if I can rename the “hws-connect” tag in the database without corrupting the discourse infrastructure?
And… how can I rename it via postgres?

[merefield (Robert)]

OK a few things:

@B-iggy

I regret turning tags on if I knew this would happen and why there wasn’t a warning about that on the frontend already.

• you are going to have to accept the odd glitch when running your own install of multi-million dollar software (backed by a super helpful community) you got to use for (almost) free
• I’ve recently updated two sites that are fairly heavy users of tags and have had no issues.
• In my > 5 years of running multiple Discourse instances I think I’ve had a problem like this once?

Anyway, looks very similar issue to this, perhaps you can use a similar strategy to resolve?:

Duplicate data explorer key blocking rebuild

Hey all, Rare I ask for help, but I’m puzzled about how to resolve this. Just rebuilt my site and getting this: discourse-data-explorer is already at latest compatible version I, [2020-08-28T17:53:57.578920 #1] INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey" 2020-08-28 17:54:07.380 UTC [3060] discourse@discourse DETAIL:…

Looks like you were already on the right track here, you just have to follow it through …

[B-iggy]

@merefield

you are going to have to accept the odd glitch when running your own install of multi-million dollar software (backed by a super helpful community) you got to use for free

I know and it’s nothing about/against discourse or the upgrade itself. It happens and it’s ok.
Just wish I would not run in a stuck scenario where I have to fight against so many topics (docker, ruby, postgres, discourse). Not my daily business and it’s time investment.

Thanks for the link!
Will update my status.

[B-iggy]

Update: the best take away from your topic @merefield was

I guess I should be brave

Fiddling in a DB is always scary for me but in this case it worked.

For anyone with the same issue (duplicated tags / index_tags_on_name):

1. crack your fingers and start Inception Phase 1:
   cd /var/discourse
sudo ./launcher enter app

   If this fails because you get something like “no docker container running” or so type
   sudo docker ps -a --no-trunc
   This will list your available docker container and the ID. With that restart the container.
   sudo docker restart <container ID>
   Then sudo ./launcher enter app should work.

2. access your postgres DB and start Inception Phase 2:
   su discourse
psql
   The rebuild error log should have given you the culprit tag name. In my case it was

    ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
   2023-01-27 07:46:45.663 UTC [584] discourse@discourse DETAIL:  Key (name)=(hws-connect) already exists.
   

   So first search for your tag with

   select * from tags where name='hws-connect'; 
   

   That gives you the table you can see above in my posts.

3. I just renamed the hws-connect tag to hws-connect1 with

   UPDATE tags SET name = 'hws-connect1' WHERE name='hws-connect';
   

4. Leave the Inception with some kicks backwards:
   \q to leave postgres
   exit to leave the docker container

5. Do the rebuild again with sudo ./launcher rebuild app

6. Be happy it works and verify in front end what you did:
   Go to your tags page https://your-forum/tags
   

   

I have no clue how it happened and why this upgrade failed so badly - all previous ones worked, via web or terminal.
Will clean my tags up now.

Bonus:

1. You click at the renamed tag.
2. Remove it from all topics. 3 in my case.
3. Go back to the tags page and use the top right feature:
   
   

All done.
Thanks for all your help guys.

[merefield (Robert)]

Go you! Excellent job!

[Ed_S]

Great explanation (and demonstrated bravery) thanks!

My forums use tags too - as does this one, loads of them:
https://meta.discourse.org/tags

Evidently something rare and unfortunate can happen to create a duplicate key value.

[n1bff]

I too am bitten by this. Thanks for sharing your manual fixes, I just used that to fix ~20 of those in mind.

My forum was using lots of tags in any kind of MixEdCasE and this never was an issue until the upgrade. Seems like there is/was a bug that made duplicate entries, regardless of case.

  id   |          name          |         created_at         
-------+------------------------+----------------------------
   707 | ParkRide               | 2019-05-21 21:36:53.213993
 18982 | ParkRide               | 2020-06-05 18:43:09.409895

(Yes, there is difference in whitespace around those)

I am now stuck with one last duplicate and it cannot be fixed:

discourse=> select name from tags group by name having count(*) > 1; 
    name    
------------
 Bike--Ride
(1 row)

discourse=> UPDATE tags SET name = 'Bike--Ride_2' WHERE name = 'Bike--Ride';
ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(Bike--Ride_2) already exists.
discourse=> UPDATE tags SET name = 'Bike--Ride_3' WHERE name = 'Bike--Ride';
ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(Bike--Ride_3) already exists.
discourse=> UPDATE tags SET name = 'something is broken here' WHERE name = 'Bike--Ride';
ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(something is broken here) already exists.

while upgrading fails on

2023-02-01 18:56:58.610 UTC [475] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
2023-02-01 18:56:58.610 UTC [475] discourse@discourse DETAIL:  Key (name)=(Bike--Ride) already exists.

Any idea how to work around this?

[stoney27]

I want to say thanks. This helped me in my upgrade!

[jamiemcclelland (Jamie)]

I had the same error - no matter what new tag name I used it reported that it already exists. I got around it by updating the tag using the id instead of the name column:

UPDATE tags SET name = 'tag1' WHERE id = 1234