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

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.

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)

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.



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!