Internal Server Error on using some tags (ERROR: duplicate key value violates unique constraint "index_tags_on_name")

Getting the same thing on another tag:

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_tags_on_name"
DETAIL:  Key (name)=(file-uploading) already exists.
)
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-1.1.2/lib/patches/db/pg.rb:72:in `async_exec_params'

Is this a Discourse or AR/Rails bug? Seems it is trying to create the tag again even though it exists?

Can you look in data explorer in the tags table, maybe there is some sort of casing issue of Ecommerce vs ecommerce ?

1 Like

Hi Sam, I’ve looked in the table and there are:

ecommerce
e-commerce

With the second one (file-uploading) there’s:

file-downloading
file-uploading

They both have hyphens so I wonder if that’s causing it?

It settings we have force lowercase tags checked - could that be conflicting somehow too?

If you need me to check anything else please let me know :blush:

Not 100% sure, hold a day or so, @neil is very familiar with the tags system he may have some ideas here.

1 Like

Will do, thanks Sam :smiley:

I’ve been playing around with mixed case tag names and tags with hyphens in them, but can’t reproduce this error. What exact version of discourse are you using? Is this happening in a category with tag restrictions? What other tag settings do you use?

2 Likes

Hi Neil, I’m using 2.4.0.beta6 (8ca5aad1e2)

I’ve managed to fix the issue by manually renaming them in rails console - interestingly, the e.commerce tag was returning nil even though it existed! I located it by ID and then renamed-named it and it is now working as well.

I have a feeling this started happening when case sensitivity was introduced (we’ve had the problem a while but I usually wait for a few new releases as you generally fix things pretty quickly).

If you need me to investigate anything else let me know, otherwise I will go through all the tags (or error logs) and identify other broken tags and fix them accordingly. In case it’s any help, here’s the answers to your questions:

No - but all categories require at least one tag

1 Like

I don’t get what the problem was. You renamed “e.commerce” from what to what? I tested enabling/disabling the force lowercase tags settings, using the tags in mixed case, and it didn’t attempt to create duplicate tags.

3 Likes

I renamed tag-name to tag-name2 then back to tag-name

I used find by name to locate them:

Tag.find_by name: "tag-name"

However that did not work for e-commerce I had to find it by ID, which seems odd - not sure why that would happen.

The only other thing I can think of, is at some point I have ‘mass’ tagged threads by carrying out a search and then clicking on a number of Topics and then ‘append tag’ from the options. Not sure if that would make a difference tho : /

Sorry @neil I’ve encountered another issue which may be related.

Two tags with the same name, however my manual fix is not working this time.

[1] pry(main)> Tag.find_by(name: "liveview")
=> #<Tag:0x000055646b510750
 id: 1843,
 name: "liveview",
 topic_count: 25,
 created_at: Tue, 01 Oct 2019 07:42:44 UTC +00:00,
 updated_at: Tue, 01 Oct 2019 07:42:44 UTC +00:00,
 pm_topic_count: 0>
[2] pry(main)> t=Tag.find_by(id: 1036)
=> #<Tag:0x000055646b653978
 id: 1036,
 name: "liveview",
 topic_count: 150,
 created_at: Sun, 09 Sep 2018 16:19:15 UTC +00:00,
 updated_at: Sun, 09 Sep 2018 16:19:15 UTC +00:00,
 pm_topic_count: 0>
[3] pry(main)> t2=Tag.find_by(id: 1843)
=> #<Tag:0x000055646b6af7a0
 id: 1843,
 name: "liveview",
 topic_count: 25,
 created_at: Tue, 01 Oct 2019 07:42:44 UTC +00:00,
 updated_at: Tue, 01 Oct 2019 07:42:44 UTC +00:00,
 pm_topic_count: 0>
[4] pry(main)> t2.name = "liveview2"
=> "liveview2"
[5] pry(main)> t2.save
=> true
[6] pry(main)> t.name = "liveview1"
=> "liveview1"
[7] pry(main)> t.save
=> true
[8] pry(main)> t.name = "liveview"
=> "liveview"
[9] pry(main)> t.save
=> true
[10] pry(main)> t.name = "liveview1"
=> "liveview1"
[11] pry(main)> 

liveview2 works, but the lone liveview doesn’t, however it works when named liveview1 but when it’s name is changed back to liveview it just goes to Oops! That page doesn’t exist or is private.. Index related?

(nothing in /logs either)

There are two indexes to protect against this case. Is your database missing the indexes?

$ bin/rails db

discourse_development=# \d tags
                                          Table "public.tags"
     Column     |            Type             | Collation | Nullable |             Default              
----------------+-----------------------------+-----------+----------+----------------------------------
 id             | integer                     |           | not null | nextval('tags_id_seq'::regclass)
 name           | character varying           |           | not null | 
 topic_count    | integer                     |           | not null | 0
 created_at     | timestamp without time zone |           | not null | 
 updated_at     | timestamp without time zone |           | not null | 
 pm_topic_count | integer                     |           | not null | 0
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "index_tags_on_lower_name" UNIQUE, btree (lower(name::text))
    "index_tags_on_name" UNIQUE, btree (name)

A migration dated 2018 Sept 28 should have fixed tags with duplicate names and then created one of the indexes. Did you import your site from other forum software? If so, it might have created duplicate rows and then failed to create the indexes.

1 Like
     Column     |            Type             | Collation | Nullable |             Default              
----------------+-----------------------------+-----------+----------+----------------------------------
 id             | integer                     |           | not null | nextval('tags_id_seq'::regclass)
 name           | character varying           |           | not null | 
 topic_count    | integer                     |           | not null | 0
 created_at     | timestamp without time zone |           |          | 
 updated_at     | timestamp without time zone |           |          | 
 pm_topic_count | integer                     |           | not null | 0
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "index_tags_on_lower_name" UNIQUE, btree (lower(name::text))
    "index_tags_on_name" UNIQUE, btree (name)

No this has always been a Discourse forum - the only thing I’ve done is move to another server and so that meant a fresh install then imported via backup. Think this was done around August last year - could that have caused it?

Should I do something like REINDEX TABLE tags; to rebuild the indexes Neil?

Edit: just noticed created_at and updated_at does not have not null under nullable - will it matter?

Edit 2: Have just noticed there are two of these tags too:

1862	live-view
1486	live-view

Yes, I think reindexing the table is needed. But first you’ll need to rename the tags that have duplicate names, otherwise the reindex will fail. And hope that only the tags table has this problem…

I don’t see how a restore would create this problem. The indexes are there, but clearly aren’t working.

2 Likes

Ok done :smiley:

If anyone else needs the query to find duplicate tags it’s:

SELECT name, COUNT(*)
FROM tags
GROUP BY name
HAVING COUNT(*) > 1

Don’t scare me now Neil! :scream:

I did have a quick look at the categories table and that seems fine (there are a few duplicated entires based on name, but they are sub-categories).

Thanks for your help @sam and @neil :blush:

(The reindex didn’t have any effect on this issue tho Neil)

We have this index:

Are you 100% sure that exists on the table cause what you are describing is a completely broken Postgres which is infinitely unlikely, this would send complete shoke waves through the community.

Much more likely somehow at some point that index got dropped due to a human …

At a minimum I recommend you create unique index on tags (name) but what else can you have missing?

3 Likes

Hi Sam,

I have:

discourse=> \d tags
                                          Table "public.tags"
     Column     |            Type             | Collation | Nullable |             Default              
----------------+-----------------------------+-----------+----------+----------------------------------
 id             | integer                     |           | not null | nextval('tags_id_seq'::regclass)
 name           | character varying           |           | not null | 
 topic_count    | integer                     |           | not null | 0
 created_at     | timestamp without time zone |           |          | 
 updated_at     | timestamp without time zone |           |          | 
 pm_topic_count | integer                     |           | not null | 0
Indexes:
    "tags_pkey" PRIMARY KEY, btree (id)
    "index_tags_on_lower_name" UNIQUE, btree (lower(name::text))
    "index_tags_on_name" UNIQUE, btree (name)

And to recreate the indexes I…

  • renamed duplicate tags (so tag_name_1 and tag_name_2 etc)
  • I checked there were no more duplicate tags with the query in the above post
  • I went to rails db and then ran:
discourse=> REINDEX TABLE tags;
REINDEX

Do I need to do anything else other than that Sam?

Maybe try inserting a dupe tag and confirm your index is happy and not allowing dupe data any more?

1 Like

Looks good Sam:

[1] pry(main)> t=Tag.new
=> #<Tag:0x000055fe76499830 id: nil, name: nil, topic_count: 0, created_at: nil, updated_at: nil, pm_topic_count: 0>
[2] pry(main)> t
=> #<Tag:0x000055fe76499830 id: nil, name: nil, topic_count: 0, created_at: nil, updated_at: nil, pm_topic_count: 0>
[3] pry(main)> t.name ="test-five"
=> "test-five"
[4] pry(main)> t.save
=> true

[5] pry(main)> Tag.last
=> #<Tag:0x000055fe7671ce90
 id: 1995,
 name: "test-five",
 topic_count: 0,
 created_at: Fri, 08 Nov 2019 02:44:36 UTC +00:00,
 updated_at: Fri, 08 Nov 2019 02:44:36 UTC +00:00,
 pm_topic_count: 0

[6] pry(main)> t=Tag.new
=> #<Tag:0x000055fe76763a70 id: nil, name: nil, topic_count: 0, created_at: nil, updated_at: nil, pm_topic_count: 0>
[7] pry(main)> t.name ="test-five"
=> "test-five"
[8] pry(main)> t.save
=> false
[9] pry(main)> 

Also tried via the forum in a Topic:

If you think I should try anything else please let me know :smiley:

1 Like

Also just to confirm, I don’t think I have done anything else with the DB via the console so I’m not sure why this happened. I -think- it might have started after case sensitivity was introduced but can’t be sure. If you need me to check anything else to help ascertain the root of the issue just let me know Sam.