PostgreSQL install fails on INSERT IGNORE INTO "mediawiki"."updatelog" in update_keys.sql
Closed, ResolvedPublic

Description

This the PostgreSQL equivalent to bug 71040 for Oracle.


Version: 1.25-git
Severity: normal

Details

Reference
bz72834
bzimport set Reference to bz72834.
bzimport added a subscriber: Unknown Object (MLST).
Jjanes created this task.Oct 31 2014, 8:27 PM

Change 170395 had a related patch set uploaded by Jjanes:
PostgreSQL: Port update-keys.sql to PostgreSQL

https://gerrit.wikimedia.org/r/170395

saper added a comment.Nov 7 2014, 9:42 PM

What error message are you getting with PostgreSQL (Oracle needs "FROM DUAL" after "SELECT"...)

saper added a comment.Nov 7 2014, 10:06 PM

Ok, I managed to reproduce it finally:

Query: INSERT IGNORE INTO "mediawiki"."updatelog"
SELECT 'filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql' AS ul_key, null as ul_value
UNION SELECT 'image-img_major_mime-patch-img_major_mime-chemical.sql', null
UNION SELECT 'oldimage-oi_major_mime-patch-oi_major_mime-chemical.sql', null
UNION SELECT 'user_groups-ug_group-patch-ug_group-length-increase-255.sql', null
UNION SELECT 'user_former_groups-ufg_group-patch-ufg_group-length-increase-255.sql', null
UNION SELECT 'user_properties-up_property-patch-up_property.sql', null

Function: DatabaseBase::sourceFile( /usr/home/saper/public_html/pg/w/maintenance/update-keys.sql )
Error: 42601 ERROR: syntax error at or near "IGNORE"
LINE 1: ...l/pg/w/maintenance/update-keys.sql ) 127.0.0.1 */ IGNORE INT...

The IGNORE in "INSERT IGNORE" from the default version of the file (for MySQL and sqlite) is not valid syntax in PostgreSQL.

ERROR: syntax error at or near "IGNORE" at character 115
STATEMENT: INSERT /* DatabaseBase::sourceFile( /usr/local/apache2/htdocs/wiki_git/maintenance/update-keys.sql ) 127.0.0.1 */ IGNORE INTO "public"."updatelog"
...

The fix for the bug against Oracle claims the IGNORE is not needed because this file is only run against fresh installs so there cannot be primary key violations.

saper added a comment.Nov 7 2014, 10:27 PM

This bug was caused by change d315c3bdb34bce9327a494e9edf5fe3774b81d7b which added INSERT IGNORE.

So there are two issues:

  1. Missing "FROM tablename" for Oracle (we can add "FROM revision" just for sanity)
  1. Updating updatelog table by hand in a funny way.

We have already "LoggedUpdateMaintenance" to deal with those....

scfc added a comment.Nov 12 2014, 3:12 AM

Do we want to wait for the issues from Gerrit change #135756 to get fixed for all databases? I'd rather have Jeff's patch merged now as this bug is a blocker for PostgreSQL and leave the clean-up for later as that is a (minor) pain point for all databases.

  • Bug 73434 has been marked as a duplicate of this bug. ***
scfc raised the priority of this task from "Normal" to "Unbreak Now!".Nov 24 2014, 4:49 AM
demon removed a subscriber: demon.Nov 24 2014, 3:55 PM
Wmat removed a subscriber: Wmat.Nov 24 2014, 4:10 PM
Dereckson lowered the priority of this task from "Unbreak Now!" to "High".EditedNov 24 2014, 11:37 PM
Dereckson added a subscriber: Dereckson.

[Affects only one database engine, not the most common one. If not an immediate blocker, it's still a high priority task to fix this issue. ]

Change 170395 merged by jenkins-bot:
PostgreSQL: Port update-keys.sql to PostgreSQL

https://gerrit.wikimedia.org/r/170395

Change 176291 had a related patch set uploaded (by Legoktm):
PostgreSQL: Port update-keys.sql to PostgreSQL

https://gerrit.wikimedia.org/r/176291

Patch-For-Review

Change 176291 merged by jenkins-bot:
PostgreSQL: Port update-keys.sql to PostgreSQL

https://gerrit.wikimedia.org/r/176291

scfc assigned this task to Jjanes.Dec 3 2014, 8:04 PM

!close

scfc closed this task as "Resolved".Dec 3 2014, 8:05 PM
scfc set Security to None.

@saper, is there documentation or examples on how LoggedUpdateMaintenance is supposed to work? It has me completely baffled.

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.