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.

Answer 1

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.

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

Answer 3

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'

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

Answer 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!

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

Modify your query and add "IGNORE" as:

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

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

Answer 12

For Windows WampServer users:

WAMP > MySQL > my.ini

search file for sql-mode=""

Uncomment it.

Answer 13

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.

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

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

Answer 16

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

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

Answer 17

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.