SQL syntax error after turning on Usage

[michellekb]

Installing Tags 1.0.5 via plugin store on Craft 3.3.15 worked fine, and its CP page at first worked fine, but after I went into settings and turned on Usage, I now get the following error when attempting to access the CP page. Would prefer not to publicly post either the full list of content columns in the query or the full stacktrace for security reasons, so I've omitted most of the content columns and all of the stacktrace outside of the actual SQL syntax error, but would be happy to share privately if that's helpful.

SQLSTATE[42000]: Syntax error or access violation: 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 'usage) as 'usage', 'elements'.'id', 'elements'.'fieldLayoutId', 'elements'.'uid'' at line 1 The SQL being executed was: SELECT EXISTS(SELECT (SELECT COUNT(*) FROM (SELECT 'r'.'sourceId', 'r'.'sourceSiteId' FROM 'craft_relations' r WHERE 'r'.'targetId' = 'elements'.'id' GROUP BY 'r'.'sourceId', 'r'.'sourceSiteId') as usage) as 'usage', 'elements'.'id', 'elements'.'fieldLayoutId', 'elements'.'uid', 'elements'.'enabled', 'elements'.'archived', 'elements'.'dateCreated', 'elements'.'dateUpdated', 'elements_sites'.'slug', 'elements_sites'.'siteId', 'elements_sites'.'enabled' AS 'enabledForSite', 'elements_sites'.'uri', 'tags'.'groupId', 'content'.'id' AS 'contentId', 'content'.'title', [...other content columns...] 'elements'.'draftId', 'drafts'.'sourceId' AS 'draftSourceId', 'drafts'.'creatorId' AS 'draftCreatorId', 'drafts'.'name' AS 'draftName', 'drafts'.'notes' AS 'draftNotes' FROM (SELECT (SELECT COUNT(*) FROM (SELECT 'r'.'sourceId', 'r'.'sourceSiteId' FROM 'craft_relations' r WHERE 'r'.'targetId' = 'elements'.'id' GROUP BY 'r'.'sourceId', 'r'.'sourceSiteId') as usage) as 'usage', 'elements'.'id' AS 'elementsId', 'elements_sites'.'id' AS 'elementsSitesId', 'content'.'id' AS 'contentId' FROM 'craft_elements' 'elements' INNER JOIN 'craft_tags' 'tags' ON 'tags'.'id' = 'elements'.'id' INNER JOIN 'craft_elements_sites' 'elements_sites' ON 'elements_sites'.'elementId' = 'elements'.'id' INNER JOIN 'craft_content' 'content' ON ('content'.'elementId' = 'elements'.'id') AND ('content'.'siteId' = 'elements_sites'.'siteId') INNER JOIN 'craft_drafts' 'drafts' ON 'drafts'.'id' = 'elements'.'draftId' WHERE ('elements_sites'.'siteId'='1') AND ('elements'.'archived'=FALSE) AND ('elements'.'dateDeleted' IS NULL) AND ('drafts'.'sourceId' IS NULL) AND ('elements'.'revisionId' IS NULL) ORDER BY 'content'.'title') 'subquery' INNER JOIN 'craft_tags' 'tags' ON 'tags'.'id' = 'subquery'.'elementsId' INNER JOIN 'craft_elements' 'elements' ON 'elements'.'id' = 'subquery'.'elementsId' INNER JOIN 'craft_elements_sites' 'elements_sites' ON 'elements_sites'.'id' = 'subquery'.'elementsSitesId' INNER JOIN 'craft_content' 'content' ON 'content'.'id' = 'subquery'.'contentId' INNER JOIN 'craft_drafts' 'drafts' ON 'drafts'.'id' = 'elements'.'draftId' ORDER BY 'content'.'title')

[khalwat]

^ have run into this same issue on Craft 3.4.0-beta.4

10.1.28-MariaDB-1~xenial

Database Exception – yii\db\Exception

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'usage) as `usage`, `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`' at line 1
The SQL being executed was: SELECT EXISTS(SELECT (SELECT COUNT(*) FROM (SELECT `r`.`sourceId`, `r`.`sourceSiteId` FROM `relations` r WHERE `r`.`targetId` = `elements`.`id` GROUP BY `r`.`sourceId`, `r`.`sourceSiteId`) as usage) as `usage`, `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `tags`.`groupId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_numberIllByCaseDefinitionKnown`, `content`.`field_anyDeaths`, `content`.`field_hospitalized`, `content`.`field_beginningDate`, `content`.`field_confirmedIll`, `content`.`field_country`, `content`.`field_errorHeadline`, `content`.`field_errorText`, `content`.`field_legacyId`, `content`.`field_numberHospitalized`, `content`.`field_numberOfDeaths`, `content`.`field_possibleIll`, `content`.`field_probableIll`, `content`.`field_productSubjectToRecall`, `content`.`field_recallLinks`, `content`.`field_reportReferenceLinks`, `content`.`field_states`, `content`.`field_summary`, `content`.`field_testResults`, `content`.`field_hasTest`, `content`.`field_totalIll`, `content`.`field_useInSearchForm`, `elements`.`draftId`, `drafts`.`sourceId` AS `draftSourceId`, `drafts`.`creatorId` AS `draftCreatorId`, `drafts`.`name` AS `draftName`, `drafts`.`notes` AS `draftNotes`, `drafts`.`trackChanges` AS `draftTrackChanges`, `drafts`.`dateLastMerged` AS `draftDateLastMerged`
FROM (SELECT (SELECT COUNT(*) FROM (SELECT `r`.`sourceId`, `r`.`sourceSiteId` FROM `relations` r WHERE `r`.`targetId` = `elements`.`id` GROUP BY `r`.`sourceId`, `r`.`sourceSiteId`) as usage) as `usage`, `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `tags` `tags` ON `tags`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
INNER JOIN `drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
WHERE (`elements_sites`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`drafts`.`sourceId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `content`.`title`) `subquery`
INNER JOIN `tags` `tags` ON `tags`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
INNER JOIN `drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId`
ORDER BY `content`.`title`)

[VarenSwaab]

I'm having the same problem, nothing but errors, can't use the plugin at all.

[heymarkreeves]

+1

[Tam]

We've removed this feature from MySQL installs. A pull request is welcome if you'd like to re-add the feature.