Users encountered a database error during a Discourse upgrade from 3.0.0 to 3.0.1, caused by duplicate tags violating the unique constraint index_tags_on_name. The issue was resolved by manually identifying and renaming duplicate tags in the PostgreSQL database. Massi first fixed the problem by renaming a duplicate tag, and B-iggy provided a detailed step-by-step guide, including accessing the database via Docker, identifying duplicates with SQL queries, and renaming them. Others, like n1bff, faced similar issues with case-sensitive or whitespace-related duplicates, and jamiemcclelland suggested updating by tag ID instead of name to bypass the constraint. The community emphasized the need for caution when modifying the database and acknowledged the rare but possible occurrence of such issues during upgrades.
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: