MySQL two column timestamp default NOW value ERROR 1067

Question

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.

Answer 1

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.

Answer 2

Actually, Randy's code is broken. It won't work because it is a mutating trigger. You can't update the table that initiated the updating. For example, if you are updating the table SESSIONS, you can't the go modify the table in your trigger using an UPDATE, INSERT or DELETE statement. The only way you are allowed to modify the table to which the trigger is attached is to use the "NEW" or "OLD" prefixes as demonstrated below. (Of course, you are allowed to update other tables at will.) Here is an example of how to overcome the problem described by the op.

create table sessions (
    id integer not null,
    created timestamp not null default 0,
    updated timestamp not null default 0
);

delimiter //
create trigger bifer_sessions_ts before insert on sessions for each row
begin
    set new.created = now();
    set new.updated = now();
end;
//
create trigger bufer_sessions_ts before update on sessions for each row
begin
    set new.updated = now();
end;
//
delimiter ;

Answer 3

To do this and have it work:

col1 TIMESTAMP DEFAULT 0,

col2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

You don't need a trigger for col1, just ensure the value is NULL in your query. The answer is in the certification guide.