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

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

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Postgres updater fixes to make update.php able to run	mediawiki/core	master	+54 -33
	Postgres updater fixes to make update.php able to run	mediawiki/core	REL1_28	+54 -33

Customize query in gerrit

Related Objects
Search...

View Standalone Graph

This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Status	Assigned	Task
		· · ·
Invalid	None	T2384 [REPLACED BY TAG] PostgreSQL/pgsql support (tracking)
Resolved	• demon	T148628 1.28-alpha / Error: 42P07 ERROR: relation "logging_log_id_seq" already exists
		· · ·

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 projects: Regression, MW-1.28-release.Oct 19 2016, 11:54 AM

Aklapper added a parent task: T2384: [REPLACED BY TAG] PostgreSQL/pgsql support (tracking).Oct 19 2016, 12:57 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?

Adding @saper since he maintains Postgres

Still present as of rMW590aa0fb7f70d59974af8ccf9f3fea4997c647ea

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.

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.

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

gerritbot added a project: Patch-For-Review.Oct 25 2016, 7:02 PM

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

mwjames mentioned this in T149468: 1.28-alpha / Uncommitted DB writes (transaction from FOR SAVEPOINT).Oct 29 2016, 1:41 AM

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

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

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

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

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

@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

@aaron maybe this rMW13384d9988dd2a484d4650a4939f2817951735a8 broke things?

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.

ReleaseTaggerBot added projects: MW-1.29-release (WMF-deploy-2016-11-08_(1.29.0-wmf.2)), MW-1.28-release-notes, MW-1.29-release-notes.Nov 2 2016, 5:00 PM

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

mwjames mentioned this in T147683: I984b80815633a8 introduces breaking change without deprecation.Nov 10 2016, 4:08 AM

In T148628#2754388, @mwjames wrote:

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.

mwjames mentioned this in T149454: 1.28-alpha / Error: 42P01 ERROR: table "unittest_user_groups" does not exist.Nov 25 2016, 10:17 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

I am seeing this error upgrading from 1.27 LTS to 1.31 LTS. PHP 7.2, PostgreSQL 9.6.14.

Solved: In my case, several sequences, functions, and a type where in schema "mediawiki" while the rest was in schema "public". I moved everything to "public" and the update script worked perfectly. Example commands:

ALTER SEQUENCE uploadstash_us_id_seq SET SCHEMA public;
ALTER FUNCTION ts2_page_title() SET SCHEMA public;
ALTER FUNCTION add_interwiki(text, integer, smallint) SET SCHEMA public;
ALTER TYPE media_type SET SCHEMA public;

If this runs, everything is gone from that schema (it won't erase anything if it is still in that schema):
DROP SCHEMA mediawiki;

Upgrading from 1.27 LTS to 1.31 LTS. PHP 7.3, PostgreSQL 9.6.17.

Today I was unable to rename a page. It was trying to insert into the logging table but that failed because log_id is NOT NULL but no default was set. I suspect it was caused by the same thing as my comment above, the wrong schema making some updates fail. (I rarely move pages, so I wouldn't notice until now.) The fix was this:

-- Find the next value for log_id.
SELECT max(log_id) + 1 FROM logging;

-- Correctly set the next value of logging_log_id_seq. Replace "NNN" with the result of the last query.
ALTER SEQUENCE logging_log_id_seq RESTART NNN;

-- Make the sequence be the default value.
ALTER TABLE logging ALTER COLUMN log_id SET DEFAULT nextval('logging_log_id_seq');

Restricted Application added a project: Performance-Team. · View Herald TranscriptApr 16 2022, 4:25 PM

1.28-alpha / Error: 42P07 ERROR: relation "logging_log_id_seq" already existsClosed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

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

Related Objects
Search...