Cannot set SQL_MODE to Mysql 8

Solution 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 !

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