MySQL two column timestamp default NOW value ERROR 1067

database, default-value, mysql, mysql-error-1067

Solution

You can only have one timestamp column that defaults to `CURRENT_TIMESTAMP` or `NOW()` per table. This is a well known bug in MySQL.

To overcome this, make your default for the created column a valid timestamp value, then insert the timestamp in your CRUD application code. Use `NOW()` or `CURRENT_TIMESTAMP` for your updated column default.

Reference material: http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

To further illustrate MySQL's shortcoming in this area, consider the following code:

CREATE TABLE testing_timestamps (
  id INT NOT NULL AUTO_INCREMENT,
  pk_id INT NOT NULL,
  col1 TIMESTAMP DEFAULT 0,
  col2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(id)
);

delimiter $$
CREATE TRIGGER testing_timestamps_trigger
  AFTER INSERT ON testing_timestamps
  FOR EACH ROW 
  BEGIN
    UPDATE testing_timestamps SET col1 = NOW() WHERE id = MAX(id);
  END;
$$
delimiter ;

INSERT INTO testing_timestamps (id) VALUES (0);

The output from this will display:

mysql> INSERT INTO testing_timestamps (id) VALUES (0);
ERROR 1442 (HY000): Can't update table 'testing_timestamps' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

This is a bummer because using a trigger in this instance would be a good work around.

Problem

I have table as shown below. In order to workaround one default now column restriction of MySQL I used the tip as shown here ``` CREATE TABLE IF NOT EXISTS mytable ( id INT NOT NULL AUTO_INCREMENT , create_date TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' , update_date TIMESTAMP NULL DEFAULT NOW() ON UPDATE NOW() , PRIMARY KEY (`parti_id`) ) ENGINE = InnoDB; ``` My sql_mode does not include `NO_ZERO_DATE` as pointed here my output : ``` mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) ``` It is still giving the error as shown below: ``` ERROR 1067 (42000) at line xx in file: '/myschema.sql': Invalid default value for 'create_date' ``` I use MySQL 5.1.37 on Ubuntu How can I fix it? Thanks.

Original source