mysql error 1364 Field doesn't have a default values

Question

This is caused by the STRICT_TRANS_TABLES SQL mode defined in the

%PROGRAMDATA%\MySQL\MySQL Server 5.6\my.ini

file. Removing that setting and restarting MySQL should fix the problem.

See https://www.farbeyondcode.com/Solution-for-MariaDB-Field--xxx--doesn-t-have-a-default-value-5-2720.html

If editing that file doesn't fix the issue, see http://dev.mysql.com/doc/refman/5.6/en/option-files.html for other possible locations of config files.

Open phpmyadmin and goto 'More' Tab and select 'Variables' submenu. Scroll down to find sql mode. Edit sql mode and remove 'STRICT_TRANS_TABLES' Save it.

In phpmyadmin, perform the following:

select @@GLOBAL.sql_mode

In my case, I get the following:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES ,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Copy this result and remove STRICT_TRANS_TABLES. Then perform the following:

set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Set a default value for Created_By (eg: empty VARCHAR) and the trigger will update the value anyways.

create table try ( 
     name varchar(8), 
     CREATED_BY varchar(40) DEFAULT '' not null
);

When I had this same problem with mysql5.6.20 installed with Homebrew, I solved it by going into my.cnf

nano /usr/local/Cellar/mysql/5.6.20_1/my.cnf

Find the line that looks like so:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server

mysql.server restart

Error gone!

Run mysql console:

mysql -u your_username -p

, select database:

USE your_database;

and run (also from mysql console):

SET GLOBAL sql_mode='';

That will turn off strict mode and mysql won't complain any more.

To make things clear: your database definition says "this field must have default value defined", and by doing steps from above you say to MySql "neah, just ignore it". So if you just want to do some quick fix locally this solution is ok. But generally you should investigate in your database definition and check if field really needs default value and if so set it. And if default value is not needed this requirement should be removed to have clean situation.

As others said, this is caused by the STRICT_TRANS_TABLES SQL mode.

To check whether STRICT_TRANS_TABLES mode is enabled:

SHOW VARIABLES LIKE 'sql_mode';

To disable strict mode:

SET GLOBAL sql_mode='';