Database error - invalid query???

I am getting a number of my sections displaying only an error page.

PLEASE take a look and tell me what the best approach to correcting this will be?



removed





thanks

also viewing products in the backend is not working.

same sql error.

ALSO, can anyone tell me is there a way to hide this type of error from visitors?

Can it be set to only show if you are logged in as admin… and display a ‘system maintenance’ page if you are just a visitor??

I have had to close my store… so if you are interested here is the error that is being displayed: Database error: 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 '' at line 1 (1064)
Invalid query: SELECT SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, products.tracking, products.feature_comparison, products.zero_price_action, products.product_type, products.tax_ids, products.weight, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, min_qty, max_qty, products.qty_step, products.list_qty_count, products.is_edp, avail_since, buy_in_advance, products.product_code, products.amount, MIN(prices.price) as price, products.status, products.list_price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, IF(products.age_verification = 'Y', 'Y', IF(cscart_categories.age_verification = 'Y', 'Y', cscart_categories.parent_age_verification)) as age_verification, IF(products.age_limit > cscart_categories.age_limit, IF(products.age_limit > cscart_categories.parent_age_limit, products.age_limit, cscart_categories.parent_age_limit), IF(cscart_categories.age_limit > cscart_categories.parent_age_limit, cscart_categories.age_limit, cscart_categories.parent_age_limit)) as age_limit, cscart_seo_names.name as seo_name FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'EN' LEFT JOIN cscart_product_options_inventory as inventory ON inventory.product_id = products.product_id LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price AND prices_2.usergroup_id IN (0, 0, 1) INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') AND products.status IN ('A') LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = products.product_id AND cscart_product_sales.category_id = products_categories.category_id LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' WHERE 1 AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0) AND prices.usergroup_id IN (0, 0, 1) AND prices_2.price IS NULL GROUP BY products.product_id ORDER BY products.timestamp desc LIMIT 0, 4

Backtrace:
File: /var/www/vhosts/site/httpdocs/gear/core/fn.database.php
Line: 310
Function: db_error
File: /var/www/vhosts/site/httpdocs/gear/core/fn.database.php
Line: 37
Function: db_query
File: /var/www/vhosts/site/httpdocs/gear/core/fn.catalog.php
Line: 3183
Function: db_get_array
File: /var/www/vhosts/site/httpdocs/gear/core/fn.cms.php
Line: 659
Function: fn_get_products
File: /var/www/vhosts/site/httpdocs/gear/core/templater_plugins/function.block.php
Line: 46
Function: fn_get_block_items
File: /var/www/vhosts/site/httpdocs/gear/var/compiled/customer/%%ED^ED1^ED12187B%%central.tpl.php
Line: 9
Function: smarty_function_block
File: /var/www/vhosts/site/httpdocs/gear/core/class.templater.php
Line: 133
Function: include
File: /var/www/vhosts/site/httpdocs/gear/var/compiled/customer/%%72^72D^72DAF6E8%%main.tpl.php
Line: 85
Function: _smarty_include
File: /var/www/vhosts/site/httpdocs/gear/core/class.templater.php
Line: 133
Function: include
File: /var/www/vhosts/site/httpdocs/gear/var/compiled/customer/%%45^45E^45E480CD%%index.tpl.php
Line: 405
Function: _smarty_include
File: /var/www/vhosts/site/httpdocs/gear/lib/templater/Smarty.class.php
Line: 1258
Function: include
File: /var/www/vhosts/site/httpdocs/gear/lib/templater/Smarty.class.php
Line: 1108
Function: fetch
File: /var/www/vhosts/site/httpdocs/gear/core/class.templater.php
Line: 170
Function: display
File: /var/www/vhosts/site/httpdocs/gear/core/fn.control.php
Line: 470
Function: display
File: /var/www/vhosts/site/httpdocs/gear/index.php
Line: 27
Function: fn_dispatch

MySQL isn’t liking the empty values in the query



If this is a dedicated or vps server and recently setup or updated, check to be sure MySQL is not running in Strict Mode.



Open my.cnf (usually located in /etc/my.cnf) and look for a line similar to this below

sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”



If that line does exist then remove the STRICT_TRANS_TABLES part and restart MySQL.



If that isn’t the problem then I’m not sure what else to check

[quote name=‘RSW’]I have had to close my store… so if you are interested here is the error that is being displayed: Database error: 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 '' at line 1 (1064)
Invalid query: SELECT SQL_CALC_FOUND_ROWS
[/quote]



This is caused by the database server or database itself.

You’ll need a pro to fix this, contact your webhost or CS-Cart. Last resort is that I’m available at a cost p/h.



J.

thanks, but I just check that file and there is no sql-mode setting.

I have put a support request in… we will see if cs-cart can be of any help.

I am worried that it my be a sql injection type issue… since nothing has changed in my setup and the site was working yesterday. The only thing I can think of is that some data input into the system might be causing the problem.

The database seems ok from phpMyAdmin?

the problem was related to a bloated satistics table… I clear the stats and shutdown the addon.

all is well now.



thanks for your help.

the problem was related to a bloated statistics table… I clear the stats and shutdown the addon.

all is well now.



thanks for your help.