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

what are the default values for these columns ? Can you please share table structure ? — Priyanshu, Apr 27 '16 at 06:39
Step by step instruction for resolving such problem described in: https://stackoverflow.com/a/69733605/2677449 — Murat Kurbanov, Oct 27 '21 at 06:12

score 226 · Accepted Answer · edited May 25 '19 at 20:46

226

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

edited May 25 '19 at 20:46

pernpas

199
3
17

answered Jun 08 '16 at 07:43

Aman Aggarwal

17,619
9
53
81

7

I know this, but in my server sql_mode showing blank, Still not working, I am using [Server version: 5.5.53-0ubuntu0.12.04.1 - (Ubuntu)]. Any one can have any solution without upgrading server version? – Diptesh Atha Dec 19 '16 at 11:30
have you checked by global command ? and is there nothing in sql_mode for same session ? – Aman Aggarwal Dec 19 '16 at 11:37
I checked show variables like 'sql_mode' ; and the output is Variable_name|Value = sql_mode| – Diptesh Atha Dec 19 '16 at 11:46
So guys, there is no way! OK I am going to upgrade server. Thanks! – Diptesh Atha Dec 19 '16 at 11:49
I found a temporary solution ->create trigger and set date field to now() – Diptesh Atha Dec 19 '16 at 11:54
This answer shows you how to see the value but not how to change it. For those wondering, this variable is stored in the "my.cnf" file on the server. Changing it there will affect the default so it will be persistent. `SET sql_mode` is temporary. – Typel Sep 27 '17 at 17:41
2

This will be useful for this answer https://stackoverflow.com/questions/2317650/setting-global-sql-mode-in-mysql – Preshan Pradeepa Oct 13 '17 at 05:55
4

Didn't work for me with 5.7. Not sure if I had to do it globally or not. – Brett Feb 28 '19 at 19:18
First check the sql_mode: Run command: `select @@sql_mode;` You will get mode list. Then run `SET sql_mode = '';` then select you database by run `use database_name`; then run your code which has error and at last again run `SET sql_mode = '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';` this has all that option which you get by first command run – Hemant Mar 03 '20 at 12:00
this is not working with MySQL 5.7 , getting this response : ```0 row(s) affected, 1 warning(s): 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.``` – charany1 Jul 15 '21 at 09:02
1

Just an note for XAMPP users like me, you can set this value by opening config for mysql in the XAMPP console. Open my.ini and search for 'mode'. Edit, save, restart. Yep, noob tip. Meh. – RushVan Sep 16 '21 at 21:16
This doesn't work on newer versions of mariadb either. You can run both queries (non-global and global), then try an import a database. The import will fail. – bob Oct 11 '21 at 07:19
Exported a WordPress database with Navicat, and attempted to import to WP Local which uses Adminer and got the error but thankfully in adminer entering the second command in this answer resolves that! – TechRemarker Feb 02 '23 at 21:15

score 129 · Answer 2 · edited Dec 24 '20 at 21:10

129

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.

edited Dec 24 '20 at 21:10

CODE-REaD

2,819
3
33
60

answered Oct 28 '16 at 10:52

Ahmed Mohamed

1,475
1
8
6

8

Thanks for the tip. But only removing date restrictions `NO_ZERO_IN_DATE,NO_ZERO_DATE` only allows us to maintain other security features : `SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';` – KeitelDOG Jan 16 '19 at 20:11
2

For clarification, this statement should be used only in development, not in production. – Ahmed Mohamed Apr 16 '19 at 23:23
I suggest this shouldn't even be used in development, one should fix the problem instead of disabling this. – Syed M. Sannan May 08 '23 at 18:08

score 40 · Answer 3 · edited Feb 18 '21 at 11:16

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.

Martin Mohan · Answer 4 · 2017-05-23T15:30:37.660

32

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

edited May 23 '17 at 15:30

answered May 23 '17 at 09:38

Martin Mohan

1,366
12
8

2

`SET sql_mode = '';` is now deprecated, `SET GLOBAL sql_mode = ''` is OK – Vadim Anisimov Apr 25 '19 at 14:55

score 9 · Answer 5 · edited Jan 04 '21 at 17:26

9

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!

edited Jan 04 '21 at 17:26

Matt

12,848
2
31
53

answered Jan 11 '17 at 20:17

Tomás

119
2
4

score 6 · Answer 6 · answered Nov 09 '16 at 00:19

6

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.

answered Nov 09 '16 at 00:19

Bhushan

18,329
31
104
137

score 5 · Answer 7 · answered Aug 01 '17 at 17:25

5

Run this query:

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

it works for me

answered Aug 01 '17 at 17:25

nyenius

67
1
3

1

by the way don't need SET time_zone = "+00:00"; – Şafak Çıplak Jan 24 '20 at 14:19

malajisi · Answer 8 · 2020-11-05T03:53:19.277

5

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!

edited Nov 05 '20 at 03:53

answered Jan 09 '19 at 10:27

malajisi

2,165
1
22
18

score 4 · Answer 9 · answered Apr 27 '16 at 07:24

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

score 4 · Answer 10 · edited Mar 12 '19 at 16:22

4

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

edited Mar 12 '19 at 16:22

Brad Larson

170,088
45
397
571

answered Mar 12 '19 at 12:45

Tareq Gamal El-din

51
1

Muhammad Hassaan · Answer 11 · 2018-11-24T19:47:19.587

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

score 2 · Answer 12 · answered May 23 '20 at 05:02

2

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

answered May 23 '20 at 05:02

Thushan

1,220
14
14

Man! This answer should be way up high. It addresses the issue from the very root. Explains it clearly and even gives you what you need to copy/paste. The perfect response. I learned and got the work done. – Panchew Aug 30 '22 at 18:36
This is not working in my case. When I remove the `NO_ZERO_IN_DATE, NO_ZERO_DATE` modes, the server won't start up anylonger. – dns_nx Jan 27 '23 at 12:09

score 2 · Answer 13 · answered Jul 23 '21 at 02:48

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

score 1 · Answer 14 · answered Mar 22 '21 at 09:25

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.

score 0 · Answer 15 · answered Dec 30 '19 at 17:06

0

For Mysql8.0.18:

CURRENT_TIMESTAMP([fsp])

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

answered Dec 30 '19 at 17:06

imldp

1

score 0 · Answer 16 · answered Mar 17 '21 at 22:52

0

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

or

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

answered Mar 17 '21 at 22:52

Dicka Kumara Syahlan

1
1
3

score 0 · Answer 17 · answered Jan 11 '22 at 05:29

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

Connect to MySQL server mysql -u root -p
Display sql_mode variables using the 'query': show variables like 'sql_mode';
exitafter copying the string output
Edit the configuration file at sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
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.
Run Sudo service mysql restart apply changes.

score 0 · Answer 18 · answered Oct 11 '22 at 20:52

0

I was facing this error with phpMyAdmin (5.2.0) because I wanted to add those two columns (created_at and updated_at) to an existing table, the way I solved it was just putting not null on those columns.

answered Oct 11 '22 at 20:52

Bonestorm

119
1
6

score 0 · Answer 19 · answered Jun 24 '23 at 12:29

0

Run this query:

SET sql_mode = ''; ALTER TABLE investments MODIFY created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

if (SET sql_mode = '';) not work replace with (SET GLOBAL sql_mode = '';)

answered Jun 24 '23 at 12:29

ELYAS AYALEW

31
1
6

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

19 Answers19

Add SETtings to header of backup

Add restore SETtings to end

Linked

Related