Page MenuHomePhabricator

1.28-alpha / Error: 42P07 ERROR: relation "logging_log_id_seq" already exists
Closed, ResolvedPublic

Description

After T148332 and its alleged fix [0], the installation of postgres is still broken[1]. It should be clear that this is a regression compared to installations like 1.26+ [2].

MediaWiki 1.28.0-alpha Updater
Your composer.lock file is up to date with current dependencies!
Going to run database updates for its_a_mw
Depending on the size of your database this may take a while!
...skipping: 'mwuser' table doesn't exist yet.
...skipping: 'mwuser' table doesn't exist yet.
...skipping: 'pagecontent' table doesn't exist yet.
Creating sequence logging_log_id_seq
[e3e26792c5a8f9d2d49f7c14] [no req]   DBQueryError from line 1054 of /home/travis/build/SemanticMediaWiki/mw/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: CREATE SEQUENCE logging_log_id_seq
Function: Database::query
Error: 42P07 ERROR:  relation "logging_log_id_seq" already exists
Backtrace:
#0 /home/travis/build/SemanticMediaWiki/mw/includes/libs/rdbms/database/DatabasePostgres.php(247): Database->reportQueryError(string, string, string, string, boolean)
#1 /home/travis/build/SemanticMediaWiki/mw/includes/libs/rdbms/database/Database.php(912): DatabasePostgres->reportQueryError(string, string, string, string, boolean)
#2 /home/travis/build/SemanticMediaWiki/mw/includes/installer/PostgresUpdater.php(613): Database->query(string)
#3 [internal function]: PostgresUpdater->addSequence(string, boolean, string)
#4 /home/travis/build/SemanticMediaWiki/mw/includes/installer/DatabaseUpdater.php(473): call_user_func_array(array, array)
#5 /home/travis/build/SemanticMediaWiki/mw/includes/installer/DatabaseUpdater.php(434): DatabaseUpdater->runUpdates(array, boolean)
#6 /home/travis/build/SemanticMediaWiki/mw/maintenance/update.php(171): DatabaseUpdater->doUpdates(array)
#7 /home/travis/build/SemanticMediaWiki/mw/maintenance/doMaintenance.php(111): UpdateMediaWiki->execute()
#8 /home/travis/build/SemanticMediaWiki/mw/maintenance/update.php(216): require_once(string)
#9 {main}

[0] https://gerrit.wikimedia.org/r/316399
[1] https://travis-ci.org/SemanticMediaWiki/SemanticMediaWiki/jobs/168867830
[2] https://travis-ci.org/SemanticMediaWiki/SemanticMediaWiki/jobs/168056742

Event Timeline

mwjames created this task.Oct 19 2016, 11:17 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 19 2016, 11:17 AM

Hi, https://gerrit.wikimedia.org/r/#/c/316399/ fixes the problem and now we see the new error.

Related task T147599

Paladox added a subscriber: aaron.Oct 22 2016, 6:36 PM

adding @aaron since he is working on db.

It doesn't just affect sequence, it affects everything in postgres, it carn't detect that tables, columns, sequences, and other things exist, so it causes it to fail instead of saying that these already exist so skipping this table creation like it does in MySQL.

Something may have broken the query function in db for postgres or this rMW7197f32c82b468d83da0aed239ba44d5435816c3 is the real cause?

aaron added a comment.EditedOct 24 2016, 4:52 AM

Looks like the existence methods query for schema.sequencename as 'relname' but the actual 'relname' values are just sequencename. It then tries to create the SEQUENCE with sequencename, which is already there. Using PGAdmin is useful to graphically probe around.

aaron added a comment.Oct 24 2016, 4:57 AM

It seems odd that addSequence() uses sequenceExists(), which prefixes $ns like a table, but then addSequence() uses a raw query using $ns with no prefix. I'd have to figure out what the logic was supposed to be previously...

Oh but this also happened to the table when it is checking for it to exist it will fail because it is trying to create a duplicate table. So maybe the query is broken for postgres?

This was all working before September 1 2016.

Paladox triaged this task as High priority.Oct 24 2016, 12:27 PM

Changing to high.

@aaron it has always used sequenceExists, it seems that all broke after September 1, another patch after that date may have broken it even further, but it affects all of update.php for postgres not just sequence.

Just saw [0] (1.28 branching release date), I do have high hopes that this will be fixed before cutting the 1.28 branch since Postgres is just broken right now and I'd like to have a chance to actually install Postgres before 1.28 is released and have our integration tests to verify that nothing else got lost during recent refactorings.

[0] https://lists.wikimedia.org/pipermail/wikitech-l/2016-October/086859.html

Change 317863 had a related patch set uploaded (by Aaron Schulz):
Postgres updater fixes

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

Change 317863 merged by jenkins-bot:
Postgres updater fixes to make update.php able to run

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

Change 318661 had a related patch set uploaded (by Paladox):
Postgres updater fixes to make update.php able to run

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

Change 318661 merged by jenkins-bot:
Postgres updater fixes to make update.php able to run

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

aaron added a comment.Oct 29 2016, 4:46 AM

As of master, I can run update.php just fine. I'm not seeing any errors.

mwjames added a comment.EditedOct 29 2016, 10:54 AM

As of master, I can run update.php just fine. I'm not seeing any errors.

Yes, the update.php goes through as seen in [0] but I'm still not confident that we are missing something since the tests are stuck. T149454 touches the issue and when trying to run MW's test suite you'll be shown:

$ php tests/phpunit/phpunit.php -c tests/phpunit/suite.xml
Using PHP 5.6.8
PHPUnit 4.8.24 by Sebastian Bergmann and contributors.

[65b1bd9553b39c41de640921] [no req]   DBQueryError from line 1054 of ...\mw-core-pg\includes\libs\rdbms\database\Data
base.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?

Query: SELECT  ug_group  FROM "unittest_user_groups"    WHERE ug_user = '2'
Function: User::loadGroups
Error: 42P01 ERROR:  relation "unittest_user_groups" does not exist
LINE 1: SELECT /* User::loadGroups  */  ug_group  FROM "unittest_use...
                                                       ^


Backtrace:
#0 ...\mw-core-pg\includes\libs\rdbms\database\DatabasePostgres.php(249): Database->reportQueryError(string, string,
string, string, boolean)
#1 ...\mw-core-pg\includes\libs\rdbms\database\Database.php(912): DatabasePostgres->reportQueryError(string, string,
string, string, boolean)
#2 ...\mw-core-pg\includes\libs\rdbms\database\Database.php(1254): Database->query(string, string)
#3 ...\mw-core-pg\includes\user\User.php(1370): Database->select(string, array, array, string)
#4 ...\mw-core-pg\includes\user\User.php(482): User->loadGroups()
#5 ...\mw-core-pg\includes\libs\objectcache\WANObjectCache.php(884): User->{closure}(boolean, integer, array, NULL)
#6 [internal function]: WANObjectCache->{closure}(boolean, integer, array, NULL)
#7 ...\mw-core-pg\includes\libs\objectcache\WANObjectCache.php(1004): call_user_func_array(Closure, array)
#8 ...\mw-core-pg\includes\libs\objectcache\WANObjectCache.php(890): WANObjectCache->doGetWithSetCallback(string, int
eger, Closure, array, NULL)
#9 ...\mw-core-pg\includes\user\User.php(496): WANObjectCache->getWithSetCallback(string, integer, Closure, array)
#10 ...\mw-core-pg\includes\user\User.php(437): User->loadFromCache()
#11 ...\mw-core-pg\includes\user\User.php(401): User->loadFromId(integer)
#12 ...\mw-core-pg\includes\user\User.php(2648): User->load()
#13 ...\mw-core-pg\tests\phpunit\includes\TestUser.php(103): User->getEmail()
#14 ...\mw-core-pg\tests\phpunit\includes\TestUser.php(63): TestUser->setEmail(string)
#15 ...\mw-core-pg\tests\phpunit\includes\TestUserRegistry.php(88): TestUser->__construct(string, string, string, arr
ay, string)
#16 ...\mw-core-pg\tests\phpunit\MediaWikiTestCase.php(138): TestUserRegistry::getImmutableTestUser(array)
#17 ...\mw-core-pg\tests\phpunit\MediaWikiTestCase.php(162): MediaWikiTestCase::getTestUser(array)
#18 ...\mw-core-pg\tests\phpunit\MediaWikiTestCase.php(1021): MediaWikiTestCase::getTestSysop()
#19 ...\mw-core-pg\tests\phpunit\MediaWikiTestCase.php(374): MediaWikiTestCase->addCoreDBData()
#20 ...\mw-core-pg\vendor\phpunit\phpunit\src\Framework\TestSuite.php(747): MediaWikiTestCase->run(PHPUnit_Framework_
TestResult)
#21 ...\mw-core-pg\vendor\phpunit\phpunit\src\Framework\TestSuite.php(747): PHPUnit_Framework_TestSuite->run(PHPUnit_
Framework_TestResult)
#22 ...\mw-core-pg\vendor\phpunit\phpunit\src\Framework\TestSuite.php(747): PHPUnit_Framework_TestSuite->run(PHPUnit_
Framework_TestResult)
#23 ...\mw-core-pg\vendor\phpunit\phpunit\src\TextUI\TestRunner.php(440): PHPUnit_Framework_TestSuite->run(PHPUnit_Fr
amework_TestResult)
#24 ...\mw-core-pg\vendor\phpunit\phpunit\src\TextUI\Command.php(149): PHPUnit_TextUI_TestRunner->doRun(PHPUnit_Frame
work_TestSuite, array)
#25 ...\mw-core-pg\vendor\phpunit\phpunit\src\TextUI\Command.php(100): PHPUnit_TextUI_Command->run(array, boolean)
#26 ...\mw-core-pg\tests\phpunit\phpunit.php(133): PHPUnit_TextUI_Command::main()
#27 ...\mw-core-pg\maintenance\doMaintenance.php(111): PHPUnitMaintClass->execute()
#28 ...\mw-core-pg\tests\phpunit\phpunit.php(163): require(string)
#29 {main}

In comparison running on 1.26.3 works through with:

$ php tests/phpunit/phpunit.php -c tests/phpunit/suite.xml
PHPUnit already present
PHPUnit 4.8.27 by Sebastian Bergmann and contributors.

Runtime:        PHP 5.6.8
Configuration:  ...\mw-26-03-pq\tests\phpunit\suite.xml
Warning:        Deprecated configuration setting "strict" used

...................................................SSSSSS..    59 / 10798 (  0%)
...............SSSSSSSSSS..................................   118 / 10798 (  1%)
...........................................................   177 / 10798 (  1%)
..............SS...........................................   236 / 10798 (  2%)
...........................................................   295 / 10798 (  2%)
...........................................................   354 / 10798 (  3%)
...........................................................   413 / 10798 (  3%)
...........................................................   472 / 10798 (  4%)
...........................................................   531 / 10798 (  4%)
...........................................................   590 / 10798 (  5%)
...........................................................   649 / 10798 (  6%)
...........................................................   708 / 10798 (  6%)
...........................................................   767 / 10798 (  7%)
...........................................................   826 / 10798 (  7%)

...

FAILURES!
Tests: 10797, Assertions: 59673, Errors: 9, Failures: 24, Skipped: 171, Risky: 35.

So, something doesn't feel okayish here.

[0] https://travis-ci.org/SemanticMediaWiki/SemanticMediaWiki/jobs/171554824

Paladox added a comment.EditedOct 29 2016, 5:09 PM

@aaron there's T149454 which I think it's because we doint call tableName, see https://github.com/SemanticMediaWiki/SemanticMediaWiki/pull/1971

But not calling tableName in smw is the same for mw postgres, since it isen't adding the prefix like for example mediawiki_user_groups. Instead it is doing user_groups. So that sunittest_ prefix is not on sunittest_user_groups because the table hasen't been created.

So I think we need to overhaul the prefix system for postgres to fix this problem.

But when we do call tableName, it breaks. So something is wrong in tableName for Postgres.

https://travis-ci.org/paladox/SemanticMediaWiki/builds/171645543

I'm getting all but a few failures with https://gerrit.wikimedia.org/r/#/c/318692/ and https://gerrit.wikimedia.org/r/#/c/318693/1

If someone could merge the changes, I'll do another round of tests but I think I will still see issues on CloneDatabase.php(122): Database->dropTable(string). I did a quick hack in CloneDatabase on $this->db->dropTable( 'mediawiki.' . $tbl ); which showed better results.

PS: Our integration tests do the whole cycle create a page, add some content, compare the result, throw away the page, start a new page and so on ... and before we start the XML import tests we throw away all tables and start from scratch with a new set of clones.

Anyway, once the changes are merged I'll give it another shot.

@mwjames you could checkout master? And checkout the patches?

Anyway, once the changes are merged I'll give it another shot.

The original issue is fixed hence this task can be closed but of course as I mentioned earlier, tests keep hanging on:

Query: DROP TABLE "unittest_user_former_groups" CASCADE
Function: Database::dropTable
Error: 42P01 ERROR:  table "unittest_user_former_groups" does not exist


Backtrace:
#0 ...\mw-core-pg\includes\libs\rdbms\database\DatabasePostgres.php(239): Database->reportQueryError(string, string, string, string, boolean)
#1 ...\mw-core-pg\includes\libs\rdbms\database\Database.php(912): DatabasePostgres->reportQueryError(string, string, string, string, boolean)
#2 ...\mw-core-pg\includes\libs\rdbms\database\Database.php(3344): Database->query(string, string)
#3 ...\mw-core-pg\includes\db\CloneDatabase.php(122): Database->dropTable(string)

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

With the changes in [0] from @Paladox the tests got us a lot further [1].

FAILURES!
Tests: 4198, Assertions: 13328, Failures: 2, Errors: 6, Skipped: 17.

Are we sure that $this->tableExists( $tableName ) in Postgres returns the correct results?

[0] https://gerrit.wikimedia.org/r/#/c/318769/6/includes/libs/rdbms/database/Database.php

[1] https://travis-ci.org/SemanticMediaWiki/SemanticMediaWiki/jobs/171693394

I think tableExists is broken in Postgres.

@aaron and @mwjames I doint think mwschema is correctly being applied to postgres since should it be table mediawiki.user_groups and not user_groups.

tableName needs fixing to work in postgres.

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

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

demon closed this task as Resolved.Nov 23 2016, 10:28 PM
demon claimed this task.
demon added a subscriber: demon.

Anyway, once the changes are merged I'll give it another shot.

The original issue is fixed hence this task can be closed but of course as I mentioned earlier, tests keep hanging on:

Resolving--fixed in master and backported everywhere as best I can tell. The unit test issues are being tracked in T149454.

Ninjavz added a subscriber: Ninjavz.EditedJan 6 2017, 7:51 PM

Just to post the error of today. Seems not fixed yet. This is for MW1.28.0, and I also tried with MW1.27.1, migrating from 1.19.1 with PostgreSQL 9.1

# php update.php --quick
MediaWiki 1.28.0 Updater

Your composer.lock file is up to date with current dependencies!
Going to run database updates for wiki3
Depending on the size of your database this may take a while!
...index mwuser_pkey already set on mwuser table.
...index mwuser_user_name_key already set on mwuser table.
...index pagecontent_pkey already set on pagecontent table.
...sequence ipblocks_ipb_id_seq already exists.
...sequence revision_rev_id_seq already exists.
...sequence text_old_id_seq already exists.
...sequence recentchanges_rc_id_seq already exists.
...sequence logging_log_id_seq already exists.
...sequence page_restrictions_pr_id_seq already exists.
...sequence uploadstash_us_id_seq already exists.
...sequence category_cat_id_seq already exists.
Creating sequence archive_ar_id_seq
[bc936cb8bb9062ea3ec05ebd] [no req]   DBQueryError from line 1054 of /var/www/html/wiki1280/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: CREATE SEQUENCE archive_ar_id_seq
Function: Database::query
Error: 42P07 ERROR:  relation "archive_ar_id_seq" already exists


Backtrace:
#0 /var/www/html/wiki1280/includes/libs/rdbms/database/DatabasePostgres.php(249): Database->reportQueryError(string, string, string, string, boolean)
#1 /var/www/html/wiki1280/includes/libs/rdbms/database/Database.php(912): DatabasePostgres->reportQueryError(string, string, string, string, boolean)
#2 /var/www/html/wiki1280/includes/installer/PostgresUpdater.php(613): Database->query(string)
#3 [internal function]: PostgresUpdater->addSequence(string, boolean, string)
#4 /var/www/html/wiki1280/includes/installer/DatabaseUpdater.php(472): call_user_func_array(array, array)
#5 /var/www/html/wiki1280/includes/installer/DatabaseUpdater.php(433): DatabaseUpdater->runUpdates(array, boolean)
#6 /var/www/html/wiki1280/maintenance/update.php(172): DatabaseUpdater->doUpdates(array)
#7 /var/www/html/wiki1280/maintenance/doMaintenance.php(111): UpdateMediaWiki->execute()
#8 /var/www/html/wiki1280/maintenance/update.php(217): require_once(string)
#9 {main}

Hope this can be fixed sometime. Also, which is the last version of MW to work for upgrading to?
Finally, what if I create a new wiki for postgresql, and then move the data from the old one to the new one (structure)?

Regards,
Miguel