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!