10

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.

4
  • First do a spell check, no_engine_substitution, check if any sql-mode was removed in 8.0 Jun 3, 2020 at 10:32
  • I tried an empty list with double quotes "" or only a single value, and it doesnt work.
    – Tobia
    Jun 3, 2020 at 10:38
  • 1
    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. Jun 3, 2020 at 21:58
  • I have tried without NO_AUTO_CREATE_USER but it still fails
    – Tobia
    Jun 4, 2020 at 7:17

6 Answers 6

14

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 !

1

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.

1
  • This is not exactly the answer, but this is exactly what I have done!
    – Tobia
    Jun 3, 2020 at 15:09
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

IMG

0

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

0

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"
-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
1
  • Changing the *.cnf files is still possible, the option mentioned in the mysql.service just override them.
    – Luuk
    Feb 14, 2022 at 10:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .