4

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
  • 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

5 Answers 5

5

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

-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 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.