MySQL Error – Field ‘name’ doesn’t have a default value – OS X

This error crops up when you’re trying to run INSERT or UPDATE command with empty fields for which default value isn’t set.

Cause:

MySQL has a couple of modes,  of which STRICT_TRANS_TABLES is the one screwing you over. This mode is enabled by default in an install-script generated my-default.cnf or my.cnf file. (the exact name depends on your MySQL version)

Solution 1:

The simplest way is to just set a default value for these fields.
ALTER TABLE `users` CHANGE COLUMN `name` `name` TEXT NOT NULL DEFAULT '';

However, if you have just too many fields, or you want an all-encompassing permanent solution, read on.

Solution 2:

This solution involves overriding the settings in the generated .cnf file with your own file.

If you’re on OS X and using a version newer than 5.7.5, go back to Solution 1.
This version onwards, there is no default .cnf file generated to override. Also, the .cnf file doesn’t affect the installation anymore:
Packaging; OS X: Using user=mysql during installation on OS X did not allow the mysql database to be installed. To fix this problem, OS X packages now use the –no-defaults option when creating this database. This also means that having a my.cnf file on the system no longer affects the installation. (Bug #21364902)

If you’re using an older version though, you might be in luck.

For brew installation,

• Open, nano /usr/local/Cellar/mysql/5.6.20_1/my.cnf
• Replace, sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES with sql_mode=NO_ENGINE_SUBSTITUTION or just remove it.
• Restart the server, mysql.server restart

For other installations, locate your my.cnf file, and follow the above.

If you’re unable to find a .cnf file, you might have to create one using the default format.
Copy file from install location /usr/local/Cellar/mysql/5.x.x/support-files/my.cnf to /etc/my.cnf
Make the changes listed above, restart server.