Invalid default value for 'create_date' timestamp field

Mysql

Mysql Problem Overview

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?

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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

Solution 3 - Mysql

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

Solution 4 - Mysql

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

Solution 5 - Mysql

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.

Solution 6 - Mysql

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.

Solution 7 - Mysql

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.

Solution 8 - Mysql

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

Solution 9 - Mysql

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

Solution 10 - Mysql

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

Solution 11 - Mysql

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.

Solution 12 - Mysql

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 ,

Solution 13 - Mysql

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

Solution 14 - Mysql

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.

Solution 15 - Mysql

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.

Solution 16 - Mysql

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

Solution 17 - Mysql

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

Solution 18 - Mysql

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

Categories
Recommended Mysql Solutions

Previous Solution:

How do you get the magnitude of a vector in Numpy?

PythonNumpy

Next Solution:

How do you get current active/default Environment profile programmatically in Spring?

JavaSpringSpring Profiles

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionrobertView Question on Stackoverflow
Solution 1 - MysqlDevartView Answer on Stackoverflow
Solution 2 - MysqlPankaj ShresthaView Answer on Stackoverflow
Solution 3 - MysqlBret VvVvView Answer on Stackoverflow
Solution 4 - MysqlMubashar AbbasView Answer on Stackoverflow
Solution 5 - MysqlAshish OdichView Answer on Stackoverflow
Solution 6 - MysqlJoy ZhuView Answer on Stackoverflow
Solution 7 - MysqlAntonio ReyesView Answer on Stackoverflow
Solution 8 - MysqldgitmanView Answer on Stackoverflow
Solution 9 - MysqlpybView Answer on Stackoverflow
Solution 10 - MysqlJatin BhattiView Answer on Stackoverflow
Solution 11 - MysqlDavid BeckwithView Answer on Stackoverflow
Solution 12 - Mysql荒木 知View Answer on Stackoverflow
Solution 13 - MysqlBojan HrnkasView Answer on Stackoverflow
Solution 14 - MysqlRobert HookView Answer on Stackoverflow
Solution 15 - MysqlKot MatroskinView Answer on Stackoverflow
Solution 16 - MysqlLucas BustamanteView Answer on Stackoverflow
Solution 17 - MysqlmzzhaafView Answer on Stackoverflow
Solution 18 - MysqlAnkush SinghalView Answer on Stackoverflow