timestamp is not updated on foreign key update

Question

I am using MYSQL 5.1.38 and I have following tables:

create table table1 (  
col1 varchar(50) primary key not null,  
ts1 timestamp not null default current_timestamp on update current_timestamp  
)engine=innodb;  

create table table2 (
col1 varchar(50) not null,
ts2 timestamp not null default current_timestamp on update current_timestamp,  
foreign key (col1) references table1 (col1) on update cascade on delete cascade  
)engine=innodb;

When I update col1 in table1, the ts1 in table1 and col1 in table2 are updated but ts2 in table2 does not get updated.

Here is the output:

mysql>   insert into table1 (col1) values ('test'); 
Query OK, 1 row affected (0.00 sec)

mysql>   insert into table2 (col1) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table1;
+------+---------------------+
| col1 | ts1                 |
+------+---------------------+
| test | 2013-05-17 09:37:56 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> select * from table2;
+------+---------------------+
| col1 | ts2                 |
+------+---------------------+
| test | 2013-05-17 09:38:03 |
+------+---------------------+
1 row in set (0.01 sec)

mysql> update table1 set col1='test1' where col1 = 'test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from table1;
+-------+---------------------+
| col1  | ts1                 |
+-------+---------------------+
| test1 | 2013-05-17 09:44:28 |
+-------+---------------------+
1 row in set (0.00 sec)

mysql> select * from table2;
+-------+---------------------+
| col1  | ts2                 |
+-------+---------------------+
| test1 | 2013-05-17 09:38:03 |
+-------+---------------------+
1 row in set (0.00 sec)

I would expect ts2 to be updated as well. Is this expected behaviour?

TheLevti TheLevti · Accepted Answer · 2015-02-28T15:48:04

This is a serious problem of MySQL.

The same applies if you try to use an on update or an on delete trigger. As expected, those should trigger even on an foreign key update or delete, but they do not.

There is no simple work around for this.

This is a serious ACID compliance failure first reported almost 10 years ago and still not fixed. Read this bug report.

timestamp is not updated on foreign key update

2 Answers