Invalid default value for 'timestamp'

Question

i am getting error in my database. i am encountering invalid default value for timestamp.

here's my database:

CREATE TABLE IF NOT EXISTS `post` (
`id` int(11) NOT NULL,
  `text` varchar(10000) NOT NULL,
  `threadId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `isModified` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `color` varchar(10) DEFAULT '#00bcd4',
  `icon` varchar(100) NOT NULL DEFAULT 'https://mymonas.com/forum/category_icon/ic_question.png'
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

Can I suggest you change your character set to `UTF8mb4`, as this will sidestep issues with wyrd characters in the future. [read more here](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Martin, Apr 24 '16 at 11:23
same problem i got ,#1067 - Invalid default value for 'timestamp' — Denver Bautista, Apr 25 '16 at 02:38
Does this answer your question? [Mysql datetime DEFAULT CURRENT\_TIMESTAMP error](https://stackoverflow.com/questions/23153822/mysql-datetime-default-current-timestamp-error) — miken32, Dec 05 '20 at 19:25

score 8 · Answer 1 · answered Sep 23 '16 at 19:40

8

I was having the same problem, I changed type from "datetime" to "timestamp" and It worked. I have mysql 5.5.52.

Mysql_error

answered Sep 23 '16 at 19:40

Raul A.

81
1
5

score 4 · Answer 2 · answered Oct 22 '18 at 12:37

4

I have the same issue in sql_mode.

Make query:

show variables like 'sql_mode' ;

You need to remove the "NO_ZERO_IN_DATE,NO_ZERO_DATE" from sql_mode.

SET sql_mode = '';

answered Oct 22 '18 at 12:37

Max Sherbakov

1,817
16
21

score 3 · Answer 3 · answered Apr 20 '16 at 14:37

3

Use CURRENT_TIMESTAMP() instead CURRENT_TIMESTAMP

i.e.

CREATE TABLE IF NOT EXISTS `post` (
`id` int(11) NOT NULL,
  `text` varchar(10000) NOT NULL,
  `threadId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `dateCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  `isModified` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=latin1;

Now() works as well

answered Apr 20 '16 at 14:37

Pavel Zimogorov

1,387
10
24

same error again. error not fix i just put () but it wont work – Denver Bautista Apr 21 '16 at 13:15
Which version of mySQL do you use – Pavel Zimogorov Apr 21 '16 at 13:36
1

Phpmyadmin Version information: 4.0.10.7, latest stable version: 4.6.0 and Database client version: libmysql - 5.1.73 – Denver Bautista Apr 21 '16 at 13:43
Server type: MySQL Server version: 5.5.48-cll – Denver Bautista Apr 21 '16 at 13:59
try to use NOW() instead it – Pavel Zimogorov Apr 21 '16 at 14:12
2

i found what is wrong timestamp was be added in MySQL only in 5.6.4 version. CURRENT_TIMESTAMP since 5.6.5 https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html – Pavel Zimogorov Apr 21 '16 at 14:32
o i see. then instead of timestamp what should i use? – Denver Bautista Apr 21 '16 at 14:35
2

Just datetime. And MySQL did not have a way to use default value before 5.6.5. You should add date in insert statement – Pavel Zimogorov Apr 21 '16 at 14:42

score 1 · Answer 4 · answered Feb 19 '18 at 07:43

From the MySQL 5.5 manual:

"You cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."

The changes in MYSQL 5.6.x that allow the functionality are documented here:

"As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table."

So, this means you are using an older version of mysql, either you can use datetime data type of upgrade your mysql version

score 1 · Answer 5 · answered Mar 16 '20 at 06:23

Answered by @max Sherbakov worked but I think its risky,

if you execute SET sql_mode = ''; query.

Because if you or other users SET any different variables in sql_mode

like NO_ENGINE_SUBSTITUTION check other SQL MODES

by changing sql_mode values in my.ini file

OR

using SET sql_mode = 'YOUR_VARIABLE_LIST'; query

it worked for you current situation

but create problem in other projects.

To view current sql mode use following query

show variables like 'sql_mode' ;

Invalid default value for 'timestamp'

5 Answers5

Linked