Fatal exception of type "DBQueryError" on sorting ORES contributions
Closed, ResolvedPublic

Description

Example of error occuring: https://en.wikipedia.org/w/index.php?limit=50&title=Special%3AContributions&contribs=user&target=Chrissymad&namespace=&tagfilter=&hidenondamaging=1&year=2017&month=-1

Steps to reproduce

  1. Load Special:Contributions
  2. Select "Only show edits needing review"

Expected behaviour

  1. Show's only edits requiring review

Actual behaviour

  1. Error message shown:

A database query error has occurred. This may indicate a bug in the software.[WH5CjgpAICoAABcj-isAAAAR] 2017-01-17 16:13:02: Fatal exception of type "DBQueryError"

Samtar created this task.Jan 17 2017, 4:23 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Hello,

Thanks for the ticket.
I have been taking a look and this looks related to the following query:

SELECT  rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,rev_content_format,rev_content_model,user_name,page_namespace,page_title,page_is_new,page_latest,page_is_redirect,page_len,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`    WHERE ct_rev_id=rev_id  ) AS `ts_tags`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,'0.49' AS `ores_damaging_threshold`  FROM `revision` FORCE INDEX (user_timestamp) INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((rev_user != 0) AND (user_id = rev_user)) LEFT JOIN `ores_model` `ores_damaging_mdl` ON (ores_damaging_mdl.oresm_is_current = '1' AND ores_damaging_mdl.oresm_name = 'damaging') LEFT JOIN `ores_classification` `ores_damaging_cls` ON ((ores_damaging_cls.oresc_model = ores_damaging_mdl.oresm_id) AND (rev_id = ores_damaging_cls.oresc_rev) AND ores_damaging_cls.oresc_class = '1')   WHERE rev_user = 'xxxxxx' AND ((rev_deleted & 12) != 12) AND (ores_damaging_cls.oresc_probability > '0.49') AND rc_patrolled = '0' AND (rev_timestamp>='20180101000000')  ORDER BY rev_timestamp LIMIT 1

And the error it gives underneath is on db1055 is:

ERROR 1054 (42S22): Unknown column 'rc_patrolled' in 'where clause'

There is indeed not such column on that table:

root@PRODUCTION s1[enwiki]> show create table ores_classification\G
*************************** 1. row ***************************
       Table: ores_classification
Create Table: CREATE TABLE `ores_classification` (
  `oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `oresc_rev` int(10) unsigned NOT NULL,
  `oresc_model` smallint(6) NOT NULL,
  `oresc_class` tinyint(4) NOT NULL,
  `oresc_probability` decimal(3,3) NOT NULL,
  `oresc_is_predicted` tinyint(1) NOT NULL,
  PRIMARY KEY (`oresc_id`),
  KEY `oresc_winner` (`oresc_rev`,`oresc_is_predicted`)
) ENGINE=InnoDB AUTO_INCREMENT=34986511 DEFAULT CHARSET=binary

The rc_patrolled column is on the recentchanges table:

root@PRODUCTION s1[enwiki]> show create table recentchanges\G
*************************** 1. row ***************************
       Table: recentchanges
Create Table: CREATE TABLE `recentchanges` (
  `rc_id` int(8) NOT NULL AUTO_INCREMENT,
  `rc_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `rc_cur_time` varbinary(14) NOT NULL DEFAULT '',
  `rc_user` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_user_text` varbinary(255) NOT NULL DEFAULT '',
  `rc_namespace` int(11) NOT NULL DEFAULT '0',
  `rc_title` varbinary(255) NOT NULL DEFAULT '',
  `rc_comment` varbinary(255) NOT NULL DEFAULT '',
  `rc_minor` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_bot` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_cur_id` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_this_oldid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_last_oldid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_source` varbinary(16) NOT NULL DEFAULT '',
  `rc_moved_to_ns` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_moved_to_title` varbinary(255) NOT NULL DEFAULT '',
  `rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `rc_ip` varbinary(40) NOT NULL DEFAULT '',
  `rc_old_len` int(10) DEFAULT NULL,
  `rc_new_len` int(10) DEFAULT NULL,
  `rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `rc_logid` int(10) unsigned NOT NULL DEFAULT '0',
  `rc_log_type` varbinary(255) DEFAULT NULL,
  `rc_log_action` varbinary(255) DEFAULT NULL,
  `rc_params` blob NOT NULL,
  PRIMARY KEY (`rc_id`),
  KEY `rc_timestamp` (`rc_timestamp`),
  KEY `rc_namespace_title` (`rc_namespace`,`rc_title`),
  KEY `rc_cur_id` (`rc_cur_id`),
  KEY `new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`),
  KEY `rc_ip` (`rc_ip`),
  KEY `rc_ns_usertext` (`rc_namespace`,`rc_user_text`),
  KEY `rc_user_text` (`rc_user_text`,`rc_timestamp`),
  KEY `tmp_1` (`rc_this_oldid`),
  KEY `tmp_2` (`rc_bot`,`rc_timestamp`),
  KEY `tmp_3` (`rc_namespace`,`rc_timestamp`),
  KEY `rc_name_type_patrolled_timestamp` (`rc_namespace`,`rc_type`,`rc_patrolled`,`rc_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=903004365 DEFAULT CHARSET=binary

Hope this helps to identify the faulty code.

Halfak assigned this task to Ladsgroup.Jan 19 2017, 3:25 PM
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptJan 19 2017, 3:25 PM

I wish I would have seen this sooner. A patch is coming, Since it's Friday, we can't do SWAT but I'll see if I can get the fix deployed.

Change 333226 had a related patch set uploaded (by Ladsgroup):
Do not add rc_patrol = 0 when querying user contributions

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

Ladsgroup triaged this task as "Unbreak Now!" priority.Jan 20 2017, 10:26 AM
Restricted Application added subscribers: Jay8g, TerraCodes. · View Herald TranscriptJan 20 2017, 10:26 AM

I'll add a regression test soon.

Change 333229 had a related patch set uploaded (by Thiemo Mättig (WMDE)):
Clean up special case handling in ORES\Hooks::hideNonDamagingFilter

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

Change 333229 abandoned by Thiemo Mättig (WMDE):
Clean up special case handling in ORES\Hooks::hideNonDamagingFilter

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

Change 333226 merged by jenkins-bot:
Do not add rc_patrol = 0 when querying user contributions

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

Change 333236 had a related patch set uploaded (by Ladsgroup):
Do not add rc_patrol = 0 when querying user contributions

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

Change 333236 merged by jenkins-bot:
Do not add rc_patrol = 0 when querying user contributions

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

Mentioned in SAL (#wikimedia-operations) [2017-01-20T12:13:10Z] <Amir1> deploy wmf.8 in mwdebug1002 (T155500)

Mentioned in SAL (#wikimedia-operations) [2017-01-20T12:32:15Z] <ladsgroup@tin> Synchronized php-1.29.0-wmf.8/extensions/ORES/includes/Hooks.php: [[gerrit:333226|ORES database query fix]] (T155500) (duration: 00m 40s)

Ladsgroup moved this task from Active to Done on the Revision-Scoring-As-A-Service board.
Ladsgroup closed this task as "Resolved".

Change 333586 had a related patch set uploaded (by Ladsgroup):
Add regression test for when hidenondamaging is true in Special:Contributions

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

Change 333586 merged by jenkins-bot:
Add regression test for when hidenondamaging is true in Special:Contributions

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