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

User · Answer

I think in name column have null values in this case  update try set name  abc  where created by  def

User · Answer

For Windows WampServer users   WAMP   MySQL   my ini  search file for sql-mode     Uncomment it

User · Answer

Modify your query and add  IGNORE  as   INSERT IGNORE INTO   mytable      field1      field2   VALUES   value1     value2

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] mysql error 1364 Field doesn't have a default values

Examples related to mysql