putyourlightson / craft-campaign

Send and manage email campaigns, contacts and mailing lists in Craft CMS.
https://putyourlightson.com/plugins/campaign
Other
63 stars 25 forks source link

Opening contacts gives a server error #488

Closed joostclevis closed 3 months ago

joostclevis commented 3 months ago

Bug Report

When opening the contacts panel we get an server error. If i look in the logs we get this error:

2024-08-14 12:17:56 [web.ERROR] [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 'LIMIT 100) subquery INNER JOIN elements elements ON elements.id = ...' at line 9 The SQL being executed was: SELECTelements.id,elements.canonicalId,elements.fieldLayoutId,elements.uid,elements.enabled,elements.archived,elements.dateLastMerged,elements.dateCreated,elements.dateUpdated,elements_sites.idASsiteSettingsId,elements_sites.siteId,elements_sites.title,elements_sites.slug,elements_sites.uri,elements_sites.content,elements_sites.enabledASenabledForSite,campaign_contacts.userId,campaign_contacts.cid,campaign_contacts.email,campaign_contacts.country,campaign_contacts.geoIp,campaign_contacts.device,campaign_contacts.os,campaign_contacts.client,campaign_contacts.lastActivity,campaign_contacts.verified,campaign_contacts.complained,campaign_contacts.bounced,campaign_contacts.blocked,elements.draftId,drafts.creatorIdASdraftCreatorId,drafts.provisionalASisProvisionalDraft,drafts.nameASdraftName,drafts.notesASdraftNotesFROM (SELECTcidASsubscriptionStatus,elements.idASelementsId,elements_sites.idASsiteSettingsIdFROMelements`elements INNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = elements.id INNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id LEFT JOIN drafts drafts ON drafts.id = elements.draftId WHERE (elements_sites.siteId=1) AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.canonicalId IS NULL) AND ((elements.draftId IS NULL) OR (drafts.provisional=FALSE)) AND ((elements.draftId IS NULL) OR (NOT (elements.canonicalId IS NULL)) OR (drafts.saved=TRUE)) AND (elements.revisionId IS NULL) ORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\"fcf4b2c0-195a-41ad-9819-da654a74339f\"')) LIMIT 100) subquery INNER JOIN elements elements ON elements.id = subquery.elementsId INNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.siteSettingsId INNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = subquery.elementsId LEFT JOIN drafts drafts ON drafts.id = elements.draftId ORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\"fcf4b2c0-195a-41ad-9819-da654a74339f\"')) {"trace":["#0 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException()","#1 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(1186): yii\db\Command->internalExecute()","#2 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(417): yii\db\Command->queryInternal()","#3 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll()","#4 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/db/Query.php(292): yii\db\Query->all()","#5 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1778): craft\db\Query->all()","#6 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/base/Element.php(1334): craft\elements\db\ElementQuery->all()","#7 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/base/Element.php(1269): craft\base\Element::indexElements()","#8 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(855): craft\base\Element::indexHtml()","#9 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(176): craft\controllers\ElementIndexesController->elementResponseData()","#10 [internal function]: craft\controllers\ElementIndexesController->actionGetElements()","#11 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()","#12 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()","#13 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()","#14 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction()","#15 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(650): craft\web\Application->runAction()","#16 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(311): craft\web\Application->_processActionRequest()","#17 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest()","#18 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/public_html/index.php(12): yii\base\Application->run()","#19 {main}"],"memory":8948720,"exception":"[object] (yii\db\Exception(code: 42000): 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 'LIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = ...' at line 9\nThe SQL being executed was: SELECTelements.id,elements.canonicalId,elements.fieldLayoutId,elements.uid,elements.enabled,elements.archived,elements.dateLastMerged,elements.dateCreated,elements.dateUpdated,elements_sites.idASsiteSettingsId,elements_sites.siteId,elements_sites.title,elements_sites.slug,elements_sites.uri,elements_sites.content,elements_sites.enabledASenabledForSite,campaign_contacts.userId,campaign_contacts.cid,campaign_contacts.email,campaign_contacts.country,campaign_contacts.geoIp,campaign_contacts.device,campaign_contacts.os,campaign_contacts.client,campaign_contacts.lastActivity,campaign_contacts.verified,campaign_contacts.complained,campaign_contacts.bounced,campaign_contacts.blocked,elements.draftId,drafts.creatorIdASdraftCreatorId,drafts.provisionalASisProvisionalDraft,drafts.nameASdraftName,drafts.notesASdraftNotes\nFROM (SELECTcidASsubscriptionStatus,elements.idASelementsId,elements_sites.idASsiteSettingsId\nFROMelements`elements\nINNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = elements.id\nINNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id\nLEFT JOIN drafts drafts ON drafts.id = elements.draftId\nWHERE (elements_sites.siteId=1) AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.canonicalId IS NULL) AND ((elements.draftId IS NULL) OR (drafts.provisional=FALSE)) AND ((elements.draftId IS NULL) OR (NOT (elements.canonicalId IS NULL)) OR (drafts.saved=TRUE)) AND (elements.revisionId IS NULL)\nORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\\\"fcf4b2c0-195a-41ad-9819-da654a74339f\\\"'))\nLIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = subquery.elementsId\nINNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.siteSettingsId\nINNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = subquery.elementsId\nLEFT JOIN drafts drafts ON drafts.id = elements.draftId\nORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\\\"fcf4b2c0-195a-41ad-9819-da654a74339f\\\"')) at /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Schema.php:676)\n[previous exception] [object] (PDOException(code: 42000): 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 'LIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = ...' at line 9 at /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php:1320)"} 2024-08-14 12:17:56 [web.WARNING] [application] Request context: {"environment":"production","userId":1,"sessionId":"1uotot9gn3kpjqne42k6levud2","body":"{\"context\":\"index\",\"elementType\":\"putyourlightson\\\\campaign\\\\elements\\\\ContactElement\",\"canHaveDrafts\":true,\"source\":\"*\",\"condition\":null,\"referenceElementId\":null,\"referenceElementSiteId\":null,\"baseCriteria\":{\"status\":null,\"drafts\":null,\"draftOf\":false,\"savedDraftsOnly\":true,\"siteId\":1},\"criteria\":{\"offset\":0,\"limit\":100,\"search\":null},\"disabledElementIds\":[],\"viewState\":{\"mode\":\"table\",\"showHeaderColumn\":true,\"inlineEditing\":false,\"nestedInputNamespace\":\"elementindex-23495\",\"order\":\"field:d26f62f6-d327-4468-875e-e6cd1b732734\",\"sort\":\"asc\"},\"paginated\":true,\"selectable\":false,\"sortable\":false}","vars":{"_GET":{"p":"admin/actions/element-indexes/get-elements","site":"default","v":"1723630676666"},"_FILES":[],"_COOKIE":{"cookie-consent":"76772914a5b990e581f3ec5bf4bff9747e1f8886a12b61d2bfef1d2424324e6da:2:{i:0;s:14:\"cookie-consent\";i:1;s:132:\"{\"consent_uid\":\"70c6f276-27af-4531-80a9-f2b001e731a7\",\"default-necessary\":true,\"default-statistics\":false,\"default-marketing\":false}\";}","1031b8c41dfff97a311a7ac99863bdc5_username":"9ccc78621c8114d1c9c33990a1d918c10de3f1b4a68e7a6eacaad29e38da7dc0a:2:{i:0;s:41:\"1031b8c41dfff97a311a7ac99863bdc5_username\";i:1;s:16:\"dev@brik.digital\";}","critical-css":"67429666","CraftSessionId":"1uotot9gn3kpjqne42k6levud2","theme":"purple-light","CRAFT_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","Craft-85f4c669-6b2b-4771-976e-a4bcdd2bd77b:sidebar":"expanded","1031b8c41dfff97a311a7ac99863bdc5_identity":"431ed7c60bb0696a00e3843f1d1b0376133c15fb7470d133e4f542a140cadc5aa:2:{i:0;s:41:\"1031b8c41dfff97a311a7ac99863bdc5_identity\";i:1;s:159:\"[1,\"[\\\"JOjn4av_ibn7kuGlXmP7v6QVabbjsS1lPDfhYXMVtGcLgYbc6iyg5VF_xslu6UEUAinUm_PCUf6svqQUxrFt52km-4keOgpWHaVf\\\",null,\\\"6013c3d5f7ddd219fad6fa774ec100e5\\\"]\",3600]\";}"},"_SESSION":{"bd62416aa8538ede709019a5e113eea5__flash":[],"1031b8c41dfff97a311a7ac99863bdc5__token":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","1031b8c41dfff97a311a7ac99863bdc5__id":1,"__authKey":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","1031b8c41dfff97a311a7ac99863bdc5__expire":1723634276,"__duration":3600,"__elevated_timeout":1723627563,"bd62416aa8538ede709019a5e113eea5__auth_access":["previewElement:299491","previewDraft:3512","previewDraft:3513"]},"_SERVER":{"PATH":"/projects/vbvd_b9c3b4d/.local/bin:/usr/local/bin:/usr/bin:/bin","USER":"vbvd_b9c3b4d","HOME":"/projects/vbvd_b9c3b4d","HTTP_COOKIE":"cookie-consent=76772914a5b990e581f3ec5bf4bff9747e1f8886a12b61d2bfef1d2424324e6da%3A2%3A%7Bi%3A0%3Bs%3A14%3A%22cookie-consent%22%3Bi%3A1%3Bs%3A132%3A%22%7B%22consent_uid%22%3A%2270c6f276-27af-4531-80a9-f2b001e731a7%22%2C%22default-necessary%22%3Atrue%2C%22default-statistics%22%3Afalse%2C%22default-marketing%22%3Afalse%7D%22%3B%7D; 1031b8c41dfff97a311a7ac99863bdc5_username=9ccc78621c8114d1c9c33990a1d918c10de3f1b4a68e7a6eacaad29e38da7dc0a%3A2%3A%7Bi%3A0%3Bs%3A41%3A%221031b8c41dfff97a311a7ac99863bdc5_username%22%3Bi%3A1%3Bs%3A16%3A%22dev%40brik.digital%22%3B%7D; critical-css=67429666; CraftSessionId=1uotot9gn3kpjqne42k6levud2; theme=purple-light; CRAFT_CSRF_TOKEN=ace03c2afb86d44e1018d7aac730349e0df6550933daf357a0e2ec7afa1ca439a%3A2%3A%7Bi%3A0%3Bs%3A16%3A%22CRAFT_CSRF_TOKEN%22%3Bi%3A1%3Bs%3A147%3A%22M8rO60t8BjA4oy4Cg-1BAMqX3c3dXTOaXZaipbRq%7C174884526d36d0ab590dc7d7928d38412664288a701796887ab6783f33123a8cM8rO60t8BjA4oy4Cg-1BAMqX3c3dXTOaXZaipbRq%7C1%22%3B%7D; Craft-85f4c669-6b2b-4771-976e-a4bcdd2bd77b:sidebar=expanded; 1031b8c41dfff97a311a7ac99863bdc5_identity=431ed7c60bb0696a00e3843f1d1b0376133c15fb7470d133e4f542a140cadc5aa%3A2%3A%7Bi%3A0%3Bs%3A41%3A%221031b8c41dfff97a311a7ac99863bdc5_identity%22%3Bi%3A1%3Bs%3A159%3A%22%5B1%2C%22%5B%5C%22JOjn4av_ibn7kuGlXmP7v6QVabbjsS1lPDfhYXMVtGcLgYbc6iyg5VF_xslu6UEUAinUm_PCUf6svqQUxrFt52km-4keOgpWHaVf%5C%22%2Cnull%2C%5C%226013c3d5f7ddd219fad6fa774ec100e5%5C%22%5D%22%2C3600%5D%22%3B%7D","HTTP_TE":"trailers","HTTP_SEC_FETCH_SITE":"same-origin","HTTP_SEC_FETCH_MODE":"cors","HTTP_SEC_FETCH_DEST":"empty","HTTP_DNT":"1","HTTP_ORIGIN":"https://www.vanbommelvandam.nl","HTTP_CONTENT_LENGTH":"601","HTTP_X_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","HTTP_X_REGISTERED_JS_FILES":"","HTTP_X_REGISTERED_ASSET_BUNDLES":"2c56ad36,22e517a2,aee7f8dc,e505ffd6,6b4d7555,e5e48399,bb2f10a0,815d39ea,fc0bc163,1ccab40d,1e21896b,54698ee0,b842675b,1c3c9add,4b1fd285,d8d08e47,8f00ce04,8768f48b,cf3018d6,7076e3ca,4c2bac09,7ca18ed1,92d8701,4cf48e42,f3074136","HTTP_X_REQUESTED_WITH":"XMLHttpRequest","HTTP_CONTENT_TYPE":"application/json","HTTP_REFERER":"https://www.vanbommelvandam.nl/admin/campaign/contacts/view?site=default&source=*","HTTP_ACCEPT_ENCODING":"gzip, deflate, br, zstd","HTTP_ACCEPT_LANGUAGE":"nl,en;q=0.5","HTTP_ACCEPT":"application/json, text/plain, */*","HTTP_USER_AGENT":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:130.0) Gecko/20100101 Firefox/130.0","HTTP_HOST":"www.vanbommelvandam.nl","REDIRECT_STATUS":"200","SERVER_NAME":"www.vanbommelvandam.nl","SERVER_PORT":"443","SERVER_ADDR":"185.173.20.33","REMOTE_PORT":"49791","REMOTE_ADDR":"84.24.86.30","SERVER_SOFTWARE":"nginx/1.27.0","GATEWAY_INTERFACE":"CGI/1.1","HTTPS":"on","REQUEST_SCHEME":"https","SERVER_PROTOCOL":"HTTP/2.0","DOCUMENT_ROOT":"/projects/vbvd_b9c3b4d/public_html","DOCUMENT_URI":"/index.php","REQUEST_URI":"/index.php?p=admin%2Factions%2Felement-indexes%2Fget-elements&site=default&v=1723630676666","PATH_INFO":"","SCRIPT_NAME":"/index.php","CONTENT_LENGTH":"601","CONTENT_TYPE":"application/json","REQUEST_METHOD":"POST","QUERY_STRING":"p=admin%2Factions%2Felement-indexes%2Fget-elements&site=default&v=1723630676666","SCRIPT_FILENAME":"/projects/vbvd_b9c3b4d/public_html/index.php","FCGI_ROLE":"RESPONDER","PHP_SELF":"","REQUEST_TIME_FLOAT":1723630676.719429,"REQUEST_TIME":1723630676,"ALLOW_UPDATES":"1","ALLOW_ADMIN_CHANGES":"1","BACKUP_ON_UPDATE":"0","DEV_MODE":"0","ENABLE_TEMPLATE_CACHING":"0","ENVIRONMENT":"production","IS_SYSTEM_LIVE":"1","RUN_QUEUE_AUTOMATICALLY":"1","SECURITY_KEY":"••••••••••••••••••••••••••••••••","DB_DRIVER":"mysql","DB_SERVER":"localhost","DB_USER":"vbvd_b9c3b4d","DB_PASSWORD":"••••••••••••••••••••••••","DB_DATABASE":"vbvd_b9c3b4d","DB_SCHEMA":"public","DB_TABLE_PREFIX":"","DB_PORT":"3306","ASSETS_URL":"https://www.vanbommelvandam.nl/","SITE_URL":"https://www.vanbommelvandam.nl/","SITE_NAME":"VBVD","WEB_ROOT_PATH":"/projects/vbvd_b9c3b4d/public_html","S3_KEY_ID":"••••••••••••••••••••","S3_SECRET":"••••••••••••••••••••••••••••••••••••••••","S3_BUCKET":"vbvd-bucket","S3_REGION":"eu-central-1","S3_SUBFOLDER":"","CLOUDFRONT_URL":"https://d2r8u91auwtfcs.cloudfront.net","CLOUDFRONT_DISTRIBUTION_ID":"EGVOCXGPWF9FB","CLOUDFRONT_PATH_PREFIX":"","SERVERLESS_SHARP_CLOUDFRONT_URL":"","REDIS_HOSTNAME":"vbvd_b9c3b4d-redis","REDIS_PORT":"6379","REDIS_PASS":"••••••••••••••••••••••••","PUBLIC_PATH":"/dist/","DEVSERVER_PUBLIC":"http://localhost:8080","DEVSERVER_HOST":"0.0.0.0","DEVSERVER_POLL":"0","DEVSERVER_PORT":"8080","DEVSERVER_HTTPS":"0","TWIGPACK_DEV_SERVER_MANIFEST_PATH":"http://webpack:8080/","TWIGPACK_DEV_SERVER_PUBLIC_PATH":"http://webpack:8080/","DISQUS_PUBLIC_KEY":"","DISQUS_SECRET_KEY":"","GA_TRACKING_ID":"","FAST_CGI_CACHE_PATH":"","DBGP_IDEKEY":"phpstorm","SENTRY_DSN":"https://a9905c8d4307476786e9ae026bd85523@o407654.ingest.sentry.io/6072656","STATUSPAGINATOR_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••"}}} 2024-08-14 12:17:58 [web.ERROR] [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 'LIMIT 100)subqueryINNER JOINelements`elements ON elements.id = ...' at line 10 The SQL being executed was: SELECTelements.id,elements.canonicalId,elements.fieldLayoutId,elements.uid,elements.enabled,elements.archived,elements.dateLastMerged,elements.dateCreated,elements.dateUpdated,elements_sites.idASsiteSettingsId,elements_sites.siteId,elements_sites.title,elements_sites.slug,elements_sites.uri,elements_sites.content,elements_sites.enabledASenabledForSite,campaign_contacts.userId,campaign_contacts.cid,campaign_contacts.email,campaign_contacts.country,campaign_contacts.geoIp,campaign_contacts.device,campaign_contacts.os,campaign_contacts.client,campaign_contacts.lastActivity,campaign_contacts.verified,campaign_contacts.complained,campaign_contacts.bounced,campaign_contacts.blocked,subscriptionStatus,elements.draftId,drafts.creatorIdASdraftCreatorId,drafts.provisionalASisProvisionalDraft,drafts.nameASdraftName,drafts.notesASdraftNotesFROM (SELECTcampaign_contacts_mailinglists.subscriptionStatusASsubscriptionStatus,elements.idASelementsId,elements_sites.idASsiteSettingsIdFROMelements`elements INNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = elements.id INNER JOIN campaign_contacts_mailinglists campaign_contacts_mailinglists ON campaign_contacts.id = campaign_contacts_mailinglists.contactId INNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id LEFT JOIN drafts drafts ON drafts.id = elements.draftId WHERE (campaign_contacts_mailinglists.mailingListId=219352) AND (elements_sites.siteId=1) AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.canonicalId IS NULL) AND ((elements.draftId IS NULL) OR (drafts.provisional=FALSE)) AND ((elements.draftId IS NULL) OR (NOT (elements.canonicalId IS NULL)) OR (drafts.saved=TRUE)) AND (elements.revisionId IS NULL) ORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\"fcf4b2c0-195a-41ad-9819-da654a74339f\"')) LIMIT 100) subquery INNER JOIN elements elements ON elements.id = subquery.elementsId INNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.siteSettingsId INNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = subquery.elementsId LEFT JOIN drafts drafts ON drafts.id = elements.draftId ORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\"fcf4b2c0-195a-41ad-9819-da654a74339f\"')) {"trace":["#0 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException()","#1 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(1186): yii\db\Command->internalExecute()","#2 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php(417): yii\db\Command->queryInternal()","#3 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll()","#4 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/db/Query.php(292): yii\db\Query->all()","#5 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1778): craft\db\Query->all()","#6 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/base/Element.php(1334): craft\elements\db\ElementQuery->all()","#7 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/base/Element.php(1269): craft\base\Element::indexElements()","#8 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(855): craft\base\Element::indexHtml()","#9 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(176): craft\controllers\ElementIndexesController->elementResponseData()","#10 [internal function]: craft\controllers\ElementIndexesController->actionGetElements()","#11 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()","#12 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()","#13 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction()","#14 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction()","#15 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(650): craft\web\Application->runAction()","#16 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/craftcms/cms/src/web/Application.php(311): craft\web\Application->_processActionRequest()","#17 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest()","#18 /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/public_html/index.php(12): yii\base\Application->run()","#19 {main}"],"memory":8975736,"exception":"[object] (yii\db\Exception(code: 42000): 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 'LIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = ...' at line 10\nThe SQL being executed was: SELECTelements.id,elements.canonicalId,elements.fieldLayoutId,elements.uid,elements.enabled,elements.archived,elements.dateLastMerged,elements.dateCreated,elements.dateUpdated,elements_sites.idASsiteSettingsId,elements_sites.siteId,elements_sites.title,elements_sites.slug,elements_sites.uri,elements_sites.content,elements_sites.enabledASenabledForSite,campaign_contacts.userId,campaign_contacts.cid,campaign_contacts.email,campaign_contacts.country,campaign_contacts.geoIp,campaign_contacts.device,campaign_contacts.os,campaign_contacts.client,campaign_contacts.lastActivity,campaign_contacts.verified,campaign_contacts.complained,campaign_contacts.bounced,campaign_contacts.blocked,subscriptionStatus,elements.draftId,drafts.creatorIdASdraftCreatorId,drafts.provisionalASisProvisionalDraft,drafts.nameASdraftName,drafts.notesASdraftNotes\nFROM (SELECTcampaign_contacts_mailinglists.subscriptionStatusASsubscriptionStatus,elements.idASelementsId,elements_sites.idASsiteSettingsId\nFROMelements`elements\nINNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = elements.id\nINNER JOIN campaign_contacts_mailinglists campaign_contacts_mailinglists ON campaign_contacts.id = campaign_contacts_mailinglists.contactId\nINNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id\nLEFT JOIN drafts drafts ON drafts.id = elements.draftId\nWHERE (campaign_contacts_mailinglists.mailingListId=219352) AND (elements_sites.siteId=1) AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.canonicalId IS NULL) AND ((elements.draftId IS NULL) OR (drafts.provisional=FALSE)) AND ((elements.draftId IS NULL) OR (NOT (elements.canonicalId IS NULL)) OR (drafts.saved=TRUE)) AND (elements.revisionId IS NULL)\nORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\\\"fcf4b2c0-195a-41ad-9819-da654a74339f\\\"'))\nLIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = subquery.elementsId\nINNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.siteSettingsId\nINNER JOIN campaign_contacts campaign_contacts ON campaign_contacts.id = subquery.elementsId\nLEFT JOIN drafts drafts ON drafts.id = elements.draftId\nORDER BY JSON_UNQUOTE(JSON_EXTRACT(elements_sites.content, '$.\\\"fcf4b2c0-195a-41ad-9819-da654a74339f\\\"')) at /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Schema.php:676)\n[previous exception] [object] (PDOException(code: 42000): 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 'LIMIT 100) subquery\nINNER JOIN elements elements ON elements.id = `...' at line 10 at /projects/vbvd_b9c3b4d/releases/5a530e0964820142dce6099b055846d16144fefe/vendor/yiisoft/yii2/db/Command.php:1320)"} 2024-08-14 12:17:58 [web.WARNING] [application] Request context: {"environment":"production","userId":1,"sessionId":"1uotot9gn3kpjqne42k6levud2","body":"{\"context\":\"index\",\"elementType\":\"putyourlightson\\campaign\\elements\\ContactElement\",\"canHaveDrafts\":true,\"source\":\"mailingList:24d36f44-9e99-4e32-a121-a72ae8444572\",\"condition\":null,\"referenceElementId\":null,\"referenceElementSiteId\":null,\"baseCriteria\":{\"status\":null,\"drafts\":null,\"draftOf\":false,\"savedDraftsOnly\":true,\"mailingListId\":219352,\"siteId\":1},\"criteria\":{\"offset\":0,\"limit\":100,\"search\":null},\"disabledElementIds\":[],\"viewState\":{\"mode\":\"table\",\"showHeaderColumn\":true,\"inlineEditing\":false,\"nestedInputNamespace\":\"elementindex-23495\",\"order\":\"field:d26f62f6-d327-4468-875e-e6cd1b732734\",\"sort\":\"asc\"},\"paginated\":true,\"selectable\":false,\"sortable\":false}","vars":{"_GET":{"p":"admin/actions/element-indexes/get-elements","site":"default","v":"1723630678697"},"_FILES":[],"_COOKIE":{"cookie-consent":"76772914a5b990e581f3ec5bf4bff9747e1f8886a12b61d2bfef1d2424324e6da:2:{i:0;s:14:\"cookie-consent\";i:1;s:132:\"{\"consent_uid\":\"70c6f276-27af-4531-80a9-f2b001e731a7\",\"default-necessary\":true,\"default-statistics\":false,\"default-marketing\":false}\";}","1031b8c41dfff97a311a7ac99863bdc5_username":"9ccc78621c8114d1c9c33990a1d918c10de3f1b4a68e7a6eacaad29e38da7dc0a:2:{i:0;s:41:\"1031b8c41dfff97a311a7ac99863bdc5_username\";i:1;s:16:\"dev@brik.digital\";}","critical-css":"67429666","CraftSessionId":"1uotot9gn3kpjqne42k6levud2","theme":"purple-light","CRAFT_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","Craft-85f4c669-6b2b-4771-976e-a4bcdd2bd77b:sidebar":"expanded","1031b8c41dfff97a311a7ac99863bdc5_identity":"431ed7c60bb0696a00e3843f1d1b0376133c15fb7470d133e4f542a140cadc5aa:2:{i:0;s:41:\"1031b8c41dfff97a311a7ac99863bdc5_identity\";i:1;s:159:\"[1,\"[\\"JOjn4av_ibn7kuGlXmP7v6QVabbjsS1lPDfhYXMVtGcLgYbc6iyg5VF_xslu6UEUAinUm_PCUf6svqQUxrFt52km-4keOgpWHaVf\\",null,\\"6013c3d5f7ddd219fad6fa774ec100e5\\"]\",3600]\";}"},"_SESSION":{"bd62416aa8538ede709019a5e113eea5flash":[],"1031b8c41dfff97a311a7ac99863bdc5token":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","1031b8c41dfff97a311a7ac99863bdc5id":1,"authKey":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","1031b8c41dfff97a311a7ac99863bdc5expire":1723634278,"duration":3600,"__elevated_timeout":1723627563,"bd62416aa8538ede709019a5e113eea5__auth_access":["previewElement:299491","previewDraft:3512","previewDraft:3513"]},"_SERVER":{"PATH":"/projects/vbvd_b9c3b4d/.local/bin:/usr/local/bin:/usr/bin:/bin","USER":"vbvd_b9c3b4d","HOME":"/projects/vbvd_b9c3b4d","HTTP_COOKIE":"cookie-consent=76772914a5b990e581f3ec5bf4bff9747e1f8886a12b61d2bfef1d2424324e6da%3A2%3A%7Bi%3A0%3Bs%3A14%3A%22cookie-consent%22%3Bi%3A1%3Bs%3A132%3A%22%7B%22consent_uid%22%3A%2270c6f276-27af-4531-80a9-f2b001e731a7%22%2C%22default-necessary%22%3Atrue%2C%22default-statistics%22%3Afalse%2C%22default-marketing%22%3Afalse%7D%22%3B%7D; 1031b8c41dfff97a311a7ac99863bdc5_username=9ccc78621c8114d1c9c33990a1d918c10de3f1b4a68e7a6eacaad29e38da7dc0a%3A2%3A%7Bi%3A0%3Bs%3A41%3A%221031b8c41dfff97a311a7ac99863bdc5_username%22%3Bi%3A1%3Bs%3A16%3A%22dev%40brik.digital%22%3B%7D; critical-css=67429666; CraftSessionId=1uotot9gn3kpjqne42k6levud2; theme=purple-light; CRAFT_CSRF_TOKEN=ace03c2afb86d44e1018d7aac730349e0df6550933daf357a0e2ec7afa1ca439a%3A2%3A%7Bi%3A0%3Bs%3A16%3A%22CRAFT_CSRF_TOKEN%22%3Bi%3A1%3Bs%3A147%3A%22M8rO60t8BjA4oy4Cg-1BAMqX3c3dXTOaXZaipbRq%7C174884526d36d0ab590dc7d7928d38412664288a701796887ab6783f33123a8cM8rO60t8BjA4oy4Cg-1BAMqX3c3dXTOaXZaipbRq%7C1%22%3B%7D; Craft-85f4c669-6b2b-4771-976e-a4bcdd2bd77b:sidebar=expanded; 1031b8c41dfff97a311a7ac99863bdc5_identity=431ed7c60bb0696a00e3843f1d1b0376133c15fb7470d133e4f542a140cadc5aa%3A2%3A%7Bi%3A0%3Bs%3A41%3A%221031b8c41dfff97a311a7ac99863bdc5_identity%22%3Bi%3A1%3Bs%3A159%3A%22%5B1%2C%22%5B%5C%22JOjn4av_ibn7kuGlXmP7v6QVabbjsS1lPDfhYXMVtGcLgYbc6iyg5VF_xslu6UEUAinUm_PCUf6svqQUxrFt52km-4keOgpWHaVf%5C%22%2Cnull%2C%5C%226013c3d5f7ddd219fad6fa774ec100e5%5C%22%5D%22%2C3600%5D%22%3B%7D","HTTP_TE":"trailers","HTTP_SEC_FETCH_SITE":"same-origin","HTTP_SEC_FETCH_MODE":"cors","HTTP_SEC_FETCH_DEST":"empty","HTTP_DNT":"1","HTTP_ORIGIN":"https://www.vanbommelvandam.nl","HTTP_CONTENT_LENGTH":"671","HTTP_X_CSRF_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••","HTTP_X_REGISTERED_JS_FILES":"","HTTP_X_REGISTERED_ASSET_BUNDLES":"2c56ad36,22e517a2,aee7f8dc,e505ffd6,6b4d7555,e5e48399,bb2f10a0,815d39ea,fc0bc163,1ccab40d,1e21896b,54698ee0,b842675b,1c3c9add,4b1fd285,d8d08e47,8f00ce04,8768f48b,cf3018d6,7076e3ca,4c2bac09,7ca18ed1,92d8701,4cf48e42,f3074136","HTTP_X_REQUESTED_WITH":"XMLHttpRequest","HTTP_CONTENT_TYPE":"application/json","HTTP_REFERER":"https://www.vanbommelvandam.nl/admin/campaign/contacts/view?site=default&source=mailingList%3A24d36f44-9e99-4e32-a121-a72ae8444572","HTTP_ACCEPT_ENCODING":"gzip, deflate, br, zstd","HTTP_ACCEPT_LANGUAGE":"nl,en;q=0.5","HTTP_ACCEPT":"application/json, text/plain, /","HTTP_USER_AGENT":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:130.0) Gecko/20100101 Firefox/130.0","HTTP_HOST":"www.vanbommelvandam.nl","REDIRECT_STATUS":"200","SERVER_NAME":"www.vanbommelvandam.nl","SERVER_PORT":"443","SERVER_ADDR":"185.173.20.33","REMOTE_PORT":"49791","REMOTE_ADDR":"84.24.86.30","SERVER_SOFTWARE":"nginx/1.27.0","GATEWAY_INTERFACE":"CGI/1.1","HTTPS":"on","REQUEST_SCHEME":"https","SERVER_PROTOCOL":"HTTP/2.0","DOCUMENT_ROOT":"/projects/vbvd_b9c3b4d/public_html","DOCUMENT_URI":"/index.php","REQUEST_URI":"/index.php?p=admin%2Factions%2Felement-indexes%2Fget-elements&site=default&v=1723630678697","PATH_INFO":"","SCRIPT_NAME":"/index.php","CONTENT_LENGTH":"671","CONTENT_TYPE":"application/json","REQUEST_METHOD":"POST","QUERY_STRING":"p=admin%2Factions%2Felement-indexes%2Fget-elements&site=default&v=1723630678697","SCRIPT_FILENAME":"/projects/vbvd_b9c3b4d/public_html/index.php","FCGI_ROLE":"RESPONDER","PHP_SELF":"","REQUEST_TIME_FLOAT":1723630678.759995,"REQUEST_TIME":1723630678,"ALLOW_UPDATES":"1","ALLOW_ADMIN_CHANGES":"1","BACKUP_ON_UPDATE":"0","DEV_MODE":"0","ENABLE_TEMPLATE_CACHING":"0","ENVIRONMENT":"production","IS_SYSTEM_LIVE":"1","RUN_QUEUE_AUTOMATICALLY":"1","SECURITY_KEY":"••••••••••••••••••••••••••••••••","DB_DRIVER":"mysql","DB_SERVER":"localhost","DB_USER":"vbvd_b9c3b4d","DB_PASSWORD":"••••••••••••••••••••••••","DB_DATABASE":"vbvd_b9c3b4d","DB_SCHEMA":"public","DB_TABLE_PREFIX":"","DB_PORT":"3306","ASSETS_URL":"https://www.vanbommelvandam.nl/","SITE_URL":"https://www.vanbommelvandam.nl/","SITE_NAME":"VBVD","WEB_ROOT_PATH":"/projects/vbvd_b9c3b4d/public_html","S3_KEY_ID":"••••••••••••••••••••","S3_SECRET":"••••••••••••••••••••••••••••••••••••••••","S3_BUCKET":"vbvd-bucket","S3_REGION":"eu-central-1","S3_SUBFOLDER":"","CLOUDFRONT_URL":"https://d2r8u91auwtfcs.cloudfront.net","CLOUDFRONT_DISTRIBUTION_ID":"EGVOCXGPWF9FB","CLOUDFRONT_PATH_PREFIX":"","SERVERLESS_SHARP_CLOUDFRONT_URL":"","REDIS_HOSTNAME":"vbvd_b9c3b4d-redis","REDIS_PORT":"6379","REDIS_PASS":"••••••••••••••••••••••••","PUBLIC_PATH":"/dist/","DEVSERVER_PUBLIC":"http://localhost:8080","DEVSERVER_HOST":"0.0.0.0","DEVSERVER_POLL":"0","DEVSERVER_PORT":"8080","DEVSERVER_HTTPS":"0","TWIGPACK_DEV_SERVER_MANIFEST_PATH":"http://webpack:8080/","TWIGPACK_DEV_SERVER_PUBLIC_PATH":"http://webpack:8080/","DISQUS_PUBLIC_KEY":"","DISQUS_SECRET_KEY":"","GA_TRACKING_ID":"","FAST_CGI_CACHE_PATH":"","DBGP_IDEKEY":"phpstorm","SENTRY_DSN":"https://a9905c8d4307476786e9ae026bd85523@o407654.ingest.sentry.io/6072656","STATUSPAGINATOR_TOKEN":"••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••"}}}

Plugin Version

3.4.1

Craft CMS Version

5.2.7

PHP Version

8.2.22

bencroker commented 3 months ago

Craft 5.2.8 fixed an SQL error that affected MariaDB. Can you please update to the latest versions of both Craft and Campaign and see if that resolves the issue for you?

joostclevis commented 3 months ago

Updating to the latest Craft version fixed this issue.