Chris Moore

MySQL Strict Mode

When I was installing FormaLMS, an open source Learning Management System based on the original Docebo Open Source LMS, I came across a MySQL STRICT mode error that prevented me from proceeding.

warningYou have MySQL strict mode enabled; forma.lms doesn't support it, so please turn it off Db will be created

By default, MySQL 5.7 is much "stricter" than older versions of MySQL. In previous versions, MySQL would modify values that don't match the field format to the closest possible correct value. For example, when inserting a placeholder date field using all zeros would work in older versions of MySQL:

INSERT INTO test VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

However in strict mode with MySQL 5.7, this same query will return an error as it is not within the valid date range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59':

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');  
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

To resolve this in your application and complete the installation as was required in FormaLMS, we can toggle the sql_mode setting within your MySQL (or MariaDB) database to be less strict:

To do this, first login to MySQL as root:

mysql -u root -p

...and enter your MySQL root password. Alternately, you may just use sudo to log into MySQL depending on your access to the server and the server configuration:

sudo mysql -u root

Once logged into the mysql command line interface (cli), you can enter one of the following commands to disable or enable strict mode:

Disable safe mode:

set global sql_mode='';

Enable safe mode:

set global sql_mode='STRICT_TRANS_TABLES';