Hi everyone,
with my Dolibarr after updating MYSQL to version 5.7, the product list returns this error:
Latest database access request error: SELECT p.rowid, p.ref, p.ref_ext, p.description, p.label, p.fk_product_type, p.barcode, p.price, p.tva_tx, p.price_ttc, p.price_base_type, p.entity, p.fk_product_type, p.duration, p.finished, p.tosell, p.tobuy, p.seuil_stock_alerte, p.desiredstock, p.tobatch, p.accountancy_code_sell, p.accountancy_code_sell_intra, p.accountancy_code_sell_export, p.accountancy_code_buy, p.accountancy_code_buy_intra, p.accountancy_code_buy_export, p.datec as date_creation, p.tms as date_modification, p.pmp, p.stock, p.cost_price, p.weight, p.weight_units, p.length, p.length_units, p.width, p.width_units, p.height, p.height_units, p.surface, p.surface_units, p.volume, p.volume_units, p.fk_country, p.fk_state, p.import_key, MIN(pfp.unitprice) as bestpurchaseprice FROM llx_product as p LEFT JOIN llx_product_fournisseur_price as pfp ON p.rowid = pfp.fk_product WHERE p.entity IN (3) AND p.fk_product_type <> 1 GROUP BY p.rowid, p.ref, p.description, p.label, p.barcode, p.price, p.tva_tx, p.price_ttc, p.price_base_type, p.fk_product_type, p.duration, p.finished, p.tosell, p.tobuy, p.seuil_stock_alerte, p.desiredstock, p.datec, p.tms, p.entity, p.tobatch, p.pmp, p.cost_price, p.stock, p.accountancy_code_sell, p.accountancy_code_sell_intra, p.accountancy_code_sell_export, p.accountancy_code_buy, p.accountancy_code_buy_intra, p.accountancy_code_buy_export, p.weight, p.weight_units, p.length, p.length_units, p.width, p.width_units, p.height, p.height_units, p.surface, p.surface_units, p.volume, p.volume_units, p.fk_country, p.fk_state, p.import_key ORDER BY p.ref ASC LIMIT 26
Return code for latest database access request error: DB_ERROR_1055
Information for latest database access request error: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘Sql172321_5.p.ref_ext’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
A web search tells me to change the DB settings:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,‘’));
My provider won’t let me (or won’t) change the settings.
What can I do?
