2

I have a table in my database that contains three fields:

id  |  start_time  |  modification_time

When I look at it in phpmyadmin, I see that:

  • id is primary key, auto increment, int
  • start_time has type timestamp, default value is NULL
  • modification_time is also timestamp, has attribute on update Current_timestamp and the default value is current_timestamp.

When I do simple insert query in which I provide only start_time (because as I assume two more values are filled automatically), I have the value 0000-00-00 00:00:00 there. I think that's because there's null value as default for that field. However, I cannot change it, because when I try, I get the error:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

How can I fix that then?

The result of the query: show create table table_name is as follows:

CREATE TABLE `Table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start_time` TIMESTAMP NULL DEFAULT NULL,
    `modification_time` TIMESTAMP NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
hjpotter92
  • 78,589
  • 36
  • 144
  • 183
randomuser2
  • 107
  • 10
  • 1
    please post `show create table table_name` results here – Ravinder Reddy Apr 12 '15 at 12:11
  • 1
    you have answers @ http://stackoverflow.com/questions/4489548/why-there-can-be-only-one-timestamp-column-with-current-timestamp-in-default-cla and http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html – Ravinder Reddy Apr 12 '15 at 12:15
  • 1
    The error message is self-explanatory... – Aleksander Wons Apr 12 '15 at 12:21
  • I added the result of the query shown by Ravinder... I've read also the suggested links, but I'm still confused about that - could you give me some hint how could I even start fixing that problem? – randomuser2 Apr 12 '15 at 13:19

0 Answers0