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;

Answer 1

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

Mysql_error

Answer 2

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