Page MenuHomePhabricator

Database::update() with IGNORE option fails on PostgreSQL (MW-1.32 migration error)
Closed, ResolvedPublic

Description

Prerequisites

I'm trying to upgrade from mw-1.23 to mw-1.32

PHP version:

PHP 7.1.26 (cli) (built: Jan  9 2019 08:02:33) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2018 Zend Technologies

Postgres version:

postgres (PostgreSQL) 9.2.24

Error

When running the maintenance/update.php script, the update fails because of an SQL error.

The subscript is maintenance/populateExternallinksIndex60.php

Error log:

<...SNIP...>
Populating el_index_60 field, printing progress markers. For large
databases, you may want to hit Ctrl-C and do this manually with
maintenance/populateExternallinksIndex60.php.
Populating externallinks.el_index_60...
el_id 0 - 200 of 1289
[7dd7e1c7d4c20fb84a749381] [no req]   Wikimedia\Rdbms\DBQueryError from line 1496 of /srv/wiki/instances/ict/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: UPDATE IGNORE "externallinks" SET el_index_60 = 'https://org.wikipedia.en./wiki/GPU_switching' WHERE el_id = '10'
Function: PopulateExternallinksIndex60::doDBUpdates
Error: 42P01 ERROR:  relation "ignore" does not exist
LINE 1: ...ernallinksIndex60::doDBUpdates postgres@cp0577 */ IGNORE "ex...
                                                             ^

Backtrace:
#0 /srv/wiki/instances/ict/includes/libs/rdbms/database/Database.php(1466): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /srv/wiki/instances/ict/includes/libs/rdbms/database/Database.php(1226): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /srv/wiki/instances/ict/includes/libs/rdbms/database/Database.php(2112): Wikimedia\Rdbms\Database->query(string, string)
#3 /srv/wiki/instances/ict/maintenance/populateExternallinksIndex60.php(76): Wikimedia\Rdbms\Database->update(string, array, array, string, array)
#4 /srv/wiki/instances/ict/maintenance/Maintenance.php(1698): PopulateExternallinksIndex60->doDBUpdates()
#5 /srv/wiki/instances/ict/includes/installer/DatabaseUpdater.php(1374): LoggedUpdateMaintenance->execute()
#6 /srv/wiki/instances/ict/includes/installer/DatabaseUpdater.php(485): DatabaseUpdater->populateExternallinksIndex60()
#7 /srv/wiki/instances/ict/includes/installer/DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean)
#8 /srv/wiki/instances/ict/maintenance/update.php(203): DatabaseUpdater->doUpdates(array)
#9 /srv/wiki/instances/ict/maintenance/doMaintenance.php(94): UpdateMediaWiki->execute()
#10 /srv/wiki/instances/ict/maintenance/update.php(248): require_once(string)
#11 {main}

I believe UPDATE IGNORE is MySQL specific and not allowed in PostgreSQL.

Event Timeline

@9st If you need a quick fix because this is causing issues on your infrastructure, removing [ 'IGNORE' ] from line 76 of maintenance/populateExternallinksIndex60.php should make it work (not sure why IGNORE was needed).

The right fix is to ignore the IGNORE modifier if running postgres.

There is support for IGNORE option on INSERT: https://phabricator.wikimedia.org/source/mediawiki/browse/master/includes/libs/rdbms/database/DatabasePostgres.php$577 but neither Postgres (ON CONFLICT) nor Mediawiki support it on UPDATE, so it needs implementation... not a 1-line trivial patch.

Thanks @jcrespo for the fast reply

@9st If you need a quick fix because this is causing issues on your infrastructure, removing [ 'IGNORE' ] from line 76 of maintenance/populateExternallinksIndex60.php should make it work (not sure why IGNORE was needed).

I tried your fix but I still get the same error. Am I doing something wrong?

diff populateExternallinksIndex60.php.orig populateExternallinksIndex60.php
76c76
< 					], __METHOD__ , [ 'IGNORE' ]
---
> 					], __METHOD__
update.php ...
<...SNIP...>
Populating el_index_60 field, printing progress markers. For large
databases, you may want to hit Ctrl-C and do this manually with
maintenance/populateExternallinksIndex60.php.
Populating externallinks.el_index_60...
el_id 0 - 200 of 1289
[a7a832757f54266c2ce572cc] [no req]   Wikimedia\Rdbms\DBQueryError from line 1496 of /srv/wiki/instances/ict.mw132/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: UPDATE IGNORE "externallinks" SET el_index_60 = 'https://org.wikipedia.en./wiki/GPU_switching' WHERE el_id = '10'
Function: PopulateExternallinksIndex60::doDBUpdates
Error: 42P01 ERROR:  relation "ignore" does not exist
LINE 1: ...ernallinksIndex60::doDBUpdates postgres@cp0577 */ IGNORE "ex...
                                                             ^


Backtrace:
#0 /srv/wiki/instances/ict.mw132/includes/libs/rdbms/database/Database.php(1466): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /srv/wiki/instances/ict.mw132/includes/libs/rdbms/database/Database.php(1226): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /srv/wiki/instances/ict.mw132/includes/libs/rdbms/database/Database.php(2112): Wikimedia\Rdbms\Database->query(string, string)
#3 /srv/wiki/instances/ict.mw132/maintenance/populateExternallinksIndex60.php(76): Wikimedia\Rdbms\Database->update(string, array, array, string, array)
#4 /srv/wiki/instances/ict.mw132/maintenance/Maintenance.php(1698): PopulateExternallinksIndex60->doDBUpdates()
#5 /srv/wiki/instances/ict.mw132/includes/installer/DatabaseUpdater.php(1374): LoggedUpdateMaintenance->execute()
#6 /srv/wiki/instances/ict.mw132/includes/installer/DatabaseUpdater.php(485): DatabaseUpdater->populateExternallinksIndex60()
#7 /srv/wiki/instances/ict.mw132/includes/installer/DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean)
#8 /srv/wiki/instances/ict.mw132/maintenance/update.php(203): DatabaseUpdater->doUpdates(array)
#9 /srv/wiki/instances/ict.mw132/maintenance/doMaintenance.php(94): UpdateMediaWiki->execute()
#10 /srv/wiki/instances/ict.mw132/maintenance/update.php(248): require_once(string)
#11 {main}

Kr.

@9st that is very strange, it is running IGNORE despite you removing it, plus: $options = [] ) { on the function signature. Unless I am very wrong, it could be a case of having cache code issues on your side or the .orig being executed somehow. While the above is not a solution, it should be good enough as a workaround.

@jcrespo You are right. The wrong script was being executed (caused by symlinking and relative paths etc.).
Running the script from the correct location succeeded with your workaround.

Many thanks for the support!

Kr
A.

Krinkle renamed this task from Update MW-1.32 PostgreSQL Migration Fails to Database::update() with IGNORE option fails on PostgreSQL (MW-1.32 migration error).Feb 9 2019, 11:33 PM

@jcrespo thanks for this but when I made the change suggested to line 76 and ran, I got the following error:

Setting sequence category_cat_id_seq owner to category.cat_id
Setting sequence change_tag_ct_id_seq owner to change_tag.ct_id
Setting sequence tag_summary_ts_id_seq owner to tag_summary.ts_id
Setting sequence sites_site_id_seq owner to sites.site_id
...change_tag_def table already exists.
Populating el_index_60 field, printing progress markers. For large
databases, you may want to hit Ctrl-C and do this manually with
maintenance/populateExternallinksIndex60.php.
[68ec385d77da4eab0318354c] [no req]   ParseError from line 77 of /usr/local/www/mediawiki/maintenance/populateExternallinksIndex60.php: syntax error, unexpected ')'
Backtrace:
#0 [internal function]: AutoLoader::autoload(string)
#1 [internal function]: spl_autoload_call(string)
#2 /usr/local/www/mediawiki/maintenance/Maintenance.php(690): class_exists(string)
#3 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(1373): Maintenance->runChild(string, string)
#4 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(485): DatabaseUpdater->populateExternallinksIndex60()
#5 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean)
#6 /usr/local/www/mediawiki/maintenance/update.php(203): DatabaseUpdater->doUpdates(array)
#7 /usr/local/www/mediawiki/maintenance/doMaintenance.php(94): UpdateMediaWiki->execute()
#8 /usr/local/www/mediawiki/maintenance/update.php(248): require_once(string)
#9 {main}

the code now reads:
71:

                        [
                                'el_index_60' => substr( $row->el_index, 0, 60 ),
                        ],
                        [
                                'el_id' => $row->el_id,
                        ], __METHOD__,
                );
        }
        wfWaitForSlaves();
        $start = $end;
}

Whatever change you did, it is not syntactically correct, I cannot help more than that:

ParseError from line 77 of /usr/local/www/mediawiki/maintenance/populateExternallinksIndex60.php

@jcrespo

Thank you for the quick response. The trailing comma was at fault. I edited that out and ran into a different error and in my uncertainty, returned it and tried it again. My bad. The diff as provided by @9st is the correct syntax. However, after running php update I got the error that incorrectly convinced me the comma might be needed:

Setting sequence sites_site_id_seq owner to sites.site_id
...change_tag_def table already exists.
Populating el_index_60 field, printing progress markers. For large
databases, you may want to hit Ctrl-C and do this manually with
maintenance/populateExternallinksIndex60.php.
Populating externallinks.el_index_60...
el_id 0 - 200 of 456
[6473cd9ab5af0f5b017a6e09] [no req]   Wikimedia\Rdbms\DBQueryError from line 1496 of /usr/local/www/mediawiki/includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: UPDATE  "externallinks" SET el_index_60 = 'http://localhost./awstats/awstats.pl?config=$site\n' WHERE el_id = '82'
Function: PopulateExternallinksIndex60::doDBUpdates
Error: 22P02 ERROR:  invalid input syntax for type bytea
LINE 1: ...root@kyonyu */  "externallinks" SET el_index_60 = 'http://lo...
                                                             ^


Backtrace:
#0 /usr/local/www/mediawiki/includes/libs/rdbms/database/Database.php(1466): Wikimedia\Rdbms\Database->makeQueryException(string, string, string, string)
#1 /usr/local/www/mediawiki/includes/libs/rdbms/database/Database.php(1226): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#2 /usr/local/www/mediawiki/includes/libs/rdbms/database/Database.php(2112): Wikimedia\Rdbms\Database->query(string, string)
#3 /usr/local/www/mediawiki/maintenance/populateExternallinksIndex60.php(76): Wikimedia\Rdbms\Database->update(string, array, array, string)
#4 /usr/local/www/mediawiki/maintenance/Maintenance.php(1698): PopulateExternallinksIndex60->doDBUpdates()
#5 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(1374): LoggedUpdateMaintenance->execute()
#6 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(485): DatabaseUpdater->populateExternallinksIndex60()
#7 /usr/local/www/mediawiki/includes/installer/DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean)
#8 /usr/local/www/mediawiki/maintenance/update.php(203): DatabaseUpdater->doUpdates(array)
#9 /usr/local/www/mediawiki/maintenance/doMaintenance.php(94): UpdateMediaWiki->execute()
#10 /usr/local/www/mediawiki/maintenance/update.php(248): require_once(string)
#11 {main}

Change 491778 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@master] DatabasePostgres: Ignore "IGNORE" option to update()

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

Query: UPDATE  "externallinks" SET el_index_60 = 'http://localhost./awstats/awstats.pl?config=$site\n' WHERE el_id = '82'

Why does your externallinks table have a row with an unescaped backslash in it?

The text versus binary field handling in MediaWiki isn't very well-defined since MySQL doesn't care, but in this case there shouldn't be a backslash to cause the problem in the first place.

It appears awstats uses backslashes as delimiters? I'm not entirely sure.

http://localhost/awstats/awstats.pl?config=$site\n

there's also a http://localhost/awstats/awstats.pl?config=demo\n in externallinks.

I"m not using awstats and could delete the references.

I think I was unclear. The URL should have the backslash encoded when MediaWiki inserted it into the externallinks table, like http://localhost/awstats/awstats.pl?config=$site%5Cn, even if it's in the wikitext with a literal backslash.

If you find the page with that link and null-edit it, that might fix it.

@Anomie,

Yes, thank you, editing the two awstats external links in the db to remove the forward slashes (and the previous UPDATE fix) permitted a successful update to 1.32. :)

Curiously, those forward slashes would have been in the DB since 2011-12-05, at 13:25:04. They are in the file awstats.configure.pl (at least the 2011 version) which was inserted post-config into the wiki between <pre> tags. They appear to be newline declarations leftover from a dos->unix conversion.

There are a lot of forward slashes, but those two entries were interpreted as URLs and added to the URL table as in

#print "\n-----> End of configuration\n";
print "\n\n";
if ($site) {
        print "A SIMPLE config file has been created: $configfile\n";
        print "You should have a look inside to check and change manually main parameters.\n";
        print "You can then manually update your statistics for '$site' with command:\n";
        print "> perl awstats.pl -update -config=$site\n";
        if (scalar keys %ApacheConfPath) {
                print "You can also read your statistics for '$site' with URL:\n";
                print "> http://localhost/awstats/awstats.pl?config=$site\n";
        }
        else {
                print "You can also build static report pages for '$site' with command:\n";
                print "> perl awstats.pl -output=pagetype -config=$site\n";
        }
        print "\n";
}
else {
        print "No config file was built. You can run this tool later to build as\n";
        print "much config/profile files as you want.\n";
        print "Once you have a config/profile file, for example 'awstats.demo.conf',\n";
        print "You can manually update your statistics for 'demo' with command:\n";
        print "> perl awstats.pl -update -config=demo\n";
        if (scalar keys %ApacheConfPath) {
                print "You can also read your statistics for 'demo' with URL:\n";
                print "> http://localhost/awstats/awstats.pl?config=demo";
        }
        else {
                print "You can also build static report pages for 'demo' with command:\n";
                print "> perl awstats.pl -output=pagetype -config=demo\n";
        }
        print "\n";
}

the two URLs that are printed were terminated as shown, they have since been edited to omit the newline.

I'd think there might be some (infrequently needed) utility in sanitizing the URL conversions for postgreSQL.

Curiously, those forward slashes would have been in the DB since 2011-12-05, at 13:25:04.

Probably then it's just that MediaWiki's code changed since 2011 and that page was never edited or had its links updated since.

Oh true, but it was only with the 1.30->1.32 update that it got flagged. There have been quite a few updates since 2011. For me it is happily solved and the wiki back.

Change 491778 merged by jenkins-bot:
[mediawiki/core@master] DatabasePostgres: Ignore "IGNORE" option to update()

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

Change 493434 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@REL1_32] DatabasePostgres: Ignore "IGNORE" option to update()

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

Change 493438 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@REL1_31] DatabasePostgres: Ignore "IGNORE" option to update()

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

Change 493434 merged by jenkins-bot:
[mediawiki/core@REL1_32] DatabasePostgres: Ignore "IGNORE" option to update()

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

Change 493438 merged by jenkins-bot:
[mediawiki/core@REL1_31] DatabasePostgres: Ignore "IGNORE" option to update()

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

Anomie claimed this task.