Page MenuHomePhabricator

migrateActors.php in Mediawiki 1.35 SQL statement error
Closed, ResolvedPublicBUG REPORT

Description

Migrating from mediawiki 1.27 to 1.35 running on Debian 10 Buster, PHP 7.3.19, PostgreSQL 9.4. After hitting "php update.php" the script will fail on "migrateActors.php" with following error:

Creating actor entries for all registered users

... 1 - 101
Wikimedia\Rdbms\DBQueryError from line 1699 of /var/www/wiki/includes/libs/rdbms/database/Database.php: Error 42601: ERROR:  INSERT has more target columns than expressions
LINE 1: ...eActors::doDBUpdates  */ INTO "actor" (actor_user,actor_name...
                                                         ^
HINT:  The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?

Function: MigrateActors::doDBUpdates
Query: INSERT INTO "actor" (actor_user,actor_name) VALUES (('1','Admin'))

#0 /var/www/wiki/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException('ERROR:  INSERT ...', '42601', 'INSERT INTO "ac...', 'MigrateActors::...')
#1 /var/www/wiki/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('ERROR:  INSERT ...', '42601', 'INSERT INTO "ac...', 'MigrateActors::...')
#2 /var/www/wiki/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError('ERROR:  INSERT ...', '42601', 'INSERT INTO "ac...', 'MigrateActors::...', false)
#3 /var/www/wiki/includes/libs/rdbms/database/DatabasePostgres.php(578): Wikimedia\Rdbms\Database->query('INSERT INTO "ac...', 'MigrateActors::...', 128)
#4 /var/www/wiki/includes/libs/rdbms/database/Database.php(2321): Wikimedia\Rdbms\DatabasePostgres->doInsertNonConflicting('actor', Array, 'MigrateActors::...')
#5 /var/www/wiki/includes/libs/rdbms/database/Database.php(3566): Wikimedia\Rdbms\Database->insert('actor', Array, 'MigrateActors::...', Array)
#6 /var/www/wiki/includes/libs/rdbms/database/DatabasePostgres.php(656): Wikimedia\Rdbms\Database->doInsertSelectGeneric('actor', 'user', Array, Array, 'MigrateActors::...', Array, Array, Array)
#7 /var/www/wiki/includes/libs/rdbms/database/Database.php(3477): Wikimedia\Rdbms\DatabasePostgres->doInsertSelectNative('actor', 'user', Array, Array, 'MigrateActors::...', Array, Array, Array)
#8 /var/www/wiki/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->insertSelect('actor', 'user', Array, Array, 'MigrateActors::...', Array, Array)
#9 /var/www/wiki/includes/libs/rdbms/database/DBConnRef.php(527): Wikimedia\Rdbms\DBConnRef->__call('insertSelect', Array)
#10 /var/www/wiki/maintenance/includes/MigrateActors.php(78): Wikimedia\Rdbms\DBConnRef->insertSelect('actor', 'user', Array, Array, 'MigrateActors::...', Array, Array)
#11 /var/www/wiki/maintenance/includes/LoggedUpdateMaintenance.php(45): MigrateActors->doDBUpdates()
#12 /var/www/wiki/maintenance/doMaintenance.php(107): LoggedUpdateMaintenance->execute()
#13 /var/www/wiki/maintenance/migrateActors.php(27): require_once('/var/www/wiki/m...')
#14 {main}

After searching on the Internet I found a discussion on the same error on postgresql forum. The advice was to remove the extra bracket at the end of the statement (outside the VALUES. So the statement should look this:

Query: INSERT INTO "actor" (actor_user,actor_name) VALUES ('1','Admin')

Event Timeline

If the query were for batches of rows it would succeed, for instance this should;

INSERT INTO actor (actor_user,actor_name) VALUES ( ('1', 'Admin'), ('2, 'Admin2')  );

It would do too if the extra parentheses are removed.

But for single row, it cannot be enclosed in another pair of parentheses. (('1','Admin')).

For Postgres < 9.5, where ON CONFLICT clause is not supported, the rows are updated serially so as to manually ignore the duplicate key error if it comes up and each row values should be enclosed by a single pair of parentheses.

Change 648534 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] dbPostgres: Remove outer parentheses in insert query

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

Hello. I already tested the patch (remove bratches at line 574) and the migration script completed successfully and wiki is running! Thank you for your help.

Change 649388 had a related patch set uploaded (by Reedy; owner: Ammarpad):
[mediawiki/core@REL1_35] rdbms: Remove outer parentheses in insert query for Postgres

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

Change 649388 merged by jenkins-bot:
[mediawiki/core@REL1_35] rdbms: Remove outer parentheses in insert query for Postgres

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

Change 648534 merged by jenkins-bot:
[mediawiki/core@master] rdbms: Remove outer parentheses in insert query for Postgres

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

Legoktm assigned this task to Ammarpad.

Thank you!