1

I have a date column whose default value is CURRENT_TIMESTAMP. It works fine locally, but when I did an export and tried to create the database on my hosting I get this:

`Invalid default value for 'created'`

...from this code:

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` varchar(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cloned` int(11) DEFAULT NULL,
  `event_id` varchar(11) NOT NULL,
  `amount_due` smallint(6) NOT NULL,
  `vat` tinyint(2) NOT NULL,
  `discount` tinyint(4) DEFAULT NULL,
  `date_paid` date DEFAULT NULL,
  `notes` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The answer to this question says that, apparently, CURRENT_TIMESTAMP is an acceptable default only for datetime, not date, columns. But like I say, it works locally, just not remotely.

Could it be to do with the difference in MySQL versions?

  • Local MySQL: protocol v10 / server v5.6.16

  • Remote MySQL: protocol v10 / server v5.5.35

Am I getting away with this locally because of the higher server version?

Community
  • 1
  • 1
Mitya
  • 33,629
  • 9
  • 60
  • 107
  • "CURRENT_TIMESTAMP is an acceptable default only for datetime, not date, columns" - In your SQL `created` is a datetime column... – ODaniel Sep 03 '14 at 12:05
  • May be this is the reason http://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla – senK Sep 03 '14 at 12:07
  • @ODaniel - well spotted, so that matter would appear to be n/a to me. Senk - thanks, I just had a read of that. Seems like it could be a version thing, then. – Mitya Sep 03 '14 at 12:12

3 Answers3

2

This won't work for MySQL 5.5; before mysql 5.6.5 CURRENT_TIMESTAMP works only for TIMESTAMP.

http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

It's working for you locally, because you are using MySQL 5.6.16 there.

mrinx
  • 71
  • 4
1

In the MySQL manual a Timestamp datatype is recommended for use with "DEFAULT CURRENT_TIMESTAMP".

Also consider this (taken from the MySQL manual): One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

ODaniel
  • 566
  • 4
  • 13
1

try using

created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

Amit Deshmukh
  • 23
  • 3