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.
Answers:
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 http://dev.mysql.com/doc/refman/5.6/en/option-files.html 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:
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'
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:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
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:
SHOW VARIABLES LIKE 'sql_mode';
To disable strict mode:
SET GLOBAL sql_mode='';
Method 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');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
[mysqld] port = 3306 sql-mode=""
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:
/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.
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:
/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%’;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0