What is the correct way to set sql-mode permanently for MySQL 8 on Ubuntu 20.04?

Question

I have a fresh LEMP stack installed on a Digital Ocean Ubuntu 20.04 with MySQL 8.

From the command line I see the default sql_mode is set as:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

I want it to simply read:

ALLOW_INVALID_DATES

I have read this but the setting sql-mode="ALLOW_INVALID_DATES" doesn't work in any of the many my.cnf variants found within the /etc/mysql sub-folders. Niether does "sql_mode". All permutations either get accepted but a mysql service restart fails (until the entry is removed) or accepted but then any attempt to run the mysql CLI fails stating that the sql_mode or sql-mode command isn't recognized.

Setting with this option works:

SET GLOBAL sql_mode = 'ALLOW_INVALID_DATES';

but resets on restart of the service/ server which is not desired.

What am I doing wrong in this configuration step?

Answer 1

I had the same issue, and my problem was that in my my.cnf file, I had a semicolon at the end of the line. It worked when, for example, in my case sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;' was the very last line in the config file. But if there were further lines, mysqld would fail to start. I took out the semicolon (and the single quotes for that matter) and it worked.