My sql query looks like this
ALTER TABLE `exercises`
ADD COLUMN `creation_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `lesson_id`,
ADD COLUMN `modification_dt` timestamp NULL DEFAULT '' ON UPDATE CURRENT_TIMESTAMP AFTER `creation_dt`;
But getting error message
How can I fix this problem?
As shown in the screenshot, this is error code 1293. The reason for this error seems to be some implementation details in MySQL.
You could get around it by using a column of type DATETIME but this does not allow setting the current time as default value. However, you can solve this in your application code or by using a trigger. I used a sample table (called datetimetest) and added the trigger like this:
Database schema:
CREATE TABLE `datetimetest` (
`id` int(11) NOT NULL,
`created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TRIGGER trigger_SetCreated
BEFORE INSERT ON datetimetest
FOR EACH ROW SET NEW.created = UTC_TIMESTAMP();
You can do the same for the modified field using a trigger BEFORE UPDATE or keep your solution as you now only have one TIMESTAMP column that gets set to CURRENT_TIMESTAMP ON UPDATE.
you can't have multiple timestamp in a table, Still if you want to insert two dates/datetime in your table go for one timestamp and another datetime type.
You can't have multiple columns with CURRENT_TIMESTAMP in one table. If you really need to you can use a 'before insert' trigger to emulate them'.
