ERROR 1067 42000 Invalid default value for created at

Question

When I tried to alter the table it showed the error   ERROR 1067  42000   Invalid default value for  created at    I googled for this error but all I found was as if they tried to alter the timestamp so it occurred  However here I am trying to add a new column and I am getting this error   mysql gt  ALTER TABLE investments ADD bank TEXT  ERROR 1067  42000   Invalid default value for  created at    and my table s last two columns are created at and updated at   Here is my table structure

User · Answer

First  check existing mode s  are using the following command in your terminal     mysql -u root -p -e  SHOW VARIABLES LIKE  sql mode     or  mysql gt  show variables like  sql mode    You would see an output like below   Disable mode s  via my cnf  In this case  you need to remove NO ZERO IN DATE  NO ZERO DATE modes  Open my cnf file  Generally you could find my cnf file located in   etc my cnf or  etc mysql my cnf   Update modes in my cnf under  mysqld  heading  sql mode ONLY FULL GROUP BY STRICT TRANS TABLES ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION  Here I have omitted NO ZERO IN DATE  NO ZERO DATE modes Restart mysql server     etc init d mysql restart

User · Answer

Try and run the following command  ALTER TABLE  investments   MODIFY created at TIMESTAMP  DEFAULT CURRENT TIMESTAMP  NOT NULL   and ALTER TABLE  investments   MODIFY updated at TIMESTAMP  DEFAULT CURRENT TIMESTAMP  NOT NULL   The reason you are getting this error is because you are not setting a default value for the created at and updated at fields  MySQL is not accepting your command since the values for these columns cannot be null

User · Answer

As mentioned in  Bernd Buffen s answer  This is issue with MariaDB 5 5  I simple upgrade MariaDB 5 5 to MariaDB 10 1 and issue resolved   Here Steps to upgrade MariaDB 5 5 into MariaDB 10 1 at CentOS 7  64-Bit    Add following lines to MariaDB repo   nano  etc yum repos d mariadb repo and paste the following lines        mariadb       name   MariaDB      baseurl   http   yum mariadb org 10 1 centos7-amd64      gpgkey https   yum mariadb org RPM-GPG-KEY-MariaDB      gpgcheck 1    Stop MariaDB  if already running  service mariadb stop Perform update  yum update Starting MariaDB  amp  Performing Upgrade  service mariadb start  mysql upgrade   Everything Done   Check MariaDB version  mysql -V  NOTE  Please always take backup of Database s  before performing upgrades  Data can be lost if upgrade failed or something went wrong

User · Answer

The problem is because of sql modes  Please check your current sql modes by command   show variables like  sql mode       And remove the sql mode  NO ZERO IN DATE NO ZERO DATE  to make it work  This is the default sql mode in mysql new versions   You can set sql mode globally as root by command    set global sql mode    ONLY FULL GROUP BY STRICT TRANS TABLES ERROR FOR DIVISION BY ZERO NO ENGINE SUBSTITUTION

User · Answer

I had similar problem  Following solved it   Change   recollect date TIMESTAMP DEFAULT  CURRENT TIMESTAMP    to   recollect date TIMESTAMP DEFAULT CURRENT TIMESTAMP   i e  just remove the quotes around CURRENT TIMESTAMP   Hope this helps someone

User · Answer

Simply  before you run any statements put this in the first line  SET sql mode        PLEASE NOTE  this statement should be used only in development  not in production

User · Answer

For Mysql5 7  login in mysql command line and run the command  mysql gt  show variables like  sql mode     It will show that NO ZERO IN DATE NO ZERO DATE in sql mode   Try to add a line below  mysqld  in your mysql conf file to remove the two option  mine mysql 5 7 on Ubuntu 16  is  etc mysql mysql conf d mysqld cnf  Now restart mysql  It works

User · Answer

Just convert it by this line     for the new table    CREATE TABLE t1     ts TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP        for Existing Table   Alter ts TIMESTAMP DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP     Source     https   dev mysql com doc refman 5 5 en timestamp-initialization html

User · Answer

I came across the same error while trying to install a third party database   I tried the solution proposed unsuccessfully i e   SET sql mode         Then I tried the command below which worked allowing the database to be installed SET GLOBAL sql mode

User · Answer

In my case  I have a file to import  So I simply added SET sql mode       at the beginning of the file and it works

User · Answer

Run this query   SET SQL MODE    NO AUTO VALUE ON ZERO   SET time zone     00 00     it works for me

User · Answer

You can do it like this    CREATE TABLE  ttt       id  INT 11  UNSIGNED NOT NULL AUTO INCREMENT     t1  TIMESTAMP  NULL DEFAULT  0000-00-00 00 00 00      t2  TIMESTAMP  NULL DEFAULT  0000-00-00 00 00 00      t3  TIMESTAMP  NULL DEFAULT  0000-00-00 00 00 00      t4  TIMESTAMP  NULL DEFAULT 0    PRIMARY KEY   id     ENGINE INNODB DEFAULT CHARSET utf8     Because the TIMESTAMP value is stored as Epoch Seconds  the timestamp value  1970-01-01 00 00 00   UTC  is reserved since the second  0 is used to represent  0000-00-00 00 00 00   In MariaDB 5 5 and before there could only be one TIMESTAMP column per table that had CURRENT TIMESTAMP defined as its default value  This limit has no longer applied since MariaDB 10 0    see  https   mariadb com kb en mariadb timestamp   sample  MariaDB    gt  insert into ttt  id  VALUES  1   2   3   Query OK  3 rows affected  0 01 sec  Records  3  Duplicates  0  Warnings  0  MariaDB    gt  select   from ttt   ---- --------------------- --------------------- --------------------- ---------------------    id   t1                    t2                    t3                    t4                     ---- --------------------- --------------------- --------------------- ---------------------     1   0000-00-00 00 00 00   2000-01-01 12 01 02   0000-00-00 00 00 00   0000-00-00 00 00 00      2   0000-00-00 00 00 00   2000-01-01 12 01 02   0000-00-00 00 00 00   0000-00-00 00 00 00      3   0000-00-00 00 00 00   2000-01-01 12 01 02   0000-00-00 00 00 00   0000-00-00 00 00 00    ---- --------------------- --------------------- --------------------- ---------------------  3 rows in set  0 00 sec   MariaDB    gt

User · Answer

For Mysql8 0 18   CURRENT TIMESTAMP  fsp     Remove    fsp     resolved my problem

[mysql] ERROR 1067 (42000): Invalid default value for 'created_at'

Examples related to mysql