Page MenuHomePhabricator

CheckUser: Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'cuc_private' in 'field list'
Closed, ResolvedPublicSecurity

Description

When promoting group0 wikis to MW-1.40-notes (1.40.0-wmf.6; 2022-10-17) , we had errors Unknown column 'cuc_private' in 'field list' being logged from three different hooks:

  • MediaWiki\CheckUser\Hooks::updateCheckUserData
  • MediaWiki\CheckUser\Hooks::onAuthManagerLoginAuthenticateAudit
  • MediaWiki\CheckUser\Hooks::onLocalUserCreated

The reason is our oldest wikis databases never had the cuc_private Schema-change-in-production applied. From T233004#5495636 and below:

There is actually not cuc_private column from what @Marostegui can see on enwiki.

@Reedy guesses it's not a feature we use with $wgCUPublicKey being "" by default.

The probable root cause is https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CheckUser/+/824746/7/src/Hooks.php which inconditonally inserts cuc_private.


  • mwversion: 1.40.0-wmf.6
MediaWiki\CheckUser\Hooks::onLocalUserCreated
normalized_message
[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'cuc_private' in 'field list'
Function: MediaWiki\CheckUser\Hooks::onLocalUserCreated
Query: INSERT INTO `cu_changes` (cuc_page_id,cuc_namespace,cuc_minor,cuc_title,cuc_u
exception.trace
from /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1558)
#0 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1542): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1516): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(892): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1775): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->insert(string, array, string)
#5 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(381): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(283): Wikimedia\Rdbms\DBConnRef->insert(string, array, string)
#7 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(375): MediaWiki\CheckUser\Hooks::insertIntoCuChangesTable(array, string, User)
#8 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookContainer.php(160): MediaWiki\CheckUser\Hooks->onLocalUserCreated(User, boolean)
#9 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookRunner.php(2408): MediaWiki\HookContainer\HookContainer->run(string, array)
#10 /srv/mediawiki/php-1.40.0-wmf.6/includes/auth/AuthManager.php(1581): MediaWiki\HookContainer\HookRunner->onLocalUserCreated(User, boolean)
#11 /srv/mediawiki/php-1.40.0-wmf.6/includes/auth/AuthManager.php(1294): MediaWiki\Auth\AuthManager->continueAccountCreation(array)
#12 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(376): MediaWiki\Auth\AuthManager->beginAccountCreation(User, array, string)
#13 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(504): AuthManagerSpecialPage->performAuthenticationStep(string, array)
#14 /srv/mediawiki/php-1.40.0-wmf.6/includes/htmlform/HTMLForm.php(730): AuthManagerSpecialPage->handleFormSubmit(array, VFormHTMLForm)
#15 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(435): HTMLForm->trySubmit()
#16 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/LoginSignupSpecialPage.php(320): AuthManagerSpecialPage->trySubmit()
#17 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/SpecialPage.php(701): LoginSignupSpecialPage->execute(NULL)
#18 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/SpecialPageFactory.php(1451): SpecialPage->run(NULL)
#19 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(316): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, RequestContext)
#20 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(904): MediaWiki->performRequest()
#21 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(562): MediaWiki->main()
#22 /srv/mediawiki/php-1.40.0-wmf.6/index.php(50): MediaWiki->run()
#23 /srv/mediawiki/php-1.40.0-wmf.6/index.php(46): wfIndexMain()
#24 /srv/mediawiki/w/index.php(3): require(string)
#25 {main}
MediaWiki\CheckUser\Hooks::onAuthManagerLoginAuthenticateAudit
normalized_message
[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'cuc_private' in 'field list'
Function: MediaWiki\CheckUser\Hooks::onAuthManagerLoginAuthenticateAudit
Query: INSERT INTO `cu_changes` (cuc_page_id,cuc_namespace,cuc_mino
exception.trace
from /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1558)
#0 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1542): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1516): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(892): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1775): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->insert(string, array, string)
#5 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(381): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(283): Wikimedia\Rdbms\DBConnRef->insert(string, array, string)
#7 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(468): MediaWiki\CheckUser\Hooks::insertIntoCuChangesTable(array, string, MediaWiki\User\UserIdentityValue)
#8 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookContainer.php(160): MediaWiki\CheckUser\Hooks->onAuthManagerLoginAuthenticateAudit(MediaWiki\Auth\AuthenticationResponse, User, string, array)
#9 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookRunner.php(890): MediaWiki\HookContainer\HookContainer->run(string, array)
#10 /srv/mediawiki/php-1.40.0-wmf.6/includes/auth/AuthManager.php(434): MediaWiki\HookContainer\HookRunner->onAuthManagerLoginAuthenticateAudit(MediaWiki\Auth\AuthenticationResponse, NULL, string, array)
#11 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(372): MediaWiki\Auth\AuthManager->beginAuthentication(array, string)
#12 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(504): AuthManagerSpecialPage->performAuthenticationStep(string, array)
#13 /srv/mediawiki/php-1.40.0-wmf.6/includes/htmlform/HTMLForm.php(730): AuthManagerSpecialPage->handleFormSubmit(array, VFormHTMLForm)
#14 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/AuthManagerSpecialPage.php(435): HTMLForm->trySubmit()
#15 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/LoginSignupSpecialPage.php(320): AuthManagerSpecialPage->trySubmit()
#16 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/SpecialPage.php(701): LoginSignupSpecialPage->execute(NULL)
#17 /srv/mediawiki/php-1.40.0-wmf.6/includes/specialpage/SpecialPageFactory.php(1451): SpecialPage->run(NULL)
#18 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(316): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, RequestContext)
#19 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(904): MediaWiki->performRequest()
#20 /srv/mediawiki/php-1.40.0-wmf.6/includes/MediaWiki.php(562): MediaWiki->main()
#21 /srv/mediawiki/php-1.40.0-wmf.6/index.php(50): MediaWiki->run()
#22 /srv/mediawiki/php-1.40.0-wmf.6/index.php(46): wfIndexMain()
#23 /srv/mediawiki/w/index.php(3): require(string)
#24 {main}
MediaWiki\CheckUser\Hooks::updateCheckUserData
normalized_message
[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'cuc_private' in 'field list'
Function: MediaWiki\CheckUser\Hooks::updateCheckUserData
Query: INSERT INTO `cu_changes` (cuc_page_id,cuc_namespace,cuc_minor,cuc_title,cuc_
exception.trace
from /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1558)
#0 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1542): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1516): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(892): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/Database.php(1775): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->insert(string, array, string)
#5 /srv/mediawiki/php-1.40.0-wmf.6/includes/libs/rdbms/database/DBConnRef.php(381): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(283): Wikimedia\Rdbms\DBConnRef->insert(string, array, string)
#7 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(204): MediaWiki\CheckUser\Hooks::insertIntoCuChangesTable(array, string, MediaWiki\User\UserIdentityValue)
#8 /srv/mediawiki/php-1.40.0-wmf.6/extensions/CheckUser/src/Hooks.php(933): MediaWiki\CheckUser\Hooks::updateCheckUserData(RecentChange)
#9 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookContainer.php(160): MediaWiki\CheckUser\Hooks->onRecentChange_save(RecentChange)
#10 /srv/mediawiki/php-1.40.0-wmf.6/includes/HookContainer/HookRunner.php(3196): MediaWiki\HookContainer\HookContainer->run(string, array)
#11 /srv/mediawiki/php-1.40.0-wmf.6/includes/changes/RecentChange.php(469): MediaWiki\HookContainer\HookRunner->onRecentChange_save(RecentChange)
#12 /srv/mediawiki/php-1.40.0-wmf.6/includes/changes/RecentChange.php(786): RecentChange->save()
#13 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/MWCallableUpdate.php(38): RecentChange::{closure}()
#14 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdates.php(474): MWCallableUpdate->doUpdate()
#15 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdates.php(399): DeferredUpdates::attemptUpdate(MWCallableUpdate, Wikimedia\Rdbms\LBFactoryMulti)
#16 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdates.php(214): DeferredUpdates::run(MWCallableUpdate, Wikimedia\Rdbms\LBFactoryMulti, Monolog\Logger, BufferingStatsdDataFactory, MediaWiki\JobQueue\JobQueueGroupFactory, string)
#17 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdatesScope.php(267): DeferredUpdates::{closure}(MWCallableUpdate, integer)
#18 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdatesScope.php(196): DeferredUpdatesScope->processStageQueue(integer, integer, Closure)
#19 /srv/mediawiki/php-1.40.0-wmf.6/includes/deferred/DeferredUpdates.php(235): DeferredUpdatesScope->processUpdates(integer, Closure)
#20 /srv/mediawiki/php-1.40.0-wmf.6/extensions/EventBus/includes/JobExecutor.php(105): DeferredUpdates::doUpdates()
#21 /srv/mediawiki/rpc/RunSingleJob.php(77): MediaWiki\Extension\EventBus\JobExecutor->execute(array)
#22 {main}

Event Timeline

hashar set Security to Software security bug.Oct 18 2022, 8:53 AM
hashar added projects: Security, Security-Team.
hashar changed the visibility from "Public (No Login Required)" to "Custom Policy".
hashar changed the subtype of this task from "Production Error" to "Security Issue".

Marking as a security issue since the traces might end up having private data.

hashar triaged this task as Unbreak Now! priority.Oct 18 2022, 8:59 AM
hashar added projects: CheckUser, DBA.
hashar updated the task description. (Show Details)

Seems to be related to https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CheckUser/+/824746, although I'm not sure why the field doesn't exist in production..

Marking as a security issue since the traces might end up having private data.

Looks like I have managed to NOT paste any private user data, so we can probably lift the restriction. I would rather have that double checked cause anything related to CheckUser makes me paranoid.

wikiadmin@10.64.132.14(mediawikiwiki)> SHOW COLUMNS FROM cu_changes;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| cuc_id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| cuc_namespace  | int(11)             | NO   |     | 0       |                |
| cuc_title      | varbinary(255)      | NO   |     |         |                |
| cuc_user       | int(11)             | NO   | MUL | 0       |                |
| cuc_user_text  | varbinary(255)      | NO   |     |         |                |
| cuc_actor      | bigint(20) unsigned | NO   | MUL | 0       |                |
| cuc_actiontext | varbinary(255)      | NO   |     |         |                |
| cuc_comment    | varbinary(255)      | NO   |     |         |                |
| cuc_comment_id | bigint(20) unsigned | NO   |     | 0       |                |
| cuc_minor      | tinyint(1)          | NO   |     | 0       |                |
| cuc_page_id    | int(10) unsigned    | NO   |     | 0       |                |
| cuc_this_oldid | int(10) unsigned    | NO   |     | 0       |                |
| cuc_last_oldid | int(10) unsigned    | NO   |     | 0       |                |
| cuc_type       | tinyint(3) unsigned | NO   |     | 0       |                |
| cuc_timestamp  | binary(14)          | NO   | MUL | NULL    |                |
| cuc_ip         | varbinary(255)      | YES  |     |         |                |
| cuc_ip_hex     | varbinary(255)      | YES  | MUL | NULL    |                |
| cuc_xff        | varbinary(255)      | YES  |     |         |                |
| cuc_xff_hex    | varbinary(255)      | YES  | MUL | NULL    |                |
| cuc_agent      | varbinary(255)      | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+
20 rows in set (0.001 sec)

That looks related to T233004#5495636 and follow up comments. There is no such cuc_private on our oldest wikis databases, apparently cause $wgCUPublicKey being "" by default.

hashar updated the task description. (Show Details)
hashar removed projects: Security-Team, Security.
hashar changed the visibility from "Custom Policy" to "Public (No Login Required)".
hashar added subscribers: Marostegui, Reedy.

Manuel is on vacation, I suggest reverting the patch that uses the column and I start the ticket to fix the drift in production. If you wonder why it happened see https://lists.wikimedia.org/hyperkitty/list/wikitech-l@lists.wikimedia.org/message/EMUAKAX4X3DCE3MSCW6LF4SHQJ5A5LYX/

Change 843905 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/CheckUser@master] Revert "Add multiple integration tests for Hooks.php"

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

Change 843906 had a related patch set uploaded (by Urbanecm; author: Zabe):

[mediawiki/extensions/CheckUser@wmf/1.40.0-wmf.6] Revert "Add multiple integration tests for Hooks.php"

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

Change 843905 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Revert "Add multiple integration tests for Hooks.php"

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

Change 843906 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@wmf/1.40.0-wmf.6] Revert "Add multiple integration tests for Hooks.php"

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

This should now be fixed in production. Leaving for @hashar to resolve after promoting the train.

Thanks for noticing this. Is there a way for me to be able to verify that a DB field exists on all wikis? CheckUser is not included in the DB dumps so I cannot run SQL queries to verify this myself and I want to avoid a similar situation to this again.

Thanks for noticing this. Is there a way for me to be able to verify that a DB field exists on all wikis? CheckUser is not included in the DB dumps so I cannot run SQL queries to verify this myself and I want to avoid a similar situation to this again.

I have some bad news for you :D

The whole drifts mess is something we have been trying to clean up for years now, I mentioned some details in https://lists.wikimedia.org/hyperkitty/list/wikitech-l@lists.wikimedia.org/message/EMUAKAX4X3DCE3MSCW6LF4SHQJ5A5LYX/

For your specific case, you need someone with production access to run the drift analyzer code (https://github.com/Ladsgroup/db-analyzor-tools/blob/master/db_drift_checker.py) for CU extension. I believe I ran this already for that extension, I just never got to analyze it (FlaggedRevs has a much bigger drift: T313253), let me try to quickly add this to drift-tracker.toolforge.org/.

Note that checking dumps is not that useful because lots of drifts can happen only on subset of replicas and you might miss those.

Here you are: https://drift-tracker.toolforge.org/report/checkuser/ keep it in mind that it picked a random wiki from each section so the report is not complete.

Create a dedicated ticket for this and we can take over from that. I think this ticket should be closed.

Thanks for creating that. I will create a new ticket.

@Dreamy_Jazz you probably can get https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CheckUser/+/824746/7/src/Hooks.php redone by adding a condition to not inject the cuc_private field when $wgCUPublicKey is an empty string. Similar to:

src/Hooks.php
    public function onEmailUser( &$to, &$from, &$subject, &$text, &$error ) {
...
        if ( trim( $wgCUPublicKey ) != '' ) {
            $privateData = $userTo->getEmail() . ":" . $userTo->getId();
            $encryptedData = new EncryptedData( $privateData, $wgCUPublicKey );
            $row['cuc_private'] = serialize( $encryptedData );
        }

This way the change will be deployable to Wikimedia and we will get the tests you have added in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CheckUser/+/824746

Once the cuc_private field has been added to our wikis, the condition can be removed I guess.

I have promoted group 0 to 1.40.0-wmf.6 and there is no more any errors showing up. Thank you everyone!

@hashar I've created a new change that instead does not add the default for cuc_private and removes a test that relied on cuc_private being an empty string (which is why the default was needed) in https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CheckUser/+/843927.

T321063 created from the data at the drift tracker. I am very surprised that some of these drifts have not caused production errors as 'cuc_agent' is always written to on any action that is logged by checkuser, but according to the drift data sometimes does not exist as a field which would cause a DB error.

Umherirrender added a subscriber: Umherirrender.

Schema changes should be separate tasks, not mixed with errors, created T321130 to track that.

Connexion ok today; sounds normal.

Ladsgroup assigned this task to Zabe.