Page MenuHomePhabricator

1.34.4 to 1.35.0 update.php db error on PostgreSQL
Closed, ResolvedPublic

Description

Hello,

While running update.php (after migrating files) I encounter this error:

Adding index redirect_pkey to table redirect ...
An error occurred:
Error 23505: ERROR:  could not create unique index "redirect_pkey"
DETAIL:  Key (rd_from)=(26) is duplicated.

Function: Wikimedia\Rdbms\Database::sourceFile( /home/canutethegreat/public_html/wiki/maintenance/postgres/archives/patch-redirect-pk.sql )
Query: ALTER TABLE redirect ADD PRIMARY KEY (rd_from)

Commenting out [ 'addIndex', 'redirect', 'redirect_pkey', 'patch-redirect-pk.sql' ], (line 667) in includes/installer/PostgresUpdater.php allows the update to finish without additional errors.

Here is the info for that table:

postgres@seafox:~$ pg_dump -st mediawiki.redirect ctgwiki                                                                                                     
--                                     
-- PostgreSQL database dump                                                                                                                                   
--                                     

-- Dumped from database version 12.4 (Ubuntu 12.4-1.pgdg20.04+1)               
-- Dumped by pg_dump version 12.4 (Ubuntu 12.4-1.pgdg20.04+1)                                                                                                 
                                       
SET statement_timeout = 0;             
SET lock_timeout = 0;              
SET idle_in_transaction_session_timeout = 0;                                                                                                                  
SET client_encoding = 'UTF8';          
SET standard_conforming_strings = on;  
SELECT pg_catalog.set_config('search_path', '', false);                        
SET check_function_bodies = false;  
SET xmloption = content;
SET client_min_messages = warning;     
SET row_security = off;
                                       
SET default_tablespace = '';
                                                                                                                                                              
SET default_table_access_method = heap;                                        

--           
-- Name: redirect; Type: TABLE; Schema: mediawiki; Owner: ctgwiki              
--               
                                                                               
CREATE TABLE mediawiki.redirect (                                              
    rd_from integer NOT NULL,                                                  
    rd_namespace smallint NOT NULL,
    rd_title text NOT NULL,                                                                                                                                   
    rd_interwiki text,                                                         
    rd_fragment text                                                           
);                                                                             
                                                                               
                                                                               
ALTER TABLE mediawiki.redirect OWNER TO ctgwiki;        
                                       
--                                     
-- Name: redirect_ns_title; Type: INDEX; Schema: mediawiki; Owner: ctgwiki     
--                                                                                                                                                            
                                       
CREATE INDEX redirect_ns_title ON mediawiki.redirect USING btree (rd_namespace, rd_title, rd_from);                                                           
                                       

--                                                                             
-- Name: redirect redirect_rd_from_fkey; Type: FK CONSTRAINT; Schema: mediawiki; Owner: ctgwiki                                                               
--                                     
                                       
ALTER TABLE ONLY mediawiki.redirect
    ADD CONSTRAINT redirect_rd_from_fkey FOREIGN KEY (rd_from) REFERENCES mediawiki.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;            
                                       
                                       
--                                                                             
-- PostgreSQL database dump complete
--

Please let me know if any additional information is needed. Thank you!

Event Timeline

Reedy added a subscriber: Reedy.

Added in rMW9d67de01cb37: Postgres: Add PK to `redirect` table... Committer and Reviewer aren't part of Platform Engineering anymore... But tagging the team workboard anyway for their input...

I'm guessing the indexExists( 'redirect_pkey' ) call is returning false, because the index is maybe implicit/named differently... But fails on creation, because it exists/is being duplicated by trying to add an explicit PK

BPirkle triaged this task as Lowest priority.Oct 6 2020, 9:03 PM
BPirkle moved this task from Inbox to Later on the Platform Team Workboards (Clinic Duty Team) board.
CanuteTheGreat1 claimed this task.

It turns out that somehow duplicate records were created. With help from a DBA (thanks Justin!) we tracked it down to 3 duplicate records. Deleting the duplicates resolved the issue completely. The only remaining question is how the duplicates got created in the first place...?