Description:
When using TIMESTAMP columns with NDB tables, updating columns that are not part of the primary key does not result in updating the TIMESTAMP column when it should.
How to repeat:
Given this table definition:
CREATE TABLE `testtable` (
`number` int(10) NOT NULL default '0',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`letter` char(1) default NULL,
PRIMARY KEY (`number`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
Then, inserting a row yields the expected result:
mysql> insert into testtable (number) values (1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from testtable;
+--------+---------------------+--------+
| number | modified | letter |
+--------+---------------------+--------+
| 1 | 2005-03-28 12:32:55 | NULL |
+--------+---------------------+--------+
1 row in set (0.05 sec)
However, updating that row does not update the `modified` column as it should.
mysql> update testtable set letter = 'A' where number = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from testtable;
+--------+---------------------+--------+
| number | modified | letter |
+--------+---------------------+--------+
| 1 | 2005-03-28 12:32:55 | A |
+--------+---------------------+--------+
1 row in set (0.05 sec)
If one were to use the same table definition with MYISAM tables, it works correctly.
However, as an aside, if one were to update the primary key, the `modified` column would update as in the following example:
mysql> update testtable set number = 2 where number = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from testtable;
+--------+---------------------+--------+
| number | modified | letter |
+--------+---------------------+--------+
| 2 | 2005-03-28 12:36:22 | A |
+--------+---------------------+--------+
1 row in set (0.00 sec)
Suggested fix:
TIMESTAMP columns should always update regardless of the updated column's status as a key.
