Problems while updating from 3.0.0 to 3.0.1

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.

1 Like

I have the same issue

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.
1 Like

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
1 Like

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

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

2 Likes
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 :cold_sweat:

1 Like

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.

1 Like

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.

1 Like

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?

OK a few things:

  • 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?:

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

2 Likes

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.

1 Like

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

I guess I should be brave

:smiley:
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. :v:
Thanks for all your help guys.

10 Likes

Go you! Excellent job!

1 Like

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.

3 Likes

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?

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

1 Like

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

1 Like