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

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> 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:

For Mysql8.0.18:

CURRENT_TIMESTAMP([fsp])

Remove "([fsp])", resolved my problem.

For Mysql5.7, login in mysql command line and run the command,

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

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.

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

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)

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

2. Stop MariaDB, if already running service mariadb stop

3. Perform update

   yum update

4. Starting MariaDB & 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.

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.

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 []> insert into ttt (id) VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB []> 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 []>

1. 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> show variables like 'sql_mode';

   You would see an output like below

   

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

3. Restart mysql server

   $ /etc/init.d/mysql restart

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

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.

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!

Run this query:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

it works for me