mysql error 1364 Field doesn't have a default values

Question

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.

Solution 1

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
);

Solution 2

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.

Solution 3

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.

Solution 4

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'

Solution 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:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Comment above line out and restart mysql server

mysql.server restart

Error gone!

View more solutions

Comments

[anasanjaria]

You can run an SQL query within your database management tool, such as phpMyAdmin: -- verified that the mode was previously set select @@GLOBAL.sql_mode; -- UPDATE MODE SET @@global.sql_mode= 'YOUR_VALUE';

[Chris]

This question is about MySQL, and doesn't make any mention of phpmyadmin. Please don't assume that everybody has that running.

[knocte]

I upgraded from 5.6 to 5.7.11 and the problem was fixed for me (and the removal of STRICT_TRANS_TABLES didn't work for me), so I'm upvoting this and downvoting the rest of answers

[JulienD]

@knocte Not everyone can upgrade MySQL on his system, so it is not worth downvoting in favor of this.

[L. D. James]

@jackadams49 This change doesn't stick. Can you advise me what you did to make this change survive a system reboot?

[maan81]

@jackadams49 to make it stay, sudo nano /etc/mysql/my.cnf, add [mysqld] sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREAT‌​E_USER,NO_ENGINE_SUB‌​STITUTION", save and exit, and restart mysql sudo service mysql restart

[maan81]

To add, I had to change the values of sql_mode to null, ie sql_mode = "" for other similar errors.

[Nagarajan Shanmuganathan]

How to set a default value in a Java program?

[KinSlayerUY]

you need a default value in the definition of the table ( create table try ( name varchar(8), CREATED_BY varchar(40) DEFAULT '' not null) )

[user889030]

ya but for that you will need to login to phpmyadmin with root account :) super account

[Waleed Ahmed]

after spending four hours, this solution worked for me in Ubuntu 16.04. Great !

[csvan]

This does not address the root issue. See the much more extensive answer by Phyxx below.

[Andrew]

but maybe you want STRICT_TRANS_TABLES ?

[Shadow]

@csvan Phyxx's answer does not address the root cause either because the root cause was a bug in MySQL that was fixed in v5.7.1 - see the answer by B98: stackoverflow.com/a/29854279/5389997 Removing strict_trans_table sql mode makes MySQL more prone to data quality errors, so removing it is not a really good advice.

[gustyaquino]

you don't need phpmyadmin at all, use these commands on the mysql command line.

[Julian]

In my version I had to change: sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZE‌​RO_DATE,NO_ZERO_IN_D‌​ATE,NO_AUTO_CREATE_U‌​SER"sql-mode="STRICT‌​_ALL_TABLES,ERROR_FO‌​R_DIVISION_BY_ZERO,N‌​O_ZERO_DATE,NO_ZERO_‌​IN_DATE,NO_AUTO_CREA‌​TE_USER" to sql-mode="". Uncommenting sql-mode="" caused the error.

[jmp]

this will reset to default after mysql / server / pc restart. You need to edit /etc/mysql/mysql.conf.d/mysqld.cnf , and after [mysqld] add this line: sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ER‌​ROR_FOR_DIVISION_BY_‌​ZERO,NO_AUTO_CREATE_‌​USER,NO_ENGINE_UBSTI‌​TUTION'

[fredy kardian]

solution by @waza123, this one works for me after upgrading to mysql 5.7.20. thanks

[dresh]

in my case the field is of type DATETIME with default set as NULL, and I am still seeing the same error, I have two schema on the same database. one for Staging, another for production, with the same table structures. It works in one schema, but does not work in another with exactly the same table structure in both. I am baffled.. I am not sure that it is an issue with STRICT_TRANS_TABLES

[José Carlos PHP]

It is not needed to do that before every insert action, just do it one time at the beggining of your script, just after connect to the database, and every insert query will work without "Field doesn't have a default value" error.

[José Carlos PHP]

This solution is fine because you don't need to alter tables (there may be a lot of fields to change).

[Pupil]

We have recently upgraded our MySQL to 5.7. We were facing too many issues. This worked for me. Saved my day.

[Ruslan Stelmachenko]

The only answer that really help me. Removing NOT NULL constraint or adding default value to column fixed the problem. Trigger works as expected.

[Prem popatia]

Manually removed "STRICT_TRANS_TABLES" from variables > sql_mode for testing and it worked !

[pvy4917]

This answer tells the same. stackoverflow.com/a/52004654/10431118

[Bill Degnan]

Generally speaking yes, but my point is that I am saying specifically not to blank out all values of sql-mode, but rather to remove only STRICT_TRANS_TABLES only, as that's all you need. Otherwise you could impact some other service.

[Mike Volmar]

I removed STRICT_TRANS_TABLES from /etc/my.cnf -- in the line starting with sql_mode -- and restarted mysql service and issue went away.

[Levchik]

this worked for me - my PHP script would abort, but with IGNORE, it just ads new row! Now, how "safe" is it to have IGNORE hardcoded into PHP-MYSQL query? I use this to auto-add rows, for new "day", where it did not exist before

[umarbilal]

You saved my day.

[zardilior]

Yeah don't add a default just remove the rules, great solution (sarcasm implied) never do this a great bad example. It solves the issue though

[MilanG]

Yeah, agree with you. But sometime you got some other's people project, which is running well i.e. on production (where strict mode is not set) and you just want to add some small feature or bugfix, working local. You don't want to fight the dragons, just to make that da*n thing work. :)

[zardilior]

for that scenario I agree

[Stefan]

@Levchik When you use IGNORE, then instead of an error, MySQL issues a warning when error happens, instead, and will try to complete the instruction somehow: mysqltutorial.org/mysql-insert-ignore

[Rashid]

It solved my error by altering column's default attribute from none to NULL. Unless of high rating answers! my cPanel was giving me access denied on shared hosting when I tried to update variable sql_mode.

[trainoasis]

For me after running the second command and checking sql_mode (1st command) it doesn't do anything. Even after restarting mysql service. Debian 9

[Reloecc]

@zardilior what's the issue? default value is picked based on column type if the rule is removed.. I see nothing wrong about it :/ that rule is pretty harsh for no reason.

[zardilior]

Not harsh at all, it just forces you to declare a default or provide a value, also strict mode works for way more things than only that, so disabling it, instead of declaring a deault on the column or passing the value, is really terrible mor ein prod. You disable one of mysql good charactersitics there

[smartworld-dm]

Worked like a charm! You saved my day~ Thanks a lot

[Jason Ebersey]

This worked perfectly, I just set the value of gzpost to NULL and the error went away :)

[cazort]

@Shadow That explanation makes sense if you're developing an application, but the answer by @Phyxx is still useful and important for some users because many people use software packages developed by others (most that were designed for MySQL < 5.7) for STRICT_TRANS_TABLES to be turned off (and more broadly, for the more permissive SQL_MODE=NO_ENGINE_SUBSTITUTION rather than STRICT. This affects other things too, such as handling DATETIME columns; I've found both such errors tend to appear in many applications when migrating to MySQL >= 5.7.

[cazort]

This solution is only temporary: it will not cause the change to persist after a server restart. To this end, you need to change the setting in the configuration file as the solution by @Phyxx recommends.

[Shadow]

@cazort those issues should be handled through the support for the 3rd party application, not as an answer on a programming Q/A site, which is aimed at developing new applications.

[cazort]

@Shadow There can also be reasons why a developer would want to use that solution, especially temporarily. I have done it myself when an application was designed for the lax settings, but I now control the code and it needs to go up in a short time-frame. Best practice is to know both solutions and apply them as needed. Applications are also common, where the data quality concerns are a non-issue, such as a web database where the overwhelming majority of use is read-only and only one admin ever edits the data.

[Shadow]

@cazort if all these caveats would be escribed in that answer, I may even agree with you. These are not added, so the answer is outright dangerous.

[Don]

Thanks man. we upgraded to mariadb 10 from 5 and had this error popping up. saved my day +1

[Abdellah Ramadan]

This is super awesome