mysql error 1364 Field doesn’t have a default values

My table looks like

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

and then I have a trigger to auto populate the CREATED_BY field

create trigger autoPopulateAtInsert BEFORE INSERT on try for each row set new.CREATED_BY=user();

When I do an insert using

insert into try (name) values ('abc');

the entry is made in the table but I still get the error message

Field 'CREATED_BY' doesn't have a default value Error no 1364

Is there a way to suppress this error without making the field nullable AND without removing the triggfer? Otherwise my hibernate will see these exceptions ( even though the insertions have been made) and then application will crash.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is caused by the STRICT_TRANS_TABLES SQL mode defined in the

%PROGRAMDATA%MySQLMySQL Server 5.6my.ini

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


If editing that file doesn’t fix the issue, see for other possible locations of config files.

Method 2

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.

Method 3

In phpmyadmin, perform the following:

select @@GLOBAL.sql_mode

In my case, I get the following:


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


Method 4

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

Method 5

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:


Comment above line out and restart mysql server

mysql.server restart

Error gone!

Method 6

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.

Method 7

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

To check whether STRICT_TRANS_TABLES mode is enabled:


To disable strict mode:

SET GLOBAL sql_mode='';

Method 8

Before every insert action I added below line and solved my issue,


I’m not sure if this is the best solution,

SET SQL_MODE = ''; INSERT INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

Method 9

Modify your query and add “IGNORE” as:

INSERT IGNORE INTO  `mytable` (  `field1` ,  `field2`) VALUES ('value1',  'value2');

Method 10

Its work and tested Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini

port = 3306

then restart MySQL

Method 11

This appears to be caused by a long-standing (since 2004) bug (#6295) in MySQL, titled

Triggers are not processed for NOT NULL columns.

It was allegedly fixed in version 5.7.1 of MySQL (Changelog, last entry) in 2013, making MySQL behave as “per the SQL standard” (ibid).

Method 12

For Windows WampServer users:

WAMP > MySQL > my.ini

search file for sql-mode=""

Uncomment it.

Method 13

In Windows Server edit my.ini (for example program filesmysqlmysql server n.nmy.ini)

I would not simply set the sql-mode=””, rather I suggest one removes STRICT_TRANS_TABLES from the line, leave everything as-was, and then restart MySQL from the services utility. Add a comment for future programmers who you are and what you did.

Method 14

i set the fields to not null and problem solved, it updates when an information is commanded to store in it, no more showing msqli message that the field was empty cus you didnt insert value to it, well application of this solution can work on some projects depends on your project structure.

Method 15

i solved problem changing my.ini file located in data folder. for mysql 5.6 my.ini file moved to data folder rather the bin or mysql installation folder.

Method 16

I think in name column have null values in this case.

update try set name='abc' where created_by='def';

Method 17

I am using Xampp 7.3.28-1 for Linux. It uses MariaDB 10.4.19. Its configuration file is:

It does NOT contain an entry that defines sql_mode.
However the query “select @@GLOBAL.sql_mode;” does return a result and it contains the problematic STRICT_TRANS_TABLES. I guess it is by default now.

My solution was to explicitly define the mode by adding this line below [mysqld]:

You can define the modes that you need or just leave it blank.

Method 18

I found that once I removed what was a doubling up of a foreign key and primary key, when I could have just used the foreign key as the primary key alone in the table. All my code then worked and I was able to upload to db.

Method 19

This is for SYNOLOGY device users:

  • How to set global variables (strict mode OFF) on SYNOLOGY device.
    (checked on DSM 7.0.1-42218 – device model DS418)

Used PUTTY to connect:
login as root and
sudo su after… (to be admin total)

  • if not exist create my.cnf in:

MariaDB 5:
MariaDB 10:

  • this should be in the file (at least for strict mode off)
# custom configs
innodb_strict_mode = OFF
  • restart mysqld daemon:
    MariaDB 5:
    /usr/syno/bin/synopkg restart MariaDB
    MariaDB 10:
    /usr/syno/bin/synopkg restart MariaDB10
  • check for strict mode enabled at these two global options – both should be not there or off (see config above)
  • log into mysql:
    mysql -u root -p
  • enter password:

show variables like ‘sql_mode’;
show variables like ‘%STRICT%’;

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x