Page MenuHomePhabricator

Special:RecentChanges with userExpLevel=newcomer causes Fatal exception of type "Wikimedia\Rdbms\DBQueryError": Unknown column 'actor_user'
Closed, ResolvedPublicPRODUCTION ERROR

Description

Error
normalized_message
[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1054: Unknown column 'actor_user' in 'on clause' (10.64.16.208)
Function: SpecialRecentChanges::doMainQuery
Query: SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_c
exception.trace
from /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/Database.php(1738)
#0 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/Database.php(1722): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/Database.php(1697): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/Database.php(1260): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/Database.php(1948): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#5 /srv/mediawiki/php-1.37.0-wmf.4/includes/libs/rdbms/database/DBConnRef.php(313): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.37.0-wmf.4/includes/specials/SpecialRecentChanges.php(413): Wikimedia\Rdbms\DBConnRef->select(array, array, array, string, array, array)
#7 /srv/mediawiki/php-1.37.0-wmf.4/includes/specialpage/ChangesListSpecialPage.php(1032): SpecialRecentChanges->doMainQuery(array, array, array, array, array, FormOptions)
#8 /srv/mediawiki/php-1.37.0-wmf.4/includes/specialpage/ChangesListSpecialPage.php(635): ChangesListSpecialPage->getRows()
#9 /srv/mediawiki/php-1.37.0-wmf.4/includes/specials/SpecialRecentChanges.php(203): ChangesListSpecialPage->execute(NULL)
#10 /srv/mediawiki/php-1.37.0-wmf.4/includes/specialpage/SpecialPage.php(646): SpecialRecentChanges->execute(NULL)
#11 /srv/mediawiki/php-1.37.0-wmf.4/includes/specialpage/SpecialPageFactory.php(1396): SpecialPage->run(NULL)
#12 /srv/mediawiki/php-1.37.0-wmf.4/includes/MediaWiki.php(313): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, RequestContext)
#13 /srv/mediawiki/php-1.37.0-wmf.4/includes/MediaWiki.php(916): MediaWiki->performRequest()
#14 /srv/mediawiki/php-1.37.0-wmf.4/includes/MediaWiki.php(550): MediaWiki->main()
#15 /srv/mediawiki/php-1.37.0-wmf.4/index.php(53): MediaWiki->run()
#16 /srv/mediawiki/php-1.37.0-wmf.4/index.php(46): wfIndexMain()
#17 /srv/mediawiki/w/index.php(3): require(string)
#18 {main}
Impact

https://www.mediawiki.org/wiki/Special:RecentChanges?userExpLevel=newcomer is unavailable.

Notes

This is happening as of today on mediawiki.org.

Raw SQL that fails:

SELECT  rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` LEFT JOIN `user` ON ((actor_user=user_id)) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 1967330 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (NOT ( (user_editcount >= 10) AND ((user_registration IS NULL) OR (user_registration <= '20210501134128')) )) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199)) AND (rc_timestamp >= '20210428134128') AND rc_new IN (0,1)   ORDER BY rc_timestamp DESC LIMIT 500

Details

Request URL
https://www.mediawiki.org/wiki/Special:RecentChanges?userExpLevel=newcomer

Event Timeline

This is happening on beta too, but not on Wikipedia: https://meta.wikimedia.beta.wmflabs.org/wiki/Special:RecentChanges?userExpLevel=newcomer

[YJKbrCL4uNw1n7FhyDYg-QAAAAU] /wiki/Special:RecentChanges?userExpLevel=newcomer Wikimedia\Rdbms\DBQueryError: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?

Error 1054: Unknown column 'actor_user' in 'on clause' (172.16.6.39:3306)
Function: SpecialRecentChanges::doMainQuery
Query: SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` LEFT JOIN `user` ON ((actor_user=user_id)) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE (NOT ( (user_editcount >= 10) AND ((user_registration IS NULL) OR (user_registration <= '20210501132044')) )) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20210428132044') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50

Backtrace:

from /srv/mediawiki/php-master/includes/libs/rdbms/database/Database.php(1738)
#0 /srv/mediawiki/php-master/includes/libs/rdbms/database/Database.php(1722): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-master/includes/libs/rdbms/database/Database.php(1697): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-master/includes/libs/rdbms/database/Database.php(1260): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-master/includes/libs/rdbms/database/Database.php(1948): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-master/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#5 /srv/mediawiki/php-master/includes/libs/rdbms/database/DBConnRef.php(313): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-master/includes/specials/SpecialRecentChanges.php(413): Wikimedia\Rdbms\DBConnRef->select(array, array, array, string, array, array)
#7 /srv/mediawiki/php-master/includes/specialpage/ChangesListSpecialPage.php(1032): SpecialRecentChanges->doMainQuery(array, array, array, array, array, FormOptions)
#8 /srv/mediawiki/php-master/includes/specialpage/ChangesListSpecialPage.php(635): ChangesListSpecialPage->getRows()
#9 /srv/mediawiki/php-master/includes/specials/SpecialRecentChanges.php(203): ChangesListSpecialPage->execute(NULL)
#10 /srv/mediawiki/php-master/includes/specialpage/SpecialPage.php(646): SpecialRecentChanges->execute(NULL)
#11 /srv/mediawiki/php-master/includes/specialpage/SpecialPageFactory.php(1396): SpecialPage->run(NULL)
#12 /srv/mediawiki/php-master/includes/MediaWiki.php(313): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, RequestContext)
#13 /srv/mediawiki/php-master/includes/MediaWiki.php(916): MediaWiki->performRequest()
#14 /srv/mediawiki/php-master/includes/MediaWiki.php(550): MediaWiki->main()
#15 /srv/mediawiki/php-master/index.php(53): MediaWiki->run()
#16 /srv/mediawiki/php-master/index.php(46): wfIndexMain()
#17 /srv/mediawiki/w/index.php(3): require(string)
#18 {main}
Aklapper renamed this task from Special:RecentChanges with userExpLevel=newcomer causes Fatal exception of type "Wikimedia\Rdbms\DBQueryError" to Special:RecentChanges with userExpLevel=newcomer causes Fatal exception of type "Wikimedia\Rdbms\DBQueryError": Unknown column 'actor_user'.May 5 2021, 1:24 PM
hashar triaged this task as Unbreak Now! priority.May 5 2021, 1:38 PM
Urbanecm changed the subtype of this task from "Task" to "Production Error".May 5 2021, 1:41 PM
Urbanecm set Request URL to https://www.mediawiki.org/wiki/Special:RecentChanges?userExpLevel=newcomer.May 5 2021, 1:43 PM
Urbanecm updated the task description. (Show Details)
Urbanecm added a project: Platform Engineering.
Urbanecm added a subscriber: Urbanecm.

Very likely caused by Platform Engineering, tagging them as well.

rMWfd01c537787d: Stop using legacy ActorMigration field "rc_user" from T278917: Clean up obsolete ActorMigration usages for non-temp tables. I get the error with that change.

Before rMWfd01c5377:

SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,actor_rc_user.actor_user AS `rc_user`,actor_rc_user.actor_name AS `rc_user_text`,rc_actor,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `user` ON ((actor_rc_user.actor_user = user_id)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE (NOT ( (user_editcount >= 10) AND ((user_registration IS NULL) OR (user_registration <= '20210501135016')) )) AND rc_bot = 0 AND (rc_timestamp >= '20210428135016') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50

After:

SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE ct_rc_id=rc_id ) AS `ts_tags` FROM `recentchanges` LEFT JOIN `user` ON ((actor_user=user_id)) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE (NOT ( (user_editcount >= 10) AND ((user_registration IS NULL) OR (user_registration <= '20210501135136')) )) AND rc_bot = 0 AND (rc_timestamp >= '20210428135136') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50

Comparing the query side by side (after normalization), it would seem that the

LEFT JOIN `user` ON ((actor_user=user_id))

is the culprit.
This used to be

LEFT JOIN `user` ON ((actor_rc_user.actor_user=user_id))

This can be found in ChangesListSpecialPage::filterOnUserExperienceLevel (line 1900).

Hacking around with prefixing it with either recentchanges_actor (which is the new name) nor actor (which is the actual table name) doesn't seem to work though...

The issue is that LEFT JOIN on user is now added before JOIN on actor, and we can only reference fields from tables joined before. Tim's change removed explicit addition of actor join from https://gerrit.wikimedia.org/r/c/mediawiki/core/+/680822/10/includes/specialpage/ChangesListSpecialPage.php#156 because the join on actor will certainly be added by RecentChange::getQueryInfo which ended up ordering join on user before join on actor.

Change 685521 had a related patch set uploaded (by Ppchelko; author: Ppchelko):

[mediawiki/core@master] Fix order of joins in SpecialRecentChanges

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

Change 685521 merged by jenkins-bot:

[mediawiki/core@master] Fix order of joins in SpecialRecentChanges

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

Pchelolo lowered the priority of this task from Unbreak Now! to High.May 5 2021, 5:28 PM

Fix merged. Perhaps @tstarling will want to enhance my quick fix for the issue, so lowering the priority to high and not resolving the ticket

Change 685480 had a related patch set uploaded (by Brennen Bearnes; author: Ppchelko):

[mediawiki/core@wmf/1.37.0-wmf.4] Fix order of joins in SpecialRecentChanges

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

Change 685480 merged by jenkins-bot:

[mediawiki/core@wmf/1.37.0-wmf.4] Fix order of joins in SpecialRecentChanges

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

Mentioned in SAL (#wikimedia-operations) [2021-05-05T19:14:21Z] <brennen@deploy1002> Synchronized php-1.37.0-wmf.4/includes/specials: Backport: [[gerrit:685480|Fix order of joins in SpecialRecentChanges (T281981)]] (duration: 01m 08s)

Mentioned in SAL (#wikimedia-operations) [2021-05-05T19:16:11Z] <brennen@deploy1002> Synchronized php-1.37.0-wmf.4/tests/phpunit/includes: Backport: [[gerrit:685480|Fix order of joins in SpecialRecentChanges (T281981)]] (duration: 01m 10s)

Krinkle reassigned this task from Pchelolo to tstarling.
Krinkle added a subscriber: Krinkle.

Fix merged. Perhaps @tstarling will want to enhance my quick fix for the issue, so lowering the priority to high and not resolving the ticket

OK.