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

iamntz · January 18, 2023, 8:16am

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?

nbianca · January 18, 2023, 8:59am

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:

github.com/discourse/discourse

FIX: Add migration to reindex invalid indexes (#19858)

committed 03:04AM - 13 Jan 23 UTC

tgxworld

+23 -0

In Discourse, there are many migration files where we CREATE INDEX CONCURRENTLY …which requires us to set disable_ddl_transaction!. Setting disable_ddl_transaction! in a migration file runs the SQL statements outside of a transaction. The implication of this is that there is no ROLLBACK should any of the SQL statements fail. We have seen lock timeouts occuring when running CREATE INDEX CONCURRENTLY. When that happens, the index would still have been created but marked as invalid by Postgres. Per the postgres documentation: > If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. > The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY . (Another possibility is to rebuild the index with REINDEX INDEX CONCURRENTLY ). When such scenarios happen, we are supposed to either drop and create the index again or run a REINDEX operation. However, I noticed today that we have not been doing so in Discourse. Instead, we’ve been incorrectly working around the problem by checking for the index existence before creating the index in order to make the migration idempotent. What this potentially mean is that we might have invalid indexes which are lying around in the database which PG will ignore for querying purposes. This commits adds a migration which queries for all the invalid indexes in the `public` namespace and reindexes them.

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.

iamntz · January 18, 2023, 11:31am

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!

sam · January 18, 2023, 10:50pm

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.

github.com/docker-library/postgres

Debian Buster is going to cause some reindexing headaches

opened 09:42PM - 17 May 19 UTC

closed 06:18PM - 16 Jun 21 UTC

tianon

See https://lists.debian.org/debian-glibc/2019/03/msg00030.html, https://bugs.de…bian.org/926627, and https://salsa.debian.org/ddp-team/release-notes/commit/ad75c4b for some context. > When upgrading from stretch to buster, the glibc locale data is upgraded. > Specifically, this changes how PostgreSQL sorts data in text indexes. > To avoid corruption, such indexes need to be `REINDEX`ed > immediately after upgrading the `locales` or > `locales-all` packages, before putting back the database > into production. > > Suggested command: `sudo -u postgres reindexdb --all` I'm not entirely sure how we're going to handle this, but it does mean it won't be reasonable for us to simply upgrade the images from Stretch to Buster once Buster is released. :confused: :disappointed:

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.

iamntz · January 19, 2023, 3:30pm

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.

uwe_keim · January 20, 2023, 4:38pm

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.

iamntz · January 20, 2023, 8:29pm

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

system · February 19, 2023, 8:29pm

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Error during upgrading from Beta 3.1.x to latest Installation	3	800	January 20, 2023
Restoring from backup failing: failure creating postgres index Installation	7	884	May 28, 2020
Upgrade Issues: Failed Upgrade due to Duplicate Key, Failed Snapshot Restore Installation	7	1617	April 25, 2023
Import failing with `could not create unique index` Installation	6	2657	July 13, 2020
Upgrade failed with "duplicate key value violates unique constraint" error Installation	18	2331	March 30, 2020

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

Update 1

Related Topics