My website is running on Fortrabbit with the following settings: PHP version 7.4.15 OS version Linux 4.15.0-50-generic Database driver & version MySQL 8.0.23 Image driver & version Imagick 3.4.4 (ImageMagick 7.0.10-62) Craft edition & version Craft Pro 3.7.13 Yii version 2.0.43 Twig version v2.14.7 Guzzle version 7.3.0
I seem to get this mysql datetime value error whenever I have this code run: {% for order in craft.orders.isCompleted().isPaid().dateOrdered(">= #{product.productCountSalesFrom}").hasPurchasables(product.tickets).limit(9999).withLineItems().all() %}, which i suspect might have to do with .dateOrdered(">= #{product.productCountSalesFrom}") this part particularly, where productCountSalesFrom is a date time field in Craft CMS.
The error I get is as follows: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: '' in /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php:1302 Stack trace: #0 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute() #1 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute('SELECT `element...') #2 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(410): yii\db\Command->queryInternal('fetchAll', NULL) #3 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll() #4 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/db/Query.php(152): yii\db\Query->all(NULL) #5 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1593): craft\db\Query->all(NULL) #6 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Extension/CoreExtension.php(1511): craft\elements\db\ElementQuery->all() #7 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/helpers/Template.php(106): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method', false, false) #8 /srv/app/testingamss2/htdocs/storage/runtime/compiled_templates/a3/a3e2c9b6d960ab31b4c5bf12daf4fa2b1ef942569d0745ace091d41396f04418.php(60): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method') #9 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(405): __TwigTemplate_d24e2c5e2158ee994ad3eda22d2f0682868b9f509316210fce079587488718b4->doDisplay(Array, Array) #10 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(378): Twig\Template->displayWithErrorHandling(Array, Array) #11 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(390): Twig\Template->display(Array) #12 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/TemplateWrapper.php(45): Twig\Template->render(Array, Array) #13 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Environment.php(318): Twig\TemplateWrapper->render(Array) #14 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(408): Twig\Environment->render('events/generalE...', Array) #15 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(461): craft\web\View->renderTemplate('events/generalE...', Array) #16 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(257): craft\web\View->renderPageTemplate('events/generalE...', Array, 'site') #17 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/controllers/TemplatesController.php(100): craft\web\Controller->renderTemplate('events/generalE...', Array) #18 [internal function]: craft\controllers\TemplatesController->actionRender('events/generalE...', Array) #19 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array) #20 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array) #21 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('render', Array) #22 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('render', Array) #23 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(277): yii\base\Module->runAction('templates/rende...', Array) #24 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/web/Application.php(104): craft\web\Application->runAction('templates/rende...', Array) #25 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request)) #26 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request)) #27 /srv/app/testingamss2/htdocs/web/index.php(26): yii\base\Application->run() #28 {main}
Next yii\db\Exception: SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: ''
The SQL being executed was: SELECT elements.id, elements.fieldLayoutId, elements.uid, elements.enabled, elements.archived, elements.dateCreated, elements.dateUpdated, elements_sites.id AS siteSettingsId, elements_sites.slug, elements_sites.siteId, elements_sites.uri, elements_sites.enabled AS enabledForSite, elements.canonicalId, elements.dateLastMerged, commerce_orders.id, commerce_orders.number, commerce_orders.reference, commerce_orders.couponCode, commerce_orders.orderStatusId, commerce_orders.dateOrdered, commerce_orders.email, commerce_orders.isCompleted, commerce_orders.datePaid, commerce_orders.currency, commerce_orders.paymentCurrency, commerce_orders.lastIp, commerce_orders.orderLanguage, commerce_orders.message, commerce_orders.returnUrl, commerce_orders.cancelUrl, commerce_orders.billingAddressId, commerce_orders.shippingAddressId, commerce_orders.estimatedBillingAddressId, commerce_orders.estimatedShippingAddressId, commerce_orders.shippingMethodHandle, commerce_orders.gatewayId, commerce_orders.paymentSourceId, commerce_orders.customerId, commerce_orders.dateUpdated, commerce_orders.registerUserOnOrderComplete, commerce_orders.recalculationMode, commerce_orders.origin, commerce_orders.dateAuthorized, commerce_orders.totalPrice AS storedTotalPrice, commerce_orders.totalPaid AS storedTotalPaid, commerce_orders.itemTotal AS storedItemTotal, commerce_orders.totalDiscount AS storedTotalDiscount, commerce_orders.totalShippingCost AS storedTotalShippingCost, commerce_orders.totalTax AS storedTotalTax, commerce_orders.totalTaxIncluded AS storedTotalTaxIncluded, commerce_orders.shippingMethodName, commerce_orders.itemSubtotal AS storedItemSubtotal, commerce_orders.orderSiteId, content.id AS contentId, content.field_newsAuthor, content.field_productAvailableFrom, content.field_productAvailableUntil, content.field_benefit, content.field_benefitInstructions, content.field_benefitLocation, content.field_calendarUrl_kctenffu, content.field_colour, content.field_productCountSalesFrom, content.field_userDob, content.field_linkDescription, content.field_hideFromNavigation, content.field_userHonours, content.field_sponsorMajorPartner_msnpclqg, content.field_maximumQuantity_novxyhdv, content.field_membershipRequired_akszozdk, content.field_productMembershipRequired, content.field_productOnePerMember, content.field_userPersonalEmail, content.field_userPhoneNumber, content.field_userRegisterAmasa, content.field_userRegisterAmsf, content.field_sponsorUrl_nmcpasgw, content.field_ticketName_rbqbnltw, content.field_userYearLevel
FROM (SELECT elements.id AS elementsId, elements_sites.id AS elementsSitesId, content.id AS contentId
FROM craft_elements elements
INNER JOIN craft_commerce_orders commerce_orders ON commerce_orders.id = elements.id
LEFT JOIN craft_commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId
LEFT JOIN craft_commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId
INNER JOIN craft_elements_sites elements_sites ON elements_sites.elementId = elements.id
INNER JOIN craft_content content ON content.elementId = elements.id
WHERE (commerce_orders.isCompleted=TRUE) AND (commerce_orders.dateOrdered >= '') AND (commerce_orders.totalPaid >= commerce_orders.totalPrice) AND (EXISTS (SELECT *
FROM craft_commerce_lineitems lineitems
WHERE (lineitems.orderId = elements.id) AND (lineitems.purchasableId IN (11141, 16448)))) AND (elements.type='craft\commerce\elements\Order') AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.draftId IS NULL) AND (elements.revisionId IS NULL)
ORDER BY commerce_orders.id
LIMIT 9999) subquery
INNER JOIN craft_commerce_orders commerce_orders ON commerce_orders.id = subquery.elementsId
LEFT JOIN craft_commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId
LEFT JOIN craft_commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId
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
ORDER BY commerce_orders.id in /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Schema.php:678
Stack trace:
#0 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'SELECT element...') #1 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute('SELECT element...')
#2 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(410): yii\db\Command->queryInternal('fetchAll', NULL)
#3 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll()
#4 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/db/Query.php(152): yii\db\Query->all(NULL)
#5 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1593): craft\db\Query->all(NULL)
#6 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Extension/CoreExtension.php(1511): craft\elements\db\ElementQuery->all()
#7 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/helpers/Template.php(106): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method', false, false)
#8 /srv/app/testingamss2/htdocs/storage/runtime/compiled_templates/a3/a3e2c9b6d960ab31b4c5bf12daf4fa2b1ef942569d0745ace091d41396f04418.php(60): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method')
#9 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(405): __TwigTemplate_d24e2c5e2158ee994ad3eda22d2f0682868b9f509316210fce079587488718b4->doDisplay(Array, Array)
#10 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(378): Twig\Template->displayWithErrorHandling(Array, Array)
#11 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(390): Twig\Template->display(Array)
#12 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/TemplateWrapper.php(45): Twig\Template->render(Array, Array)
#13 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Environment.php(318): Twig\TemplateWrapper->render(Array)
#14 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(408): Twig\Environment->render('events/generalE...', Array)
#15 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(461): craft\web\View->renderTemplate('events/generalE...', Array)
#16 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(257): craft\web\View->renderPageTemplate('events/generalE...', Array, 'site')
#17 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/controllers/TemplatesController.php(100): craft\web\Controller->renderTemplate('events/generalE...', Array)
#18 [internal function]: craft\controllers\TemplatesController->actionRender('events/generalE...', Array)
#19 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#20 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array)
#21 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('render', Array)
#22 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('render', Array)
#23 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(277): yii\base\Module->runAction('templates/rende...', Array)
#24 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/web/Application.php(104): craft\web\Application->runAction('templates/rende...', Array)
#25 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request))
#26 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request))
#27 /srv/app/testingamss2/htdocs/web/index.php(26): yii\base\Application->run()
#28 {main}
Additional Information:
Array
(
[0] => HY000
[1] => 1525
[2] => Incorrect DATETIME value: ''
)
I do not get this error on my local server, however, mysql on my local sever is on 5.5.5, and I suspect it may be because of this difference in mysql version.
Any help on how to resolve this would be much appreciated! Thankyou in advance
