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:

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

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.

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.

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

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!

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.

Run this query:

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

it works for me

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

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<br> > gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB<br> > 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.

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

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!

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

For those receiving this error when using Navicat to transfer or import data, in my case from MariaDB to an old version of MySQL..

Try enabling "Use DDL from SHOW CREATE TABLE" under the Date Transfer > Advanced Tab

navicat data transfer advanced tab settings

For Mysql8.0.18:

CURRENT_TIMESTAMP([fsp])

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

the simplest way is by adding current timestamp to default value.

or

by add this by sql = "... DEFAULT CURRENT_TIMESTAMP;"

The question is too broad for the answer. There are many problems regarding these questions, even more so when the incompatibility of the different MySQL-based engines is notorious. For me the best option is to know the state of variables at the time of making the backup with the option --opt (mysqldump --opt) and apply it to our backup if it does not have it as usual, either because the original backup I did not have it, or because the one that has happened to us is incorrect.

If the backup does not contain the settings with which it was made, we will have to start investigating, but basically we can do it like this.

Add SETtings to header of backup

echo '
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;' | cat - mybackup.sql > temp && mv temp  mybackup.sql 

Add restore SETtings to end

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=@OLD_INNODB_STATS_AUTO_RECALC */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;' >> mybackup.sql 

If you don't have those settings you can make a mysqldump backup with the --opt option on the original server, in order to get them.

If you don't have it, you can go little by little, setting the necessary settings, both at the start and at the exit.

I bumped into a similar challenge with Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu) and solved using the following trick

1. Connect to MySQL server mysql -u root -p
2. Display sql_mode variables using the 'query': show variables like 'sql_mode';
3. exitafter copying the string output
4. Edit the configuration file at sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
5. If your config file is missing sql_mode =, sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ommiting NO_ZERO_IN_DATE, NO_ZERO_DATE from your string.
6. Run Sudo service mysql restart apply changes.