Invalid default value for 'create_date' timestamp field

Question

I have the following sql create statement

mysql> CREATE  TABLE IF NOT EXISTS `erp`.`je_menus` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT ,
    ->   `name` VARCHAR(100) NOT NULL ,
    ->   `description` VARCHAR(255) NOT NULL ,
    ->   `live_start_date` DATETIME NULL DEFAULT NULL ,
    ->   `live_end_date` DATETIME NULL DEFAULT NULL , 
    ->   `notes` VARCHAR(255) NULL ,
    ->   `create_date` TIMESTAMP NOT NULL DEFAULT  '0000-00-00 00:00:00',
    ->   `created_by` INT(11) NOT NULL ,
    ->   `update_date` TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP  ,
    ->   `updated_by` INT(11) NOT NULL , 
    ->   `status` VARCHAR(45) NOT NULL ,
    ->   PRIMARY KEY (`id`) ) 
    -> ENGINE = InnoDB;

giving following error

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

What is the error here?

I can't see anything wrong with your query and it works on 5.1.50-community just tested. — Jaspreet Chahal, Feb 08 '12 at 11:07
i use mysql 5.1.56 community in ubuntu 10.04. and not working — robert, Feb 08 '12 at 11:24
The no zero date requires a date. Use '1970-01-01 00:00:01'. [taken from here][1] [1]: http://dba.stackexchange.com/questions/6171/invalid-default-value-for-datetime-when-changing-to-utf8-general-ci — Jadeye, Oct 28 '14 at 16:29
I know many people don't like NULL fields in their databases, but isn't NULL (which means "unknown" or "undefined") here better than '0000-00-00'? That's like using `age = -1` instead of `age = NULL` — golimar, Oct 26 '20 at 16:52

score 218 · Accepted Answer · edited Jan 19 '22 at 01:54

218

That is because of server SQL Mode - NO_ZERO_DATE.

From the reference: NO_ZERO_DATE - In strict mode, doesn'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.

edited Jan 19 '22 at 01:54

Ares

5,905
3
35
51

answered Feb 08 '12 at 11:24

Devart

119,203
23
166
186

27

how do i give ignore option? – robert Feb 08 '12 at 11:27
10

You cannot ignore this option. This is server option. If you have access to my.ini (mysql configuration file), then remove NO_ZERO_DATE from sql-mode option and restart server. – Devart Feb 08 '12 at 11:44
12

To check this option - run SHOW VARIABLES LIKE 'sql_mode' – Devart Feb 08 '12 at 11:46
6

i generated the script using mysql workbench. In the script the sql_mode is set to traditional. If I remove the traditional, the script works. – robert Feb 09 '12 at 04:43
18

In MySQL Workbench preferences, go to tab 'Model: MySQL'. There set 'SQL_MODE to be used in generated scripts' to "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" That solves the problem for good. – sgtdck Jan 28 '14 at 20:07
3

you can check your sql_mode using the commands (note there are two levels): SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; – Pitt Aug 18 '16 at 21:50
If you do not have administation rights for the server, you can just set the sql mode for the session: SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; – Bojan Hrnkas Aug 03 '21 at 07:58

score 198 · Answer 2 · edited Aug 02 '18 at 08:57

198

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

edited Aug 02 '18 at 08:57

wittich

2,079
2
27
50

answered Apr 07 '15 at 23:57

Pankaj Shrestha

2,069
1
11
11

2

Saved me some time searching why the hell it is not working :) – Srneczek Jun 05 '15 at 11:12
7

Ahhh thank you. SET SQL_MODE='ALLOW_INVALID_DATES'; was a life saver. I had this problem when I trie migrating a wordpress site to another server (both local) and it would not let me import the database data due to this error, even though there were no rows in the tables with this error. – Michael K Dec 11 '16 at 06:19
1

Worked like a charm! Thank you. – moreirapontocom Sep 27 '19 at 21:05
SET SQL_MODE='ALLOW_INVALID_DATES'; did the trick before the executing the query – Sumit Wadhwa May 11 '21 at 15:47
1

To get this to work in MySQL 8 I had to add global : `SET GLOBAL SQL_MODE ='ALLOW_INVALID_DATES';` – Sjoerd Nov 08 '21 at 11:46

score 72 · Answer 3 · answered May 24 '16 at 18:07

72

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

answered May 24 '16 at 18:07

Bret VvVv

779
6
5

1

This is happening to me. Wth is going on? ts2 is not even "NOT NULL"...! – PedroD Mar 14 '18 at 18:13
3

Might be because "If you do not set a value for the first TIMESTAMP column in a table, MariaDB will automatically assign it the current date and time when performing an UPDATE or INSERT query on the row(s) in question." – [MariaDB Docs](https://mariadb.com/kb/en/library/timestamp/) – jsphpl Apr 08 '18 at 07:42
3

I like the use of `column_name TIMESTAMP DEFAULT NOW()`. May not be appropriate for every situation but thought I'd share since I was dealing with this too. – Andrew Wynham Aug 01 '18 at 15:20
2

column_name TIMESTAMP DEFAULT '1970-01-01 00:00:01' did the trick for me. Thanks! – code_gamer Apr 09 '19 at 10:21
1

Thank you! This worked for me when adding a column with liquibase like so. ` ` – Nunchucks Feb 03 '21 at 03:07
@code_gamer worked for me. It was `request_date date NOT NULL DEFAULT 'current_timestamp()'` and I changed it to `request_date date NOT NULL DEFAULT '1970-01-01 00:00:01'` The database was written for php 7.3 mysql 5.1 and I am using php 7.4 and mysql 5.7. Is there a way to update my sql file to suit mysql 5.7? – Umair Sep 12 '22 at 10:56
Main annoying thing for me is that this error message Invalid default value for 'ts2' only appears for second column (ts2). Why the same error is not shown for first column (ts)? – Oleksii Volynskyi Jul 20 '23 at 19:47

score 48 · Answer 4 · edited Jun 13 '18 at 01:06

48

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

edited Jun 13 '18 at 01:06

halfer

19,824
17
99
186

answered Dec 14 '16 at 06:53

Mubashar Abbas

5,536
4
38
49

9

It did helped, except that the `sql_mode` wasn't there for my instance of mySQL on ubuntu16.04. I had to add an entry for it in the file, by removing the "NO_ZERO_DATE". So, here is how it looks now: #Adding the below line to get rid of no_zero_date 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 – OK999 Dec 22 '16 at 03:50
you are right.. I added that earlier to disable the strict mode.. and when I edited it for this particular issue, the `sql_mode` entry was already there. – Mubashar Abbas Dec 25 '16 at 04:18
Is this a bug? CURRENT_TIMESTAMP should never return "0000-00-00 00:00:00", or I'm wrong? Why should I have to change mysqld settings? – Gianpaolo Scrigna Mar 01 '17 at 15:31
@letsjump because someone configured your mysql server incorrectly. i'm hitting this issue with a database I imported. the default value for the timestamp in a column is not a valid timestamp value. the REAL fix is to update the default timestamp to something valid like 1970 - the earliest date available. The temporary workaround is to disable checking on the database. – fIwJlxSzApHEZIl Sep 26 '18 at 20:42

score 27 · Answer 5 · answered Apr 08 '19 at 13:18

27

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.

answered Apr 08 '19 at 13:18

Ashish Odich

573
1
10
25

score 13 · Answer 6 · answered Jan 12 '17 at 03:07

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.

score 12 · Answer 7 · edited May 09 '18 at 00:57

12

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.

edited May 09 '18 at 00:57

mickmackusa

43,625
12
83
136

answered Mar 01 '18 at 20:32

Antonio Reyes

516
4
7

When I restart Mamp they reappear – The Sloth Sep 17 '20 at 09:08
For my Mac, changing it in phpmyadmin as suggested didn't work, as the sql mode was reset when MAMP restarted. As suggested from other searches, in /Applications/MAMP/conf, I created file my.cnf with the following content: `[mysqld] \n 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"` (replace '\n' with a carriage return). Cheers! jz – J.Z. Oct 28 '22 at 01:47

score 10 · Answer 8 · answered Oct 19 '17 at 18:53

10

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

answered Oct 19 '17 at 18:53

pyb

4,813
2
27
45

2

I think this is actually the best option for when it does make sense to default to current, it wouldn't however make sense for birth timestamp - just as an example. – meow Jul 07 '18 at 12:15
You'll loose precision (milli-seconds) – Keerthi Jan 13 '21 at 11:51
I was using the data type `Int(11)`, `date` and this problem happens to me. As you said, I converted it to `TIMESTAMP` and it worked. thanks – Aref Solaimany Jul 08 '22 at 20:09

score 8 · Answer 9 · answered Jul 05 '17 at 11:47

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

score 3 · Answer 10 · edited Nov 01 '17 at 10:45

3

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

edited Nov 01 '17 at 10:45

Peter Szekeli

2,712
3
30
44

answered Nov 01 '17 at 10:07

Jatin Bhatti

31
2

score 3 · Answer 11 · answered Aug 03 '21 at 08:00

3

If you do not have administation rights for the server, you can just set the sql mode for the current session:

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

answered Aug 03 '21 at 08:00

Bojan Hrnkas

1,587
16
22

score 2 · Answer 12 · edited Oct 18 '18 at 07:06

2

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.

edited Oct 18 '18 at 07:06

Barry

3,303
7
23
42

answered Jun 17 '18 at 03:23

David Beckwith

2,679
1
17
11

Nope. The major issue is in incorrect default day and month values, which can't be 00 (there are no 0 month and 0 day). – Vitaly Filatenko Jul 09 '22 at 19:24

score 2 · Answer 13 · edited Feb 03 '20 at 02:13

2

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 ,

edited Feb 03 '20 at 02:13

MEDZ

2,227
2
14
18

answered Feb 02 '20 at 13:26

荒木知

29
1

1

From Review: Hi, please don't answer just with source code. Try to provide a nice description about how your solution works. See: [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Thanks – sɐunıɔןɐqɐp Feb 02 '20 at 13:51
1

If this is a WordPress site, please do no update the Core WordPress tables. Many plugins expect a value of zeores, so WordPress cannot change this default value for legacy reasons that would break plugins. [Wordpress thread](https://core.trac.wordpress.org/ticket/41785) and [Wordpress support](https://wordpress.org/support/topic/invalid-default-value-for-user_registered/). Changing database structure without familiarity of code relying on the default values can lead to troubling bugs. While this may work as a solution in many cases, it could wreck havock in others. Not a universal solution. – SherylHohman Feb 25 '20 at 07:52

score 2 · Answer 14 · answered Jan 19 '22 at 09:50

I'm wondered to see so many answers, but no one had specified main reason: incorrect "ZERO" date format. In short, just use '0000-01-01 00:00:00' instead of '0000-00-00 00:00:00'. Both month 00 and day 00 are not valid values, obviously.

I agree with top user that the error was caused by MySQL setting NO_ZERO_DATE. This setting was introduced in new MySQL server releases to be enabled by default, surprisal for many developers. But please note that the main goal of this setting is not to make developers' life harder, but just force them to fix outdated table structures and avoid using such incorrect data structure format in future.

So, if you're creating new table and want to specify "ZERO" date, use '0000-01-01 00:00:00'.

If you have already created tables and obtaining this error (e.g. on inserting new records with omitting default value), just update your tables like that:

ALTER TABLE `erp`.`je_menus` 
MODIFY COLUMN `create_date` datetime(0) NOT NULL DEFAULT '0000-01-01 00:00:00'  AFTER `notes`;

Removing mentioned server setting is just temporarily solution and is not an example of good practice.

score 0 · Answer 15 · answered Jul 27 '17 at 12:33

You might like to examine the timezone setting on the MySql instance:

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

score -1 · Answer 16 · answered Jun 13 '22 at 07:43

ALTER TABLE `wp_actionscheduler_actions` CHANGE `scheduled_date_gmt` `scheduled_date_gmt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `scheduled_date_local` `scheduled_date_local` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `last_attempt_gmt` `last_attempt_gmt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `last_attempt_local` `last_attempt_local` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

score -2 · Answer 17 · answered Apr 06 '18 at 14:59

-2

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

answered Apr 06 '18 at 14:59

Lucas Bustamante

15,821
7
92
86

score -2 · Answer 18 · answered Sep 11 '20 at 18:02

-2

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

answered Sep 11 '20 at 18:02

mzzhaaf

11
1

score -3 · Answer 19 · answered Jul 14 '20 at 17:15

-3

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

answered Jul 14 '20 at 17:15

Ankush Singhal

11
3

Invalid default value for 'create_date' timestamp field

19 Answers19

Linked