Home / server / Questions / 1019823
In Process
Tobia
Asked: 2020-06-04 01:57:19 +0800 CST

Cannot set SQL_MODE to Mysql 8

  • 772

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.

mysql sql

4 Answers

  1. 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 !

    • 4

  2. 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

  3. 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

  4. 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