CODEWITHSUNDEEP
mysql
robert
robert

Reputation: 8717

Invalid default value for 'create_date' timestamp field

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?

Upvotes: 252

Views: 447176

Answers (20)

Markus
Markus

Reputation: 1

Just posting in case anybody runs into the similar issue that I had using MySql 5.6. I was receiving an invalid default error for the following line:

`last_observed_time` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',

This shouldn't throw an error because the date is within unix time, however, the default time is not specified as unix time, but rather by whatever timezone your mysql database is set to use (in my case, SYSTEM time in Australia, which is GMT+10.

Because Australian Eastern Time is 10 hours ahead of GMT, '1970-01-01 00:00:01' converts to GMT (unix) time as '1969-12-30 14:00:01', which is an invalid datetime.

This can be solved either by setting the session or global time zone on mysql.

SET @@session.time_zone = '+00:00';

OR

SET @@global.time_zone = '+00:00';

Upvotes: 0

Maulik patel
Maulik patel

Reputation: 2442

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;

Upvotes: -1

Vitaly Filatenko
Vitaly Filatenko

Reputation: 496

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.

Upvotes: 2

Devart
Devart

Reputation: 121922

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.

Upvotes: 223

Bojan Hrnkas
Bojan Hrnkas

Reputation: 1694

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

Upvotes: 3

mzzhaaf
mzzhaaf

Reputation: 11

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

Upvotes: -2

Ankush Singhal
Ankush Singhal

Reputation: 31

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

Upvotes: -3

荒木 知
荒木 知

Reputation: 39

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 ,

Upvotes: 3

Ashish Odich
Ashish Odich

Reputation: 571

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.

Upvotes: 27

David Beckwith
David Beckwith

Reputation: 2789

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.

Upvotes: 2

Pankaj Shrestha
Pankaj Shrestha

Reputation: 2169

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

Upvotes: 208

Mubashar Abbas
Mubashar Abbas

Reputation: 5663

In ubuntu desktop 16.04, I did this:

  1. open file: /etc/mysql/mysql.conf.d/mysqld.cnf in an editor of your choice.

  2. Look for: sql_mode, it will be somewhere under [mysqld].

  3. and set sql_mode to the following:

    NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  4. Save and then restart mysql service by doing:

    sudo service mysql restart

Upvotes: 49

Antonio Reyes
Antonio Reyes

Reputation: 536

To avoid this issue, you need to remove NO_ZERO_DATE from the mysql mode configuration.

  1. Go to 'phpmyadmin'.
  2. Once phpmyadmin is loaded up, click on the 'variables' tab.
  3. Search for 'sql mode'.
  4. 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.

Upvotes: 13

Lucas Bustamante
Lucas Bustamante

Reputation: 17198

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

Upvotes: -2

Jatin Bhatti
Jatin Bhatti

Reputation: 31

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

Upvotes: 3

pyb
pyb

Reputation: 5269

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

Upvotes: 10

Robert Hook
Robert Hook

Reputation: 77

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.

Upvotes: 0

dgitman
dgitman

Reputation: 341

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

Upvotes: 8

Joy Zhu
Joy Zhu

Reputation: 464

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.

Upvotes: 13

Bret VvVv
Bret VvVv

Reputation: 799

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

Upvotes: 74

Related Questions