2

I absolutely need to unset ONLY_FULL_GROUP_BY on my VPS server hosted by OVH

So I use the command

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

found here

But I don't why or who automatically reset this flag the day after.

Could you give me some ideas to find what happens??

Can phpMyAdmin set this mode ??

fralbo
  • 2,534
  • 4
  • 41
  • 73
  • It resets on server restart. You will have to edit your config file (probably `/etc/mysql/my.cnf` or `/etc/my.cnf`) to "survive" server restart. You cannot do this in phpmyadmin. – Solarflare Aug 21 '17 at 18:40
  • @Solarflare I really suspect it's done my phpMyAdmin because I'm the only one to administrate this server and it never restart nor the services. – fralbo Aug 22 '17 at 05:43
  • @Solarflare, I looked at all `/etc/mysql/*` and I didn't see any sql_mode. I suppose it's working with the default configuration. So I added the string I can see in `Variables` page in phpMyAdmin, without `ONLY_FULL_GROUP_BY` flag to see.... – fralbo Aug 22 '17 at 06:07
  • Well there are exactly two ways the value changes: 1. by changing it explicitly by using code like yours. This can be done by running it in phpmyadmin (though it does not do it automatically) or by any client (given it has the required permissions) or by a procedure/event in mysql. You have to figure out which it is. It might be e.g. part of a cronjob, maybe a backup script that runs daily. 2. By a server restart. Check `select @@uptime` to get the uptime in seconds to verify that it's not that. It does not have to be you that restarts the service, but could be part of a maintainance script. – Solarflare Aug 22 '17 at 07:26

1 Answers1

3

I had the same issue and solved by using the following tutorial

https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/

Ahmed Shaaban Elgendy
  • 153
  • 1
  • 4