Author Topic: SQL syntax error after update to 3.2.2  (Read 360 times)

postkat

  • Beginner
  • *
  • Posts: 21
SQL syntax error after update to 3.2.2
« on: May 17, 2017, 07:55:06 am »
Hi all,

I updated to Virtuemart 3.2.2 yesterday and now I get a SQL syntax error on the product detail page.
Quote
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 'WHERE i.`published` = "1" AND j.`element` = "weight_countries" AND (i.`virtuema' at line 6 

Joomla 3.7.X
PHP version 7.0.19
Database version 10.1.23-MariaDB

Help please!

Regards, Dorine

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9085
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: SQL syntax error after update to 3.2.2
« Reply #1 on: May 17, 2017, 12:51:40 pm »
I am sorry, I need a bit more info. The sql is in vmpsplugin.php around line 533. The shown sql is without errors, as far I can see. When I have the full sql,...
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

panagiotiss

  • Beginner
  • *
  • Posts: 31
Re: SQL syntax error after update to 3.2.2
« Reply #2 on: May 17, 2017, 12:56:36 pm »
Hi I get the following sql error  after upgraded to vm 3.2.2

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 'ASC FROM `#__virtuemart_products` as p INNER JOIN `#__virtuemart_products_el' at line 1 SQL=SELECT p.`virtuemart_product_id`, `l`.`product_name`,virtuemart_product_id ASC FROM `#__virtuemart_products` as p INNER JOIN `#__virtuemart_products_el_gr` as l ON l.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `#__virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` WHERE ( `pc`.`virtuemart_category_id` = 26 AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) AND p.`virtuemart_product_id`!="364" AND virtuemart_product_id ASC >= "ΒΡΑΧΙΟΛΙ ΣΤΟΧΟΣ ΑΠΟ ΑΣΗΜΙ 925° ΜΕ ΖΙΡΓΚΟΝ" ORDER BY `p`.virtuemart_product_id ASC LIMIT 1

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9085
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: SQL syntax error after update to 3.2.2
« Reply #3 on: May 17, 2017, 13:21:51 pm »
We did not work on this sql, very strange that you get suddenly an error there. But maybe I found already a solution. Please edit your product model (/administrator/components/com_virtuemart/models/product.php) around line 1841 move the line
Code: [Select]
$orderByNameMain = str_replace(array('DESC','ASC'), '',$orderByNameMain);

to line 1843, so that it looks for the lines 1836 - 1843 like this
Code: [Select]
if($cpos = strpos($orderByName,',')!==false){
$t = explode(',',$orderByName);
if(!empty($t[0])){
$orderByNameMain = $t[0];
}
}
$orderByNameMain = str_replace(array('DESC','ASC'), '',$orderByNameMain);
$orderByNameMain = trim(str_replace('`','',$orderByNameMain));
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

panagiotiss

  • Beginner
  • *
  • Posts: 31
Re: SQL syntax error after update to 3.2.2
« Reply #4 on: May 17, 2017, 13:32:01 pm »
that works for me

what about when update, are you going to put it into next update?

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9085
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: SQL syntax error after update to 3.2.2
« Reply #5 on: May 17, 2017, 17:46:52 pm »
of course. The next update is planned in some days, when we evaluated the final problems with j3.7.1
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

panagiotiss

  • Beginner
  • *
  • Posts: 31
Re: SQL syntax error after update to 3.2.2
« Reply #6 on: May 18, 2017, 09:59:05 am »
thanks

rado85

  • Beginner
  • *
  • Posts: 14
Re: SQL syntax error after update to 3.2.2
« Reply #7 on: June 26, 2017, 14:26:22 pm »
In my case it was because of shipment by payment plugin from VP.


regards