Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"Syntax error or access violation: 1064" when placing an order with 19.5.0-rc3 #3213

Closed
andyt opened this issue Apr 24, 2023 · 10 comments · Fixed by #3227
Closed

"Syntax error or access violation: 1064" when placing an order with 19.5.0-rc3 #3213

andyt opened this issue Apr 24, 2023 · 10 comments · Fixed by #3227

Comments

@andyt
Copy link

andyt commented Apr 24, 2023

Preconditions (*)

  1. OpenMage 19.5.0-rc3
  2. PHP 8.2.5

Steps to reproduce (*)

  1. Place an order

Expected result (*)

  1. No exceptions

Actual result (*)

  1. Order appears tp be placed, but there are exceptions in the exception.log file
> 2023-04-24T10:17:51+00:00 ERR (3): 
> PDOException: 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 '.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WH' at line 2 in /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php:228
> Stack trace:
> #0 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
> #1 /var/www/htdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(98): Zend_Db_Statement_Pdo->_execute(Array)
> #2 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
> #3 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
> #4 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Pdo/Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT ROUND(((...', Array)
> #5 /var/www/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(486): Zend_Db_Adapter_Pdo_Abstract->query('SELECT ROUND(((...', Array)
> #6 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(797): Varien_Db_Adapter_Pdo_Mysql->query('SELECT ROUND(((...', Array)
> #7 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(315): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))
> #8 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(542): Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices(Object(Mage_Catalog_Model_Layer_Filter_Price), 22.0, 14.0, 0, NULL)
> #9 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(347): Mage_Catalog_Model_Layer_Filter_Price->loadPrices(22.0, 14.0, 0, NULL)
> #10 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(534): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->_findPriceSeparator(1)
> #11 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(277): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->calculateSeparators()
> #12 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(297): Mage_Catalog_Model_Layer_Filter_Price->_getCalculatedItemsData()
> #13 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(142): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
> #14 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(111): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
> #15 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(100): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
> #16 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(123): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
> #17 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(209): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
> #18 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(224): Mage_Catalog_Block_Layer_View->canShowOptions()
> #19 /var/www/htdocs/app/design/frontend/ultimo/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock()
> #20 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #21 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #22 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #23 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #24 /var/www/htdocs/app/code/core/Mage/Core/Block/Text/List.php(36): Mage_Core_Block_Abstract->toHtml()
> #25 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Text_List->_toHtml()
> #26 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(644): Mage_Core_Block_Abstract->toHtml()
> #27 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(588): Mage_Core_Block_Abstract->_getChildHtml('left', true)
> #28 /var/www/htdocs/app/design/frontend/ultimo/default/template/page/2columns-left.phtml(36): Mage_Core_Block_Abstract->getChildHtml('left')
> #29 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #30 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #31 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #32 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #33 /var/www/htdocs/app/code/core/Mage/Core/Model/Layout.php(574): Mage_Core_Block_Abstract->toHtml()
> #34 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(391): Mage_Core_Model_Layout->getOutput()
> #35 /var/www/htdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(167): Mage_Core_Controller_Varien_Action->renderLayout()
> #36 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(422): Mage_Catalog_CategoryController->viewAction()
> #37 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(256): Mage_Core_Controller_Varien_Action->dispatch('view')
> #38 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(182): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
> #39 /var/www/htdocs/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
> #40 /var/www/htdocs/app/Mage.php(739): Mage_Core_Model_App->run(Array)
> #41 /var/www/htdocs/index.php(55): Mage::run('freeborn', 'store')
> #42 {main}
> 
> Next Zend_Db_Statement_Exception: 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 '.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WH' at line 2, query was: SELECT ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))) * 1, 2) AS `min_price_expr` FROM `catalog_product_index_price` AS `e`
>  INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = 4 WHERE 1=1 AND ( e.website_id = 1 ) AND ( e.customer_group_id = 1) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)) IS NOT NULL) AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000  ELSE 0 END)) >= -0.005000) ORDER BY `(e`.`min_price -(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END)+((e`.`min_price-(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000  ELSE 0 END))` ASC LIMIT 22 OFFSET 14 in /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php:235
> Stack trace:
> #0 /var/www/htdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(98): Zend_Db_Statement_Pdo->_execute(Array)
> #1 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Statement.php(303): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
> #2 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
> #3 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Pdo/Abstract.php(244): Zend_Db_Adapter_Abstract->query('SELECT ROUND(((...', Array)
> #4 /var/www/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(486): Zend_Db_Adapter_Pdo_Abstract->query('SELECT ROUND(((...', Array)
> #5 /var/www/vendor/shardj/zf1-future/library/Zend/Db/Adapter/Abstract.php(797): Varien_Db_Adapter_Pdo_Mysql->query('SELECT ROUND(((...', Array)
> #6 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(315): Zend_Db_Adapter_Abstract->fetchCol(Object(Varien_Db_Select))
> #7 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(542): Mage_Catalog_Model_Resource_Layer_Filter_Price->loadPrices(Object(Mage_Catalog_Model_Layer_Filter_Price), 22.0, 14.0, 0, NULL)
> #8 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(347): Mage_Catalog_Model_Layer_Filter_Price->loadPrices(22.0, 14.0, 0, NULL)
> #9 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price/Algorithm.php(534): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->_findPriceSeparator(1)
> #10 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(277): Mage_Catalog_Model_Layer_Filter_Price_Algorithm->calculateSeparators()
> #11 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(297): Mage_Catalog_Model_Layer_Filter_Price->_getCalculatedItemsData()
> #12 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(142): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
> #13 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(111): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
> #14 /var/www/htdocs/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(100): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
> #15 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(123): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
> #16 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(209): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
> #17 /var/www/htdocs/app/code/core/Mage/Catalog/Block/Layer/View.php(224): Mage_Catalog_Block_Layer_View->canShowOptions()
> #18 /var/www/htdocs/app/design/frontend/ultimo/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock()
> #19 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #20 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #21 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #22 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #23 /var/www/htdocs/app/code/core/Mage/Core/Block/Text/List.php(36): Mage_Core_Block_Abstract->toHtml()
> #24 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Text_List->_toHtml()
> #25 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(644): Mage_Core_Block_Abstract->toHtml()
> #26 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(588): Mage_Core_Block_Abstract->_getChildHtml('left', true)
> #27 /var/www/htdocs/app/design/frontend/ultimo/default/template/page/2columns-left.phtml(36): Mage_Core_Block_Abstract->getChildHtml('left')
> #28 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(251): include('/var/www/htdocs...')
> #29 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(288): Mage_Core_Block_Template->fetchView('frontend/ultimo...')
> #30 /var/www/htdocs/app/code/core/Mage/Core/Block/Template.php(301): Mage_Core_Block_Template->renderView()
> #31 /var/www/htdocs/app/code/core/Mage/Core/Block/Abstract.php(927): Mage_Core_Block_Template->_toHtml()
> #32 /var/www/htdocs/app/code/core/Mage/Core/Model/Layout.php(574): Mage_Core_Block_Abstract->toHtml()
> #33 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(391): Mage_Core_Model_Layout->getOutput()
> #34 /var/www/htdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(167): Mage_Core_Controller_Varien_Action->renderLayout()
> #35 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(422): Mage_Catalog_CategoryController->viewAction()
> #36 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(256): Mage_Core_Controller_Varien_Action->dispatch('view')
> #37 /var/www/htdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(182): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
> #38 /var/www/htdocs/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
> #39 /var/www/htdocs/app/Mage.php(739): Mage_Core_Model_App->run(Array)
> #40 /var/www/htdocs/index.php(55): Mage::run('freeborn', 'store')
> #41 {main}
> 
@ADDISON74
Copy link
Contributor

I used the latest version of OpenMage (main branch) with Magento Sample Pack. I placed the order and looked in the logs. Apart from those reported here #3184, I did not find any of those reported by you.

We cannot investigate particular cases that appear in custom installations, with extensions. It is known that major changes have been made in OpenMage to make it compatible with the latest version of PHP and certain extensions can no longer work. As you can see from you report, there are most likely errors in the extensions (custom SQL queries., Try to disable all of them except those related to OpenMage and analyze if the errors still occur.

If no one else confirms this report we will close it within a week from now.

@fballiano
Copy link
Contributor

mmmm the only thing I see is a tax_class_id = 2, maybe we should check with real tax amounts just to be sure :-\

@andyt
Copy link
Author

andyt commented Apr 24, 2023

Thanks @ADDISON74. The stack trace doesn't appear to be running through any extensions, and I'm not aware of any on this installation relating to taxation. I've checked the only other mention of third party code, which is the theme, and that doesn't appear to be a culprit.

However, I'll try disabling some extensions and see what happens.

Thanks for investigating.

I used the latest version of OpenMage (main branch) with Magento Sample Pack. I placed the order and looked in the logs. Apart from those reported here #3184, I did not find any of those reported by you.

We cannot investigate particular cases that appear in custom installations, with extensions. It is known that major changes have been made in OpenMage to make it compatible with the latest version of PHP and certain extensions can no longer work. As you can see from you report, there are most likely errors in the extensions (custom SQL queries., Try to disable all of them except those related to OpenMage and analyze if the errors still occur.

If no one else confirms this report we will close it within a week from now.

@fballiano
Copy link
Contributor

It could be an observer, it wouldnt be visible in the stack in some cases

@luigifab
Copy link
Contributor

I think this is that.
Try to search ELSE 0 END in all of your files, if you are lucky, you will find the culprit.

@elidrissidev
Copy link
Member

Most likely originating from the line below. Try wrapping the sql code in a Zend_Db_Expr and see if it will solve your issue.

$$rateVariable = "CASE {$taxClassField} {$$rateVariable} ELSE 0 END";

@andyt
Copy link
Author

andyt commented May 3, 2023

Thanks @elidrissidev. This fixed the issue.

Most likely originating from the line below. Try wrapping the sql code in a Zend_Db_Expr and see if it will solve your issue.

$$rateVariable = "CASE {$taxClassField} {$$rateVariable} ELSE 0 END";

@elidrissidev
Copy link
Member

@andyt Did you test with my PR #3227?

@andyt
Copy link
Author

andyt commented May 3, 2023

@elidrissidev No, I simply wrapped the line you suggested in Data.php with a new Zend_DB_Expr. Your PR doesn't make any changes to that file.

@elidrissidev
Copy link
Member

Could you try that fix instead? If it solves the issue it will make it to a new RC release soon.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants