0

Server version: 5.6.22 Homebrew

The table:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

By default, I want the valid_until column to be the current time + 1 day (or whatever). I've tried settings the 'default' to DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY) but this failed. I also tried changing the type to date, timestamp, time and year but those give the same error:

ALTER TABLE `sessions` CHANGE `valid_until` `valid_until` DATETIME
 NOT NULL
 DEFAULT 'DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY)'

MySQL said: Invalid default value for 'valid_until'

1 Answer 1

0

From the manual:

With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that 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 TIMESTAMP and DATETIME columns.

The way to workaround this is to use a trigger.

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions` 
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));
1
  • Ah, I thought it had something to do with no calculations allowed in the default section. This verifies that, couldn't find it in the docs. Must've read over it. Thank you!
    – Rowan Kaag
    Apr 22, 2015 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.