Duplicate key value violates unique constraint on a table during upgrade

[Steven]

Hi everyone, I have a weird issue with one forum I maintain.

During an upgrade, in the admin/upgrade page or in the terminal, it failed and I have this error:

--------------------------------------------------------------------------------
1 migrations failed!

Failed to migrate default
#<ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_screened_ip_addresses_on_ip_address"
DETAIL:  Key (ip_address)=(10.0.0.0/8) already exists.

---

Fortunately, I didn’t break the forum, with the ./launcher restart app command (./launcher destroy app / ./launcher start app saved me once when I had a blank page from a restart), so there’s no emergency at all, that’s why I wanted some guidance.

I decided to inspect the database, I have a fewreferences to the ip 10.0.0.0/8 that looks like some logs, but in the screened_ip_addresses table, I don’t have any duplicate.

--
-- TOC entry 6829 (class 0 OID 382198)
-- Dependencies: 657
-- Data for Name: screened_ip_addresses; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.screened_ip_addresses (id, ip_address, action_type, match_count, last_match_at, created_at, updated_at) FROM stdin;
236	10.0.0.0/8	2	0	\N	2020-05-24 19:44:41.587257	2020-05-24 19:44:41.587257
237	192.168.0.0/16	2	0	\N	2020-05-24 19:44:47.150337	2020-05-24 19:44:47.150337
239	172.16.0.0/12	2	0	\N	2020-05-24 19:44:57.347656	2020-05-24 19:44:57.347656
240	fc00::/7	2	0	\N	2020-05-24 19:45:02.270948	2020-05-24 19:45:02.270948
261	154.71.107.147	1	0	\N	2020-06-05 13:15:17.718236	2020-06-07 00:27:57.204765
257	154.126.107.81	1	0	\N	2020-06-02 09:51:31.191431	2020-06-07 00:27:58.538628
259	197.1.186.242	1	0	\N	2020-06-05 08:39:52.218198	2020-06-07 00:27:58.985867
258	89.158.72.7	1	0	\N	2020-06-02 20:44:41.584317	2020-06-07 00:27:59.542337
260	196.179.229.13	1	0	\N	2020-06-05 08:39:52.227515	2020-06-07 00:28:00.288445
238	127.0.0.0/8	2	0	\N	2020-05-24 19:44:52.369958	2020-05-24 19:44:52.369958

The forum admin told me he cleaned some IPs in the /admin/logs/screened_ip_addresses page and added some. But I doubt he touched this ip. The weird thing is it says the ip has been added 14 days ago (it was the last time I upgraded, and it was probably the postgresql upgrade) even though the forum is from 2015. And on other forums, it checks with the creation date.

So I guess, the table is a bit messed up, but it doesn’t really look like it.

I don’t want to try my luck too much, especially because I suck at sql. So I wanted some guidance on the procedure to follow to make sure I can upgrade safely

Should I clean the page /admin/logs/screened_ip_addresses, try to purge entries for this table or another?

[pfaffman (Jay Pfaffman)]

Sounds like the index is corrupted. You can look around at some other topics about corrupt indexes.

First you can try to rebuild the index. Also note that many searches won’t find the duplicate entries because they assume that the index works.

[Steven]

Thanks, to be sure, it would be something like that?

cd /var/discourse
./launcher enter app
su postgres
psql
\connect discourse
REINDEX SCHEMA CONCURRENTLY public;

If it doesn’t help because the index looks fine to system, does this solution seems viable given the size of this table?

TRUNCATE public.screened_ip_addresses

I run a reindex, then I add manually those ip again?

[pfaffman (Jay Pfaffman)]

I’m on my phone so you’ll have to rtfm, but I would rebuild just public.screened_ip_addresses. I think when you do you’ll get errors about the conflicts.

[Steven]

Thanks, I ran the command, no error mentionned. I’ll try another rebuild in a few hour and I’ll see

edit : ok the reindex didn’t do anything

1 migrations failed!

Failed to migrate default
#<ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_screened_ip_addresses_on_ip_address"
DETAIL:  Key (ip_address)=(10.0.0.0/8) already exists.
>

I restarted the app, cleaned all the ip in /admin/logs/screened_ip_addresses, and upgraded successfully.

The ip are back, didn’t have to add them manually

Maybe I took a little too much caution, but since it’s not my forum, I didn’t want to mess things up. Thanks @pfaffman for the help!

I guess I’ll see next time if it happens again but at least I’ll know how to fix it

[Steven]

Last update for this topic, last time I upgraded via the admin/upgrade page. This time I wanted to rebuild with the terminal to be sure. No migration issue. It went well.

I can confirm the issue is closed