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

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 (1064)
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

Try and run a database check/repair in phpmyadmin

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

https://www.thoughtco.com/repairing-mysql-database-with-phpmyadmin-2693966

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('')"

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

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' (1054)
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

Any help would be greatly appreciated.

Regards

Vic

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

Thank you eCom Labs

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