升级期间重复键值违反表的唯一约束

[Steven]

大家好，我在维护的一个论坛上遇到了一个奇怪的问题。

在升级过程中，无论是在 admin/upgrade 页面还是终端中，操作都失败了，并出现以下错误：

--------------------------------------------------------------------------------
1 次迁移失败！

默认迁移失败
#<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.

---

幸运的是，我没有搞坏论坛，通过运行 ./launcher restart app 命令（./launcher destroy app / ./launcher start app 曾在我遇到重启后空白页时救过我一回），所以完全没有紧急情况，这也是我想寻求指导的原因。

我决定检查数据库，发现有一些指向 IP 10.0.0.0/8 的引用，看起来像是某些日志，但在 screened_ip_addresses 表中，我没有发现任何重复项。

--
-- TOC 条目 6829 (class 0 OID 382198)
-- 依赖项：657
-- screened_ip_addresses 的数据；类型：TABLE DATA；模式：public；所有者：-
--

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

论坛管理员告诉我，他在 /admin/logs/screened_ip_addresses 页面清理了一些 IP 并添加了一些新 IP。但我怀疑他并没有动过这个 IP。奇怪的是，系统显示该 IP 是在 14 天前添加的（那是上次升级的时间，很可能是 PostgreSQL 升级），尽管该论坛创建于 2015 年。而在其他论坛上，检查的是创建日期。

所以我想，这张表可能有点混乱，但看起来又不太像。

我不太想冒险尝试，尤其是因为我不擅长 SQL。因此，我想请教一下正确的操作流程，以确保能够安全升级：

我是否应该清理页面 /admin/logs/screened_ip_addresses，尝试清除该表或其他表的条目？

[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