Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Any Body Have An Idea How To Fix This Sql Error? Rate Topic   - - - - -

 
  • Dexterflamez
  • Senior Member
  • Trial users
  • Join Date: 08-Jan 18
  • 358 posts

Posted 29 August 2021 - 02:03 AM #1

Database (error)
Error: 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 'products.product_id NOT IN('') AND (companies.status = 'A' OR products.compan...' at line 1 <b>(1064)</b>
Query: SELECT products.company_id, COUNT(DISTINCT products.product_id) as products_count FROM mcoves_products as products LEFT JOIN mcoves_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'en' LEFT JOIN mcoves_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN mcoves_companies AS companies ON companies.company_id = products.company_id INNER JOIN mcoves_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN mcoves_categories ON mcoves_categories.category_id = products_categories.category_id AND (mcoves_categories.usergroup_ids = '' OR FIND_IN_SET(0, mcoves_categories.usergroup_ids) OR FIND_IN_SET(1, mcoves_categories.usergroup_ids)) AND mcoves_categories.status IN ('A', 'H') LEFT JOIN mcoves_supplier_links ON mcoves_supplier_links.object_id = products.product_id AND mcoves_supplier_links.object_type = 'P' WHERE 1=1AND products.product_id NOT IN('') AND (companies.status = 'A' OR products.company_id = 0) AND products.status IN ('A') AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) AND products.parent_product_id = 0 AND products.master_product_status IN ('A') AND products.product_type != 'D' AND products.company_id IN (17) GROUP BY products.company_id
Backtrace››

index.php (fn_dispatch): 25
app/functions/fn.control.php (fn_run_controller): 433
app/functions/fn.control.php (include): 684
app/controllers/frontend/companies.php (fn_get_companies_active_products_count): 157
app/functions/fn.companies.php (db_get_hash_single_array): 1785
app/functions/fn.database.php (getSingleHash): 107
app/Tygh/Database/Connection.php (call_user_func_array): 374
app/Tygh/Database/Connection.php (query): 518


 
  • johnbol1
  • Never Re
  • Members
  • Join Date: 23-Feb 10
  • 5110 posts

Posted 29 August 2021 - 12:57 PM #2

Try and run a database check/repair in phpmyadmin

 then maybe turn on dev mode and see any erros in front end.

 

https://www.thoughtc...myadmin-2693966


4 14.1


 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 12156 posts

Posted 02 September 2021 - 07:12 PM #3

Some addon (or core change) is not properly formatting their addtion to the SQL statement:

WHERE 1=1AND products.product_id NOT IN('')

Should be

WHERE 1 AND products.product_id NOT IN('')

I would search your addons directory for that syntax, then disable the addon that's associated with it.

grep -R app/addons "AND products.product_id NOT IN('')"

EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 22943 posts

Posted 03 September 2021 - 05:40 AM #4

Try to disable 3rd party addons one-by-one


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 1210     Multi-Vendor              USD 1250    Multi-Vendor PLUS           USD 3100 (2775)
CS-Cart Ultimate  USD 4025     CS-Cart + YOUPI      USD 1459      Multi-Vendor Ultimate       USD 7500 (6000)


 

Posted 03 January 2022 - 11:29 PM #5

Happy New Year everyone!

 

I thought I will quickly update the site during holidays :} 

Well 4.14.1 had other ideas. Could anybody share their opinions on how to fix the DB errors I am getting right after upgrade. they seems to be store ID and language based.

 

I have optimised DB, checked and repaired it in phpMA. Nothing changed. Have disabled most add-ons, same story.

 

This is the error code I am getting:

Database (error)
Error: Unknown column 'storefronts_languages.lang_id' in 'on clause' <b>(1054)</b>
Query: SELECT cscart_languages.* FROM cscart_languages LEFT JOIN cscart_storefronts_languages AS storefronts_languages ON storefronts_languages.language_id = cscart_languages.lang_id INNER JOIN cscart_ult_objects_sharing ON (cscart_ult_objects_sharing.share_object_id = storefronts_languages.lang_id AND cscart_ult_objects_sharing.share_company_id = 1 AND cscart_ult_objects_sharing.share_object_type = 'languages') WHERE 1 AND cscart_languages.status = 'A'AND (storefronts_languages.storefront_id = 1 OR storefronts_languages.storefront_id IS NULL)

Backtrace››
index.php (require): 24
init.php (fn_init): 179
app/functions/fn.init.php (fn_init_language): 1200
app/functions/fn.init.php (getOrSetCache): 226
app/Tygh/Registry.php ({closure}): 792
app/functions/fn.init.php (getAvailable): 225
app/Tygh/Languages/Languages.php (db_get_hash_array): 608
app/functions/fn.database.php (getHash): 48
app/Tygh/Database/Connection.php (query): 345
app/Tygh/Database/Connection.php (throwError): 690

cs-cart-pp.jpg

 

Any help would be greatly appreciated.

 

Regards

Vic



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 22943 posts

Posted 04 January 2022 - 08:15 AM #6

As far as I can see, the cscart_storefronts_languages table should have the following columns

 

  `storefront_id` int(11) unsigned NOT NULL DEFAULT '0',
  `language_id` int(11) unsigned NOT NULL DEFAULT '0',

In your case for some reason lang_id column is used. It can be caused by wrong upgrade or 3rd party module. Examination is required


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 1210     Multi-Vendor              USD 1250    Multi-Vendor PLUS           USD 3100 (2775)
CS-Cart Ultimate  USD 4025     CS-Cart + YOUPI      USD 1459      Multi-Vendor Ultimate       USD 7500 (6000)


 

Posted 04 January 2022 - 11:17 AM #7

Thank you eCom Labs

 

I have talked to Aleksey Ivanov before. Will reach out again. Thank you!