Level 75
Jan 10, 2021
4
Level 1
And again about 'ONLY_FULL_GROUP_BY' in Stored Procedures
Hi guys,
I am sorry that ask again this question but i met with very strange issue (for me :-) )
Details: 1, I have a Laravel application written for Laravel 5.4. I am not the developer and developer is not available now so the option to change the query is the last option for me.
-
It works nice with 10.3.27-MariaDB with sql_mode NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-
I decided to move it to another server Ubuntu 20.04 with MYSQL 8.022 (under Virtualmin)
After that i started to get the error 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by for the sql query stored as Stored Procedure
I read a lot about this issue and try to change everything what i can.
- i've changed settings in /etc/mysql/mysql.conf.d/mysqld.cnf to
[mysqld] sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- i've changed database.php 'strict' => false, 'engine' => null, 'modes' => [ 'ERROR_FOR_DIVISION_BY_ZERO', 'NO_ENGINE_SUBSTITUTION', ],
Mysql shows me now that sqlmode is 'ERROR_FOR_DIVISION_BY_ZERO', 'NO_ENGINE_SUBSTITUTION',
But Laravel continues to tell me about incompatible with sql_mode=only_full_group_by
I already don't know where to look and why Laravel is still not ok with my settings.
Could you suggest to me anything?
Level 1
Thank you The main problem to use this way is that it is not my code. That is why i tryed first to change settings
And also i am a bit confused - strict mode is false for whole project now as it is set in database.php.
Is this way using config()->set('database.connections.mysql.strict', false); will add something else?
Level 1
I have checked the query it works nice in the console. But the same query generates the 'incompatible with sql_mode=only_full_group_by' error in Laravel'.
How is it possible?
Level 1
The reason was here:
For stored procedures: MySQL stores the sql_mode system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing.
My sql query were stored as stored procedure that is why the current sql_mode had no effect. I just altered the procedure in correct sql_mode and it works nice.
Please or to participate in this conversation.