Page MenuHomePhabricator

Reserved MySQL keywords cause query errors.
Closed, ResolvedPublic

Description

Cargo allows MySQL keywords to be used as field names which usually results in query errors such as shown below.

[6811cf3d4dd34b0098991632] [no req]   Wikimedia\Rdbms\DBQueryError from line 1081 of /var/app/current/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: SELECT  COUNT(*)  FROM `cargo__Abilities`    WHERE _pageID = '13901' AND character = 'Ashka' AND name = 'Fireball' AND description_wiki = 'Launch a Fireball that deals 15 damage. Reapplies [[Ashka#Ignite_debuff|Ignite]] on impact.' AND description_plain = 'Launch a Fireball that deals 15 damage. Reapplies Ignite on impact.' AND key = 'LMB' AND casttime = '0.4' AND cooldown = '0.3' AND type = 'Projectile' AND energy = '0' AND version IS NULL AND property1 = 'Energy Gain' AND value1 = '7%' AND property2 IS NULL AND value2 IS NULL AND property3 IS NULL AND value3 IS NULL AND property4 IS NULL AND value4 IS NULL AND property5 IS NULL AND value5 IS NULL AND property6 IS NULL AND value6 IS NULL  
Function: Wikimedia\Rdbms\Database::select
Error: 0 

Backtrace:
#0 /var/app/current/includes/libs/rdbms/database/Database.php(933): Wikimedia\Rdbms\Database->reportQueryError(NULL, integer, string, string, boolean)
#1 /var/app/current/includes/libs/rdbms/database/Database.php(1275): Wikimedia\Rdbms\Database->query(string, string)
#2 /var/app/current/extensions/Cargo/includes/parserfunctions/CargoStore.php(309): Wikimedia\Rdbms\Database->select(string, string, array)
#3 /var/app/current/extensions/Cargo/includes/parserfunctions/CargoStore.php(111): CargoStore::storeAllData(Title, string, array, CargoTableSchema)
#4 /var/app/current/includes/parser/Parser.php(3408): CargoStore::run(Parser, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string, string)
#5 /var/app/current/includes/parser/Parser.php(3128): Parser->callParserFunction(PPTemplateFrame_DOM, string, array)
#6 /var/app/current/includes/parser/Preprocessor_DOM.php(1199): Parser->braceSubstitution(array, PPTemplateFrame_DOM)
#7 /var/app/current/includes/parser/Parser.php(3282): PPFrame_DOM->expand(DOMElement)
#8 /var/app/current/includes/parser/Preprocessor_DOM.php(1199): Parser->braceSubstitution(array, PPFrame_DOM)
#9 /var/app/current/includes/parser/Parser.php(2942): PPFrame_DOM->expand(DOMElement, integer)
#10 /var/app/current/includes/parser/Parser.php(1297): Parser->replaceVariables(string)
#11 /var/app/current/includes/parser/Parser.php(451): Parser->internalParse(string)
#12 /var/app/current/includes/StubObject.php(113): Parser->parse(string, Title, ParserOptions)
#13 /var/app/current/includes/StubObject.php(139): StubObject->_call(string, array)
#14 /var/app/current/extensions/Cargo/includes/CargoUtils.php(431): StubObject->__call(string, array)
#15 /var/app/current/extensions/Cargo/maintenance/cargoRecreateData.php(152): CargoUtils::parsePageForStorage(Title, string)
#16 /var/app/current/extensions/Cargo/maintenance/cargoRecreateData.php(69): CargoRecreateData->recreateAllDataForTable(string, boolean)
#17 /var/app/current/maintenance/doMaintenance.php(111): CargoRecreateData->execute()
#18 /var/app/current/extensions/Cargo/maintenance/cargoRecreateData.php(161): require_once(string)
#19 /var/app/current/maintenance/hostHelper.php(60): require(string)
#20 {main}

My work around for the moment is to back tick all of the field names before they get sent off to MediaWiki's database layer. I am not sure this is the best fix though.

		$_tableFieldValuesForCheck = [];
		foreach ($tableFieldValuesForCheck as $_field => $_condition) {
			$_tableFieldValuesForCheck['`'.$_field.'`'] = $_condition;
		}
		$tableFieldValuesForCheck = $_tableFieldValuesForCheck;

Event Timeline

Alexia created this task.Nov 15 2017, 1:48 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 15 2017, 1:48 AM
Yaron_Koren closed this task as Resolved.Nov 15 2017, 2:44 PM
Yaron_Koren claimed this task.
Yaron_Koren added a subscriber: Yaron_Koren.

@Alexia - sorry about the problem, and it's great that you were able to find a fix. I checked in a fix pretty similar to yours, so hopefully this has been resolved.

A similar error on line 244 of drilldown/CargoFilter.php. Path of Exile has weapons with range so that conflicts with the MySQL reserved word.

 Wikimedia\Rdbms\DBQueryError: Uncaught exception 'Wikimedia\Rdbms\DBQueryError' with message 'A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? Query: SELECT range AS value,COUNT(*) AS total FROM `cargo__Ability_poe1` GROUP BY range Function: Error: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'range AS value,COUNT(*) AS total FROM `cargo__Ability_poe1` GROUP BY range' at line 1 (***)' in /var/app/current/includes/libs/rdbms/database/Database.php:1081
Stack trace
…ueryError called at /var/app/current/includes/libs/rdbms/database/
Database.php (933)
…se::query called at /var/app/current/includes/libs/rdbms/database/
Database.php (1275)
…::select called at /var/app/current/extensions/Cargo/drilldown/
CargoFilter.php (245)
…alled at /var/app/current/extensions/Cargo/drilldown/
CargoSpecialDrilldown.php (1089)
…alled at /var/app/current/extensions/Cargo/drilldown/
CargoSpecialDrilldown.php (1262)
…e::getPageHeader called at /var/app/current/includes/specialpage/
QueryPage.php (643)
…alled at /var/app/current/extensions/Cargo/drilldown/
CargoSpecialDrilldown.php (158)
…ldown::execute called at /var/app/current/includes/specialpage/
SpecialPage.php (522)
…ge::run called at /var/app/current/includes/specialpage/
SpecialPageFactory.php (578)
…cialPageFactory::executePath called at /var/app/current/includes/
MediaWiki.php (287)
in MediaWiki::performRequest called at /var/app/current/includes/MediaWiki.php (862)
in MediaWiki::main called at /var/app/current/includes/MediaWiki.php (523)
in MediaWiki::run called at /var/app/current/index.php (43)

Ah, I never thought to test with a field name that's an SQL keyword. Sorry about the problem! Thankfully, it looks like the issues are only in Special:Drilldown. I just checked in some fixes, so hopefully everything works now.

It probably will! I will get this fix deployed on the Gamepedia stack today.