Page MenuHomePhabricator

WebInstaller and DatabasePostgres throws Error: 23505 ERROR: duplicate key value violates unique constraint "updatelog_pkey"
Open, Needs TriagePublic

Description

Attempted to install Postgres with MW 1.30 alpha [0] on a repeated trial install throws:

[3e98eaabfd1a733f09ae78ae] /mw-master-pg/mw-config/index.php?page=Install Wikimedia\Rdbms\DBQueryError from line 1145 of ...\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: INSERT INTO "updatelog" (ul_key, ul_value)
VALUES( 'filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql', null )

Function: Wikimedia\Rdbms\Database::sourceFile( .../maintenance/postgres/update-keys.sql )
Error: 23505 ERROR: duplicate key value violates unique constraint "updatelog_pkey"
DETAIL: Key (ul_key)=(filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql) already exists.

Backtrace:

#0 ...\includes\libs\rdbms\database\DatabasePostgres.php(261): Wikimedia\Rdbms\Database->reportQueryError(string, string, string, string, boolean)
#1 ...\includes\libs\rdbms\database\Database.php(975): Wikimedia\Rdbms\DatabasePostgres->reportQueryError(string, string, string, string, boolean)
#2 ...\includes\libs\rdbms\database\Database.php(3226): Wikimedia\Rdbms\Database->query(string, string)
#3 ...\includes\libs\rdbms\database\Database.php(3174): Wikimedia\Rdbms\Database->sourceStream(unknown type, NULL, NULL, string, NULL)
#4 ...\includes\installer\DatabaseInstaller.php(198): Wikimedia\Rdbms\Database->sourceFile(string)
#5 ...\includes\installer\DatabaseInstaller.php(230): DatabaseInstaller->stepApplySourceFile(string, string, boolean)
#6 ...\includes\installer\Installer.php(1519): DatabaseInstaller->insertUpdateKeys(PostgresInstaller)
#7 ...\includes\installer\WebInstallerInstall.php(44): Installer->performInstallation(array, array)
#8 ...\includes\installer\WebInstaller.php(279): WebInstallerInstall->execute()
#9 ...\mw-config\index.php(81): WebInstaller->execute(array)
#10 ...\mw-config\index.php(39): wfInstallerMain()
#11 {main}

[0] https://github.com/wikimedia/mediawiki/commit/bf1d5b2ab367c9723300ec0fedaf565bb8eed8e5

Event Timeline

Could this https://gerrit.wikimedia.org/r/#/c/350562/ have caused the problem again?

No, its another problem (again) while I tried to actually confirm above fixes another issue.

I recently got this error when trying to install postgres using mediwiki-docker:

Wikimedia\Rdbms\DBQueryError from line 1534 of /var/www/html/w/includes/libs/rdbms/database/Database.php: Error 23505: ERROR:  duplicate key value violates unique constraint "updatelog_pkey"
DETAIL:  Key (ul_key)=(filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql) already exists.

Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/w/maintenance/postgres/update-keys.sql )
Query: INSERT INTO "updatelog" (ul_key, ul_value)
 VALUES( 'filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql', null )


#0 /var/www/html/w/includes/libs/rdbms/database/Database.php(1518): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/html/w/includes/libs/rdbms/database/Database.php(1492): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/html/w/includes/libs/rdbms/database/Database.php(865): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/html/w/includes/libs/rdbms/database/Database.php(3087): Wikimedia\Rdbms\Database->query()
#4 /var/www/html/w/includes/libs/rdbms/database/Database.php(3026): Wikimedia\Rdbms\Database->sourceStream()
#5 /var/www/html/w/includes/installer/DatabaseInstaller.php(234): Wikimedia\Rdbms\Database->sourceFile()
#6 /var/www/html/w/includes/installer/DatabaseInstaller.php(278): DatabaseInstaller->stepApplySourceFile()
#7 [internal function]: DatabaseInstaller->insertUpdateKeys()
#8 /var/www/html/w/includes/installer/Installer.php(1670): call_user_func()
#9 /var/www/html/w/includes/installer/CliInstaller.php(211): Installer->performInstallation()
#10 /var/www/html/w/maintenance/install.php(142): CliInstaller->execute()
#11 /var/www/html/w/maintenance/includes/MaintenanceRunner.php(568): CommandLineInstaller->execute()
#12 /var/www/html/w/maintenance/doMaintenance.php(99): MediaWiki\Maintenance\MaintenanceRunner->run()
#13 /var/www/html/w/maintenance/install.php(201): require_once('...')
#14 {main}
PHP Notice:  Uncommitted DB writes (transaction from DatabaseInstaller::stepApplySourceFile) in /var/www/html/w/includes/libs/rdbms/database/TransactionManager.php on line 586

The method to get around this for me was to blank maintenance/postgres/update-keys.sql.

Isn't that just because the scripts for mysql / mariadb and sql-lite use

INSERT (OR) IGNORE

while postgres does NOT use the equivalent

ON CONFLICT DO NOTHING`

? (see https://github.com/search?q=repo%3Awikimedia%2Fmediawiki%20filearchive-fa_major_mime-patch-fa_major_mime-chemical.sql&type=code)