problem set sql_mode on 19.10

I'm having some bad issues while im trying to set sql_mode for my 19.10 ubuntu with mysql 8.0.19.

I need to set it as:

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

(i've tried with or without double quote)

I run:

which mysqld
/usr/sbin/mysqld

then

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

I've tried on each of every file to add the line (even in the folders/files linked by /etc/mysql/my.cnf), but when i try to reload the service an error is rised by this line.

The only file that make it works is on "~/.my.cnf" that ive manually created. but now the sql_mode is not as i'd like:

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+

but i can't find who set other's value and where.... and this mode seems thats no make work my crm (vtiger). And ive the big doubt that the setting is made just for my user and not for www-data, or i badly wrong?

1 Answer

In recent versions of ubuntu / debian I am modifying the file: /lib/systemd/system/mysql.service

ExecStart=/usr/sbin/mysqld --sql-mode=NO_ENGINE_SUBSTITUTION

Then:

systemctl daemon-reload
systemctl restart mysql

changes to *.cnf files do nothing