Invalid default value for create date timestamp field

Question

I have the following sql create statement  mysql gt  CREATE  TABLE IF NOT EXISTS  erp   je menus        - gt     id  INT 11  NOT NULL AUTO INCREMENT       - gt     name  VARCHAR 100  NOT NULL       - gt     description  VARCHAR 255  NOT NULL       - gt     live start date  DATETIME NULL DEFAULT NULL       - gt     live end date  DATETIME NULL DEFAULT NULL        - gt     notes  VARCHAR 255  NULL       - gt     create date  TIMESTAMP NOT NULL DEFAULT   0000-00-00 00 00 00       - gt     created by  INT 11  NOT NULL       - gt     update date  TIMESTAMP NOT NULL DEFAULT  CURRENT TIMESTAMP        - gt     updated by  INT 11  NOT NULL        - gt     status  VARCHAR 45  NOT NULL       - gt    PRIMARY KEY   id          - gt  ENGINE   InnoDB    giving following error  ERROR 1067  42000   Invalid default value for  create date   What is the error here

User · Answer

Using OS X  install mysql from Homebrew  System Variables based on its compiled-in defaults   Solution is to remove  NO ZERO DATE  from System Variables  sql mode    Just please keep in mind that scope involve   If you want to affect only in your session  please use    session   For example   SET   session sql mode   ONLY FULL GROUP BY STRICT TRANS TABLES NO ZERO IN DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION     In this case  it will not affect once your session ends or your change it  It has not effect on other session   If you want to affect on all client  please use    global   for example   SET   global sql mode   ONLY FULL GROUP BY STRICT TRANS TABLES NO ZERO IN DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION     In this case  it only affects on the clients that connect after the change not affect on current all clients   and will not work once server exit

User · Answer

Change this    create date  TIMESTAMP NOT NULL DEFAULT   0000-00-00 00 00 00    update date  TIMESTAMP NOT NULL DEFAULT  CURRENT TIMESTAMP      To the following    create date  TIMESTAMP NOT NULL DEFAULT  CURRENT TIMESTAMP    update date  TIMESTAMP NOT NULL DEFAULT  CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP

User · Answer

That is because of server SQL Mode - NO ZERO DATE   From the reference  NO ZERO DATE - In strict mode  don t allow  0000-00-00  as a valid date  You can still insert zero dates with the IGNORE option  When not in strict mode  the date is accepted but a warning is generated

User · Answer

I try to set type of column as  timestamp  and it works for me

User · Answer

In ubuntu desktop 16 04  I did this    open file   etc mysql mysql conf d mysqld cnf in an editor of your choice  Look for  sql mode  it will be somewhere under  mysqld   and set sql mode to the following   NO ZERO IN DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION Save and then restart mysql service by doing   sudo service mysql restart

User · Answer

You could just change this  create date datetime NOT NULL DEFAULT  0000-00-00 00 00 00   To something like this  create date varchar 80  NOT NULL DEFAULT  0000-00-00 00 00 00

User · Answer

You might like to examine the timezone setting on the MySql instance   mysql gt  show variables like  time zone    --------------- --------    Variable name   Value     --------------- --------    time zone       SYSTEM    --------------- --------    in my case  I realised that the underlying system had it s timezone set to BST rather than UTC  and so in the create table the default of  1970-01-01 00 00 01  was being coerced back 1 hour  resulting in an invalid timestamp value   For me  I actually wanted the machine s timezone set to UTC  and that sorted me out  As I was running Centos 7  I simply did    timedatectl set-timezone UTC   and restarted everything

User · Answer

Default values should start from the year 1000    For example   ALTER TABLE mytable last active DATETIME DEFAULT  1000-01-01 00 00 00    Hope this helps someone

User · Answer

If you generated the script from the MySQL workbench   The following line is generated  SET  OLD SQL MODE   SQL MODE  SQL MODE  TRADITIONAL ALLOW INVALID DATES     Remove TRADITIONAL from the SQL MODE  and then the script should work fine  Else  you could set the SQL MODE as Allow Invalid Dates   SET SQL MODE  ALLOW INVALID DATES

User · Answer

TIMESTAMP has a range of  1970-01-01 00 00 01  UTC to  2038-01-19 03 14 07  UTC  see doc    The default value must be within that range     Other odd  related  behavior   CREATE TABLE tbl1       ts TIMESTAMP     Query OK  0 rows affected  0 01 sec   CREATE TABLE tbl2       ts TIMESTAMP      ts2 TIMESTAMP   ERROR 1067  42000   Invalid default value for  ts2   CREATE TABLE tbl3       ts TIMESTAMP      ts2 TIMESTAMP DEFAULT  1970-01-01 00 00 01    Query OK  0 rows affected  0 01 sec    Side note  if you want to insert NULLS   CREATE TABLE tbl4       ts TIMESTAMP NULL DEFAULT NULL

User · Answer

You could just change this    create date  TIMESTAMP NOT NULL DEFAULT  0000-00-00 00 00 00     To something like this    create date  TIMESTAMP NOT NULL DEFAULT  2018-04-01 12 00 00

User · Answer

I had a similar issue with MySQL 5 7 with the following code    update date  TIMESTAMP 3  NOT NULL DEFAULT CURRENT TIMESTAMP  I fixed by using this instead    update date  TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP

User · Answer

To avoid this issue  you need to remove NO ZERO DATE from the mysql mode configuration    Go to  phpmyadmin   Once phpmyadmin is loaded up  click on the  variables  tab  Search for  sql mode   Click on the Edit option and remove NO ZERO DATE  and its trailing comma  from the configuration    This is a very common issue in the local environment with wamp or xamp

User · Answer

Just Define following lines at top of your Database SQL file   SET SQL MODE    NO AUTO VALUE ON ZERO   SET time zone     00 00     It is working for me

User · Answer

To disable strict SQL mode  Create disable strict mode cnf file at  etc mysql conf d    In the file  enter these two lines    mysqld  sql mode IGNORE SPACE NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   Finally  restart MySQL with this command   sudo service mysql restart

User · Answer

I was able to resolve this issue on OS X by installing MySQL from Homebrew   brew install mysql   by adding the following to  usr local etc my cnf  sql mode ONLY FULL GROUP BY STRICT TRANS TABLES NO ZERO IN DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   and restarting MySQL  brew tap homebrew services brew services restart mysql

[mysql] Invalid default value for 'create_date' timestamp field

Examples related to mysql