0

Even though strict mode is disabled I am still getting ERROR 1055 when running a stored procedure

On Ubuntu 19.04 with MySQL 5.7.26, I've tried every combination of options for sql-mode in my.cnf and set global that makes any sense. Including just sql-mode = '', but it still acts as though strict mode is on.

mysql> SHOW VARIABLES LIKE 'sql_mode';
| Variable_name | Value
| sql_mode      | IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Or even with sql-mode empty

mysql> SHOW VARIABLES LIKE 'sql_mode';
| Variable_name | Value |
| sql_mode      |       |

I get the error that only_full_group_by is still on...

mysql> CALL getReadyToInvoiceOrders(480,-1,-1,'','','','','','');
ERROR 1055 (42000): Expression #14 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'axistms_transport_local.BPBCR.currentAwardedID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

You can see "STRICT_TRANS_TABLES" and "ONLY_FULL_GROUP_BY" have been removed, but still the ERROR 1055 persists

Barmar
  • 741,623
  • 53
  • 500
  • 612
zoonix
  • 11
  • 2

1 Answers1

0

Remove the clause ONLY_FULL_GROUP_BY :

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The default settings are ONLY_FULL_GROUP_BY, NO_AUTO_CREATE_USER, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION for sql_mode in version 5.7 MySQL

Slawomir Dziuba
  • 1,265
  • 1
  • 6
  • 13
  • Where do you see `ONLY_FULL_GROUP_BY` in the output? – Barmar Jun 26 '19 at 20:10
  • The problem turned out to be something going wrong during the import from prod to the new dev server. Too many to recreate (as suggested) for a legacy application so I did a separate export and import and works like a charm now. – zoonix Jun 26 '19 at 20:35