How does one run sql queries if the container doesn't start? (rebuild failed)

Hello.

I tried to run updates & rebuild, but I ended up with this beautiful error:

I, [2023-01-18T08:05:48.701709 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'LOAD_PLUGINS=0 bundle exec rake plugin:pull_compatible_all'
I, [2023-01-18T08:05:52.431210 #1]  INFO -- :
I, [2023-01-18T08:05:52.431807 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2023-01-18 08:05:59.081 UTC [1166] discourse@discourse ERROR:  could not create unique index "index_tags_on_name_ccnew_ccnew_ccnew5"
2023-01-18 08:05:59.081 UTC [1166] discourse@discourse DETAIL:  Key (name)=(vuejs) is duplicated.
2023-01-18 08:05:59.081 UTC [1166] discourse@discourse STATEMENT:  REINDEX INDEX CONCURRENTLY index_tags_on_name_ccnew_ccnew
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::UniqueViolation: ERROR:  could not create unique index "index_tags_on_name_ccnew_ccnew_ccnew5"
DETAIL:  Key (name)=(vuejs) is duplicated.
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `exec'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:110:in `async_exec'

There are other topics on the forum with similar issues (1, 2, 3) but when I try to enter the container I get this:

/var/discourse# ./launcher enter app
x86_64 arch detected.
Error: No such container: app

Therefore I’m unable to drop indices manually.

Any other suggestion on what aproach should I take next?

1 Like

Hello Ionut,

Thanks for the bug report. I will have a look at this, but I believe there is an issue with an index in your database and a recent migration attempts to fix that, but some issues cannot be fixed without a human intervention.

I believe the commit that introduced the migration that fails is this one:

You can try pinning your Discourse instance to the previous version which is commit 690e2f15ab9549486aaa6750e1093c1336bf17f2. Edit your app.yml file and set the version key under params. Make sure to uncomment that key too!

Then, everything should start and you should be able to remove the duplicated vuejs tag, but that might have some unwanted effects if the duplicated tag is in use. We will try to find a better solution, but this should work until then.

2 Likes

After booting up, I was able to find all duplicate tags with Data Explorer plugin:

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

For some reasons, there were about ten duplicate tags. Wonder why and how those appear there in the first place

Managed to wipe those sucker out, removed the version lock from yml file, rebuild, things looks fine now.

Thank you!

:+1:

4 Likes

We have seen this in the past, it is a nightmare problem but isolated it to being upgrade related.

Index structure in PG depends on the locale at install time and upgrades can lead to a nightmare sometimes.

If the reindex is not issued early enough, trouble can ensue. Some of our earlier PG docker upgrades did not run this… and some old installs sadly have a bit of a time bomb.

This is mostly resolved these days in the ecosystem but some pain remains.

1 Like

Maybe this can help for further indentifying the cause, so here are some more details about the system:

The install is indeed ancient, since 2014, but the system is updated at least once a month (though usually is every other week). So there was never a major version skipped.

There is no version lock, so basically we’re running on latest version available on main branch (YOLO!)

Although we’ve tried a very few non-official a while back, all installed plugins are official and it was this way for the past… 5 years or more.

1 Like

Having the same issue, I’ve followed you until this sentence.

How do you actually remove the duplicates? Simply issuing a delete SQL statement in Data Explorer? What about foreign tables records pointing to this tag?

Update 1

OK, I see that you cannot execute update statements.

I’ve now used the GUI and simply renamed the tag.

Edit the tag (by going to /tag/foo) and either rename it or remove it.