MySQL / MariaDB: Disable strict mode | Upgrading to MariaDB 10.2

We recently needed to upgrade a server from MySQL 5.7 to MariaDB 10.3 and one of the key steps in doing so is to disable strict mode after the upgrade. By default, MariaDB enables strict mode from version 10.2 and onwards.

Having this enabled can cause various issues, a few below:

• Can break your application
• SQL insert queries that previous worked, with missing or invalid data, will stop and  start throwing errors

An example of this is the following:

We’re trying to enter data without the date, leaving it blank

INSERT INTO `transactions` (`id`, `name`, `amount`, `date`) VALUES ('209', 'Payment for online services', '5000', '')

With strict mode enabled it’ll throw an error like the following:

ERROR 1292 (22007): Incorrect date value: '' for column `db_test1`.`transactions`.`date` at row 1

Conventionally, and with strict mode disabled, this would let the query go through warning of missing data but not stopping the query from executing. Something like this:

MariaDB [db_test1]> INSERT INTO `transactions` (`id`, `name`, `amount`, `date`) VALUES ('209', 'Payment for online services', '5000', '');
Query OK, 1 row affected, 1 warning (0.012 sec)


MariaDB [db_test1]> select * from transactions;
+-----+-----------------------------+--------+------------+
| id  | name                        | amount | date       |
+-----+-----------------------------+--------+------------+
| 209 | Payment for online services |   5000 | 0000-00-00 |
+-----+-----------------------------+--------+------------+
1 row in set (0.000 sec)

A better and obvious approach could be alter the application to allow for null entries, automatically insert real dates etc. But that is not the scope of this article, if this is a shared hosting then it is going to be virtually impossible to go around to your clients asking them to make the necessary changes.

To disable strict mode, locate your my.cnf file. This is either going to be in /etc/my.cnf or /etc/my.cnf.d/server.cnf depending on your OS and installation type. Edit the my.cnf file and under [mysqlnd] add the following line:

sql_mode=NO_ENGINE_SUBSTITUTION

Save and restart MariaDB

service mariadb restart

That’s it, you’re done.