In Laravel Homestead to disable MySQL 5.7.* strict mode edit your mysql.cnf options file using the command sudo nano /etc/mysql/conf.d/mysql.cnf and then after the [mysql] line at add following:
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
With this configuration STRICT_TRANS_TABLES, NO_ZERO_IN_DATE and NO_ZERO_DATE are removed. Also, note ~/.my.cnf is not the file we are editing.
Next, restart mysql sudo /etc/init.d/mysql restart.
Then, run mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';" using the password secret.
You should see:
| Variable_name | Value |
|---|---|
| sql_mode | ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
Notice, the values STRICT_TRANS_TABLES, NO_ZERO_IN_DATE and NO_ZERO_DATE are removed. Take note of the MySQL documentation on sql_mode options.
You can also edit sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf instead if you like but the /etc/mysql/conf.d/mysql.cnf file is empty and easy to manage.
MySQL 8.0
If you are using MySQL 8, on Laravel Forge for example, use the command sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf. The options file /etc/mysql/conf.d/mysql.cnf is not the .cnf file we are using in this particular instance.
Then add the sql_mode options you need and wrap them in quotes under the [mysqld] group. The sql_mode options needed to disable NO_ZERO_IN_DATE errors are:
sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Then, restart MySQL with sudo service mysql restart and check your sql_mode if desired.
Note that NO_AUTO_CREATE_USER was removed in MySQL 8.0.11 so avoid using that option.
Solves MySQL Error Codes
- [ERR] 1067 - Invalid default value
- ERROR 1067 (42000): Invalid default value for
These error are often seen in Laravel for created_at, updated_at, and deleted_at.