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.
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
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?
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 …
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.
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):
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.
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.
I just renamed the hws-connect tag to hws-connect1 with
UPDATE tags SET name = 'hws-connect1' WHERE name='hws-connect';
Leave the Inception with some kicks backwards: \q to leave postgres exit to leave the docker container
Do the rebuild again with sudo ./launcher rebuild app
Be happy it works and verify in front end what you did:
Go to your tags page https://your-forum/tags
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.
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: