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?
This question is related to
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 ,
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.
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.
I try to set type of column as 'timestamp' and it works for me.
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.
To avoid this issue, you need to remove NO_ZERO_DATE
from the mysql mode configuration.
NO_ZERO_DATE
(and its trailing comma) from the configuration.This is a very common issue in the local environment with wamp or xamp.
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
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
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
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);
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';
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',
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',
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.
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
Source: Stackoverflow.com