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.

score 231 · Answer 1 · edited Jul 03 '23 at 12:07

231

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.

edited Jul 03 '23 at 12:07

Hypenexy

5
1
2

answered May 29 '13 at 05:11

Phyxx

15,730
13
73
112

5

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';` – anasanjaria Dec 04 '13 at 18:17
7

but maybe you want STRICT_TRANS_TABLES ? – Andrew Apr 21 '17 at 16:16
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 – dresh Feb 01 '18 at 04:08
2

I removed STRICT_TRANS_TABLES from /etc/my.cnf -- in the line starting with sql_mode -- and restarted mysql service and issue went away. – Mike Volmar Jan 28 '19 at 13:42
The link to farbeyondcode is dead – Alex May 01 '23 at 21:04
I wonder if Stack Overflow will ever learn to provide a way to FIX the error, not to sweep it under the rug. – Your Common Sense May 22 '23 at 07:38

score 102 · Answer 2 · answered Oct 19 '14 at 15:41

102

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.

answered Oct 19 '14 at 15:41

Nilesh Dhangare

1,069
1
7
2

33

This question is about MySQL, and doesn't make any mention of phpmyadmin. Please don't assume that everybody has that running. – ChrisGPT was on strike Oct 19 '14 at 15:59
2

@jackadams49 This change doesn't stick. Can you advise me what you did to make this change survive a system reboot? – L. D. James Jul 18 '16 at 11:11
10

@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_CREATE_USER,NO_ENGINE_SUBSTITUTION"`, save and exit, and restart mysql `sudo service mysql restart` – maan81 Aug 27 '16 at 11:57
1

To add, I had to change the values of `sql_mode` to null, ie `sql_mode = ""` for other similar errors. – maan81 Aug 27 '16 at 12:02
We have recently upgraded our MySQL to 5.7. We were facing too many issues. This worked for me. Saved my day. – Pupil Apr 19 '18 at 06:49
Thanks man. we upgraded to mariadb 10 from 5 and had this error popping up. saved my day +1 – Don Aug 31 '21 at 23:53

score 50 · Answer 3 · answered Aug 26 '16 at 07:43

50

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'

answered Aug 26 '16 at 07:43

Kamil

968
1
8
18

ya but for that you will need to login to phpmyadmin with root account :) super account – user889030 Oct 19 '16 at 10:30
2

after spending four hours, this solution worked for me in Ubuntu 16.04. Great ! – Waleed Ahmed Haris Nov 14 '16 at 09:47
5

you don't need `phpmyadmin` at all, use these commands on the `mysql` command line. – gustyaquino Aug 12 '17 at 15:11
5

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,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_UBSTITUTION' – jmp Oct 30 '17 at 17:55
solution by @waza123, this one works for me after upgrading to mysql 5.7.20. thanks – fredy kardian Dec 29 '17 at 02:58

score 40 · Accepted Answer · edited Jun 16 '19 at 01:33

40

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

edited Jun 16 '19 at 01:33

zardilior

2,810
25
30

answered Mar 15 '13 at 17:47

KinSlayerUY

1,903
17
22

How to set a default value in a Java program? – Nagarajan Shanmuganathan Sep 01 '16 at 07:55
1

you need a default value in the definition of the table ( create table try ( name varchar(8), CREATED_BY varchar(40) DEFAULT '' not null) ) – KinSlayerUY Sep 14 '16 at 15:56
1

This does not address the root issue. See the much more extensive answer by Phyxx below. – csvan Feb 17 '17 at 20:09
7

@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: http://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. – Shadow May 18 '17 at 08:59
@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 Aug 24 '21 at 14:19
@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. – Shadow Aug 24 '21 at 14:24
@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. – cazort Aug 24 '21 at 15:26
1

@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. – Shadow Aug 24 '21 at 16:21

score 32 · Answer 5 · answered Sep 15 '14 at 13:37

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!

MilanG · Answer 6 · 2020-04-03T06:39:16.447

27

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.

edited Apr 03 '20 at 06:39

answered Aug 17 '18 at 08:22

MilanG

6,994
2
35
64

2

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 – zardilior Jun 15 '19 at 23:13
2

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. :) – MilanG Jul 09 '19 at 07:38
for that scenario I agree – zardilior Jul 10 '19 at 17:16
@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. – Reloecc Jun 24 '20 at 09:20
1

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 – zardilior Jun 24 '20 at 13:12
This worked perfectly, I just set the value of gzpost to NULL and the error went away :) – Jason Ebersey Jul 10 '21 at 13:20
1

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. – cazort Aug 24 '21 at 14:23
This is super awesome – Abdellah Ramadan Feb 09 '22 at 16:23

score 17 · Answer 7 · answered Aug 24 '18 at 12:35

17

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='';

answered Aug 24 '18 at 12:35

Damjan Pavlica

31,277
10
71
76

Manually removed "STRICT_TRANS_TABLES" from variables > sql_mode for testing and it worked ! – Prem popatia Sep 26 '18 at 07:05
1

You saved my day. – umarbilal May 22 '19 at 19:15
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 – trainoasis Mar 04 '20 at 10:22

score 15 · Answer 8 · answered Mar 22 '17 at 17:12

15

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

SET SQL_MODE = '';

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

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

answered Mar 22 '17 at 17:12

Vinith

1,264
14
25

1

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 Feb 20 '18 at 19:58
This solution is fine because you don't need to alter tables (there may be a lot of fields to change). – José Carlos PHP Feb 20 '18 at 20:00

score 13 · Answer 9 · answered Feb 15 '18 at 09:05

13

Modify your query and add "IGNORE" as:

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

answered Feb 15 '18 at 09:05

Rohit Dhiman

131
1
4

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 – Levchik Feb 04 '19 at 21:53
@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: http://www.mysqltutorial.org/mysql-insert-ignore/ – Stefan Oct 20 '19 at 07:48
Worked like a charm! You saved my day~ Thanks a lot – Zhong Ri Jun 18 '21 at 04:58

score 12 · Answer 10 · edited Aug 30 '16 at 09:41

12

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

[mysqld]
port = 3306
sql-mode=""

then restart MySQL

edited Aug 30 '16 at 09:41

ddb

2,423
7
28
38

answered Aug 30 '16 at 07:44

Devraj Gupta

329
3
13

score 6 · Answer 11 · answered Apr 24 '15 at 17:52

6

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

answered Apr 24 '15 at 17:52

B98

1,229
12
20

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 – knocte Mar 16 '16 at 13:59
6

@knocte Not everyone can upgrade MySQL on his system, so it is not worth downvoting in favor of this. – JulienD Mar 21 '16 at 20:56
The only answer that really help me. Removing `NOT NULL` constraint or adding default value to column fixed the problem. Trigger works as expected. – Ruslan Stelmachenko Aug 02 '18 at 21:51

score 6 · Answer 12 · answered Apr 21 '17 at 16:23

6

For Windows WampServer users:

WAMP > MySQL > my.ini

search file for sql-mode=""

Uncomment it.

answered Apr 21 '17 at 16:23

Andrew

18,680
13
103
118

2

In my version I had to change: `sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"` to `sql-mode=""`. Uncommenting `sql-mode=""` caused the error. – Julian Oct 05 '17 at 08:54

score 3 · Answer 13 · answered Dec 03 '18 at 18:14

3

In Windows Server edit my.ini (for example program files\mysql\mysql server n.n\my.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.

answered Dec 03 '18 at 18:14

Bill Degnan

41
3

This answer tells the same. https://stackoverflow.com/a/52004654/10431118 – pvy4917 Dec 03 '18 at 18:41
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. – Bill Degnan Dec 03 '18 at 20:21

score 3 · Answer 14 · answered Sep 30 '22 at 21:24

Most of these answers are a lot of work for the not-seasoned coder. Like mentioned the issues is with STRICT_TRANS_TABLES.

First verify STRICT_TRANS_TABLES is running.

$ mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

You can disable strict mode on your MySQL server by running the following command on your Linode's command line:

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

Then, you can verify that the mode is set by running the following:

$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

This answer was found here https://www.linode.com/community/questions/17070/how-can-i-disable-mysql-strict-mode

score 2 · Answer 15 · answered Jan 09 '22 at 06:32

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:
/var/packages/MariaDB/etc
MariaDB 10:
/var/packages/MariaDB10/etc

this should be in the file (at least for strict mode off)

# custom configs
[mysqld]
innodb_strict_mode = OFF
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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%';

score 1 · Answer 16 · answered Nov 25 '16 at 01:34

1

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.

answered Nov 25 '16 at 01:34

ExploreTech

11
2

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. – Rashid Dec 27 '19 at 13:44

sadik keskin · Answer 17 · 2019-06-02T15:02:03.153

0

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.

edited Jun 02 '19 at 15:02

answered Jun 02 '19 at 09:58

sadik keskin

1
3

score 0 · Answer 18 · answered Jun 25 '20 at 05:56

0

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

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

answered Jun 25 '20 at 05:56

Sasindu

1

score 0 · Answer 19 · answered Jun 17 '21 at 19:05

I am using Xampp 7.3.28-1 for Linux. It uses MariaDB 10.4.19. Its configuration file is: /opt/lampp/etc/my.cnf

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]: sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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

score 0 · Answer 20 · answered Nov 21 '21 at 10:34

0

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.

answered Nov 21 '21 at 10:34

Spinstaz

287
6
12

score 0 · Answer 21 · answered Apr 19 '23 at 17:37

0

in my case Centos 8 Directadmin Mariadb

Go to and edit:

- /etc/my.cnf

Add this text in last line - save file, and restart mysql

sql-mode=""

Final same as:

[mysqld]
...
sql-mode=""

Don't forget

/bin/systemctl restart mysqld.service

answered Apr 19 '23 at 17:37

Hoàng Vũ Tgtt

1,863
24
8

mysql error 1364 Field doesn't have a default values

21 Answers21

Linked

Related