2

Unable to get rid of sql_mode ONLY_FULL_GROUP_BY on ubuntu 16.04

I am trying to remove the sql_mode = ONLY_FULL_GROUP_BY in a persistent way. Issuing the following command I can do it for this sessison

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

Then I tried to remove it permanently by modifying /etc/mysql/my.cnf /etc/mysql/mysql.cnf /etc/mysql/mysql.conf.d/mysqld.cnf adding a section 

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

However when I restart the mysql, teh ONLY_FULL_GROUP_BY mode is there. I am running out of options.

Does anyone can provide a little bit of light here? Thank you.

mosh442
  • 123
  • 1
  • 4

2 Answers2

4

The only thing that works for me is to edit /lib/systemd/system/mysql.service:

# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
# Normally, we'd simply use:
# ExecStart=/usr/sbin/mysqld
ExecStart=/usr/sbin/mysqld --sql-mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
Jason Darwin
  • 56
  • 2
0

Depending on your mysql distribution /etc/mysql/my.cnf should already have [mysqld] section. I would look into my.cnf* files and see if sql_mode is already there. What mysql version are you using?

Alexander Rubin
  • 101
  • 1