Error in setting the default value to CURRENT_TIMESTAMP

Question

I am having problem while altering a table. I need a column with data type DATETIME to take default value as current date/time and on update also it should automatically update it's value to current date/time. I am writing the following SQL

ALTER TABLE `groups` 
CHANGE COLUMN `modified` `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

I am getting the following error message.

Error Code: 1067. Invalid default value for 'modified'

The MySQL version I'm using is 5.5.49 on a Ubuntu 14.04.1 system.

Please let me know how this can be fixed.

MySQL does not support dynamic default values (save for a tiny exception in `TIMESTAMP` columns). — Álvaro González, Nov 23 '16 at 10:20
Possible duplicate of http://stackoverflow.com/questions/9005766/invalid-default-value-for-dateadded — Jan, Nov 23 '16 at 10:22

score 1 · Answer 1 · edited May 23 '17 at 12:18

1

Most probably this is because you already have another column with CURRENT_TIMESTAMP as default.

In MySQL versions prior to 5.6 this is a problem:

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?

edited May 23 '17 at 12:18

Community

1
1

answered Nov 23 '16 at 10:13

Matijs

2,533
20
24

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

Prior to MySQL 5.6.5, you can only use the CURRENT_TIMESTAMP default value for columns of type TIMESTAMP. See https://stackoverflow.com/a/9005872/1293303

edited May 23 '17 at 10:30

Community

1
1

answered Nov 23 '16 at 10:19

Jan

1,040
14
29

Error in setting the default value to CURRENT_TIMESTAMP

2 Answers2