Cannot set SQL_MODE to Mysql 8

Question

I have to set a persistent custom SQL MODE persistent to my server. I added this line in /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
...
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

When I try to run mysql I get this error:

2020-06-03T09:49:24.567568Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'. mysqld Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

I tried to change sql-mode to sql_mode and remove the double quotes, but it doesn't change.

First do a spell check, no_engine_substitution, check if any sql-mode was removed in 8.0 — jerichorivera, Jun 03 '20 at 10:32
I tried an empty list with double quotes `""` or only a single value, and it doesnt work. — Tobia, Jun 03 '20 at 10:38
it should be sql-mode='' (single-quotes), regarding your original problem NO_AUTO_CREATE_USER was removed in MySQL 8 hence the reason why it is not starting up. — jerichorivera, Jun 03 '20 at 21:58

score 10 · Answer 1 · edited Apr 29 '21 at 13:14

10

Just edit /etc/mysql/my.conf, removing "NO_AUTO_CREATE_USER". The result will be:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

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

It worked for me !

edited Apr 29 '21 at 13:14

Andrew Schulman

8,811
21
32
47

answered Apr 28 '21 at 13:34

Paolo Arena

101
1
3

score 1 · Answer 2 · answered Jun 03 '20 at 11:12

There is some limitations to the behaviour of sql_mode in different linux environments.

Instead, you should uninstall mysql and replace it with MariaDB. A very good alternative to mysql server. (As far as your concerned - this is mysql server, except it's package name)

To uninstall:

apt remove mysql mysqld mysql-server mysql-client
# note we do not purge this package, since we have databases with data in place.

Now install MariaDB - https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=focal--ubuntu_focal&mirror=icm&version=10.4

Sometime, the package maintainer already has MariaDB. So, it's simple as:

apt install mariadb mariadb-server mariadb-client

I encourge you to backup your data beforehand, using mysqldump or other means. But I would note that installing maraidb (as long as it will use the same dir for data) then all your databases, and tables will keep working normally.

This is not exactly the answer, but this is exactly what I have done! — Tobia, Jun 03 '20 at 15:09

score 0 · Answer 3 · edited Jul 09 '20 at 12:44

0

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 run:

systemctl daemon-reload
systemctl restart mysql

changes to *.cnf files do nothing

edited Jul 09 '20 at 12:44

kenlukas

3,101
2
16
26

answered Jul 07 '20 at 17:41

Marek Wesołowski

9
1

score 0 · Answer 4 · answered Oct 28 '22 at 10:38

0

correct syntax is sql-mode = STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

answered Oct 28 '22 at 10:38

Darshan

1

score 0 · Answer 5 · answered Jul 04 '23 at 08:52

For me the issue was solved by removing leading comma

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

Changed to this:

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

score -1 · Answer 6 · answered Apr 10 '21 at 19:12

-1

I think it is impossible to change *.cnf file in recent version. Instead of, you can change mysql.service file .

/lib/systemd/system/mysql.service
ExecStart=/usr/sbin/mysqld --sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

answered Apr 10 '21 at 19:12

darkhorse

1

Changing the `*.cnf` files is still possible, the option mentioned in the `mysql.service` just override them. – Luuk Feb 14 '22 at 10:48

Cannot set SQL_MODE to Mysql 8

6 Answers6