Create a default value for session valid_until MySQL

Question

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'

Answer 1

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));

• Rowan Kaag: 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!