Page MenuHomePhabricator

Notifications ('Echo') should work with postgres
Closed, ResolvedPublic

Related Objects

Event Timeline

Of Notifications's two dependencies, CentralAuth is known-broken with postgres; EventLogging might also break, let's see.

MarcoAurelio renamed this task from Notifiations ('Echo') should work with postgres to Notifications ('Echo') should work with postgres.Feb 11 2020, 6:24 PM

Change 565045 had a related patch set uploaded (by Jforrester; owner: Jack Phoenix):
[mediawiki/extensions/Echo@master] PostgreSQL support

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

Reedy changed the task status from Open to Stalled.Jul 31 2020, 5:18 PM

Marking stalled due to subtask needing solving first (I don't know if the attached patch will be merged... Maybe it will be)

Change 621928 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/Echo@master] Convert Echo to use abstract schema

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

Change 565045 abandoned by Jack Phoenix:

[mediawiki/extensions/Echo@master] PostgreSQL support

Reason:

Imagine a popular extension supporting PGSQL out of the box. Hah!

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

Change 751783 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting rows

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

Change 751783 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting rows

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

hashar subscribed.

Change 751783 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting rows

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

This might be causing T304307 Wikimedia\Timestamp\ConvertibleTimestamp::getTimestamp: The timestamp cannot be represented in the specified format

And or T281451#7792422 Duplicate entry '17604325-1515299' for key 'PRIMARY' (db1157) INSERT INTO echo_notification

I have rolled back https://gerrit.wikimedia.org/r/751783 since it had an exception when rolled to the Wikimedia cluster ( T304307 ).

Change 773346 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting notification_read_timestamp

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

The database columns euw_alerts_ts and euw_messages_ts in table echo_unread_wikis are NOT NULL and using a default timestamp of 00000000000000, which works good with ourer mysql timestamp implementation, which is a binary(14). But for Postgresql the columns would get a real timestamp and a conversion is needed. When giving 00000000000000 as timestamp it is converted to negative timestamp (-00011130000000), because all the "out of bounds" numbers (month and day) are treated as the previous day/month (month 0 is month 12 of the previous year).

Testing with "now"

Change 773359 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting echo_unread_wikis

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

Change 773346 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting notification_read_timestamp

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

Change 773359 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] Call IDatabase::timestamp before inserting echo_unread_wikis

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

https://integration.wikimedia.org/ci/job/quibble-vendor-sqlite-php72-noselenium-docker/1480/console

12:11:35 Creating echo_unread_wikis table...Wikimedia\Rdbms\DBQueryError from line 1638 of /workspace/src/includes/libs/rdbms/database/Database.php: Error 1: unrecognized token: "#"
12:11:35 Function: Wikimedia\Rdbms\Database::sourceFile( /workspace/src/extensions/Echo/db_patches/echo_unread_wikis.sql )
12:11:35 Query: CREATE TABLE echo_unread_wikis (
12:11:35  # Primary key
12:11:35  euw_id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
12:11:35  # Global user id
12:11:35  euw_user INTEGER  NOT NULL,
12:11:35  # Name of wiki
12:11:35  euw_wiki TEXT NOT NULL,
12:11:35  # unread alerts count on that wiki
12:11:35  euw_alerts INTEGER  NOT NULL,
12:11:35  # TEXT of the most recent unread alert
12:11:35  euw_alerts_ts BLOB NOT NULL,
12:11:35  # unread messages count on that wiki
12:11:35  euw_messages INTEGER  NOT NULL,
12:11:35  # TEXT of the most recent unread message
12:11:35  euw_messages_ts BLOB NOT NULL
12:11:35  )
12:11:35 
12:11:35 #0 /workspace/src/includes/libs/rdbms/database/Database.php(1622): Wikimedia\Rdbms\Database->getQueryException('unrecognized to...', 1, 'CREATE TABLE ec...', 'Wikimedia\\Rdbms...')
12:11:35 #1 /workspace/src/includes/libs/rdbms/database/Database.php(1596): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('unrecognized to...', 1, 'CREATE TABLE ec...', 'Wikimedia\\Rdbms...')
12:11:35 #2 /workspace/src/includes/libs/rdbms/database/Database.php(1178): Wikimedia\Rdbms\Database->reportQueryError('unrecognized to...', 1, 'CREATE TABLE ec...', 'Wikimedia\\Rdbms...', false)
12:11:35 #3 /workspace/src/includes/libs/rdbms/database/Database.php(4987): Wikimedia\Rdbms\Database->query('CREATE TABLE ec...', 'Wikimedia\\Rdbms...')
12:11:35 #4 /workspace/src/includes/libs/rdbms/database/Database.php(4926): Wikimedia\Rdbms\Database->sourceStream(Resource id #2059, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
12:11:35 #5 /workspace/src/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile('/workspace/src/...')
12:11:35 #6 /workspace/src/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
12:11:35 #7 /workspace/src/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile('/workspace/src/...')
12:11:35 #8 /workspace/src/includes/installer/DatabaseUpdater.php(749): DatabaseUpdater->applyPatch('/workspace/src/...', true, 'Creating echo_u...')
12:11:35 #9 /workspace/src/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->addTable('echo_unread_wik...', '/workspace/src/...', true)
12:11:35 #10 /workspace/src/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates(Array, true)
12:11:35 #11 /workspace/src/maintenance/update.php(193): DatabaseUpdater->doUpdates(Array)
12:11:35 #12 /workspace/src/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
12:11:35 #13 /workspace/src/maintenance/update.php(264): require_once('/workspace/src/...')
12:11:35 #14 {main}

New one from https://integration.wikimedia.org/ci/job/quibble-vendor-sqlite-php72-noselenium-docker/1492/console:

...echo_notification_user_hash_timestamp key doesn't exist.
Creating echo_unread_wikis table...done.
Modifying euw_wiki field of table echo_unread_wikis...Wikimedia\Rdbms\DBQueryError from line 1779 of /workspace/src/includes/libs/rdbms/database/Database.php: Error 1: near "MODIFY": syntax error
Function: Wikimedia\Rdbms\Database::sourceFile( /workspace/src/extensions/Echo/db_patches/patch-increase-varchar-echo_unread_wikis-euw_wiki.sql )
Query: ALTER TABLE echo_unread_wikis MODIFY euw_wiki TEXT NOT NULL


#0 /workspace/src/includes/libs/rdbms/database/Database.php(1763): Wikimedia\Rdbms\Database->getQueryException('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#1 /workspace/src/includes/libs/rdbms/database/Database.php(1737): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#2 /workspace/src/includes/libs/rdbms/database/Database.php(1217): Wikimedia\Rdbms\Database->reportQueryError('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...', false)
#3 /workspace/src/includes/libs/rdbms/database/Database.php(5184): Wikimedia\Rdbms\Database->query('ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#4 /workspace/src/includes/libs/rdbms/database/Database.php(5123): Wikimedia\Rdbms\Database->sourceStream(Resource id #2080, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
#5 /workspace/src/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile('/workspace/src/...')
#6 /workspace/src/includes/libs/rdbms/database/DBConnRef.php(743): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
#7 /workspace/src/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\DBConnRef->sourceFile('/workspace/src/...')
#8 /workspace/src/includes/installer/DatabaseUpdater.php(986): DatabaseUpdater->applyPatch('/workspace/src/...', true, 'Modifying euw_w...')
#9 /workspace/src/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->modifyField('echo_unread_wik...', 'euw_wiki', '/workspace/src/...', true)
#10 /workspace/src/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates(Array, true)
#11 /workspace/src/maintenance/update.php(193): DatabaseUpdater->doUpdates(Array)
#12 /workspace/src/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
#13 /workspace/src/maintenance/update.php(264): require_once('/workspace/src/...')
#14 {main}

New one from https://integration.wikimedia.org/ci/job/quibble-vendor-sqlite-php72-noselenium-docker/1492/console:

...echo_notification_user_hash_timestamp key doesn't exist.
Creating echo_unread_wikis table...done.
Modifying euw_wiki field of table echo_unread_wikis...Wikimedia\Rdbms\DBQueryError from line 1779 of /workspace/src/includes/libs/rdbms/database/Database.php: Error 1: near "MODIFY": syntax error
Function: Wikimedia\Rdbms\Database::sourceFile( /workspace/src/extensions/Echo/db_patches/patch-increase-varchar-echo_unread_wikis-euw_wiki.sql )
Query: ALTER TABLE echo_unread_wikis MODIFY euw_wiki TEXT NOT NULL


#0 /workspace/src/includes/libs/rdbms/database/Database.php(1763): Wikimedia\Rdbms\Database->getQueryException('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#1 /workspace/src/includes/libs/rdbms/database/Database.php(1737): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#2 /workspace/src/includes/libs/rdbms/database/Database.php(1217): Wikimedia\Rdbms\Database->reportQueryError('near "MODIFY": ...', 1, 'ALTER TABLE ech...', 'Wikimedia\\Rdbms...', false)
#3 /workspace/src/includes/libs/rdbms/database/Database.php(5184): Wikimedia\Rdbms\Database->query('ALTER TABLE ech...', 'Wikimedia\\Rdbms...')
#4 /workspace/src/includes/libs/rdbms/database/Database.php(5123): Wikimedia\Rdbms\Database->sourceStream(Resource id #2080, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
#5 /workspace/src/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile('/workspace/src/...')
#6 /workspace/src/includes/libs/rdbms/database/DBConnRef.php(743): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
#7 /workspace/src/includes/installer/DatabaseUpdater.php(704): Wikimedia\Rdbms\DBConnRef->sourceFile('/workspace/src/...')
#8 /workspace/src/includes/installer/DatabaseUpdater.php(986): DatabaseUpdater->applyPatch('/workspace/src/...', true, 'Modifying euw_w...')
#9 /workspace/src/includes/installer/DatabaseUpdater.php(533): DatabaseUpdater->modifyField('echo_unread_wik...', 'euw_wiki', '/workspace/src/...', true)
#10 /workspace/src/includes/installer/DatabaseUpdater.php(501): DatabaseUpdater->runUpdates(Array, true)
#11 /workspace/src/maintenance/update.php(193): DatabaseUpdater->doUpdates(Array)
#12 /workspace/src/maintenance/doMaintenance.php(114): UpdateMediaWiki->execute()
#13 /workspace/src/maintenance/update.php(264): require_once('/workspace/src/...')
#14 {main}

Should go away after https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/779931

Change 799333 had a related patch set uploaded (by Jforrester; author: Jforrester):

[mediawiki/extensions/Echo@master] tests: Skip all database tests if running on Postgres

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

Change 799333 abandoned by Jforrester:

[mediawiki/extensions/Echo@master] tests: Skip all database tests if running on Postgres

Reason:

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

The switch to abstract definition seems to have changed, but not fixed, this.

Change 621928 abandoned by Umherirrender:

[mediawiki/extensions/Echo@master] Convert Echo to use abstract schema

Reason:

Now done with Ib0dca100c9885b12bc53228eddac72a5fb855d26

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

The switch to abstract definition seems to have changed, but not fixed, this.

To get the CI pass for postgres there are also some other extensions failing within the Echo CI run.
At the moment there is AbuseFilter - fix in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/AbuseFilter/+/812413 and a core fix with https://gerrit.wikimedia.org/r/c/mediawiki/core/+/812423
and FlaggedRevs - fix in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/FlaggedRevs/+/812439

Now those errors have gone (or been hidden), but the sqlite tests don't pass either:

00:03:44.095 There were 8 errors:
00:03:44.095 
00:03:44.095 1) AbuseFilterConsequencesTest::testGlobalFilters with data set #0 (array(18), array('edit', 'Global', 'Old text', 'New text', ''), array(array(18)))
00:03:44.104 Wikimedia\Rdbms\DBQueryError: Error 1: no such table: unittest_external_abuse_filter
00:03:44.104 Function: MediaWiki\Extension\AbuseFilter\FilterLookup::getAllActiveFiltersInGroupFromDB
…

… as well as the postgres installer fails:

00:01:13.855 ...index afh_timestamp already set on abuse_filter_history table.
00:01:13.855 [bc8bb729b631666c9b081925] [no req]   ArgumentCountError: Too few arguments to function PostgresUpdater::changeNullableField(), 2 passed in /workspace/src/includes/installer/DatabaseUpdater.php on line 547 and at least 3 expected
00:01:13.855 Backtrace:
00:01:13.855 from /workspace/src/includes/installer/PostgresUpdater.php(928)
…

Getting this voting so it doesn't regress would be wonderful. :-(

The following dependency extension needs to pass postgres, before Echo can pass postgres:

  • AbuseFilter
  • AntiSpoof
  • CentralAuth
  • CheckUser
  • FlaggedRevs

Going to check that

Change 814257 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/extensions/Echo@master] postgres: Convert timestamp in EchoUnreadWikis

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

Change 814257 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] postgres: Convert timestamp in EchoUnreadWikis

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

Extension itself should work with postgres
CI tests needs T298717 to pass, because AntiSpoof is part of the (soft) dependencies

MGChecker changed the task status from Stalled to Open.Sep 7 2022, 1:20 AM
Jdforrester-WMF assigned this task to Umherirrender.

Provisionally fixed.