Bug #27127 incorrect behaviour of timestamp column with DEFAULT CURRENT_TIMESTAMP
Submitted: 14 Mar 2007 12:22 Modified: 10 Apr 2007 8:07
Reporter: Robert Grellmann
Status: Closed
Category:Server: Cluster Severity:S2 (Serious)
Version:5.1.16 OS:Any
Assigned to: Bugs System Target Version:
Tags: current_timestamp, timestamp, ON UPDATE

[14 Mar 2007 12:22] Robert Grellmann 
Description:
In ndbcluster tables with a timestamp column which has DEFAULT CURRENT_TIMESTAMP, the
value in that column changes when you update another column in a row.
It changes to a random value, sometimes 0000-00-00... sometimes 1970-01-04..., sometimes
2034-...
The local time on all nodes is set correctly.

I think it only happens when you have a primary key/auto_increment column, but i am not
sure.

see details below.

How to repeat:
CREATE TABLE `test` (
`row1` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`row2` INT NOT NULL,
`row3` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = ndbcluster ;

insert into test (row2) values (1);

-- now the values are correct: 1, 1, 2007-03-14 12:03:15

update test set row2 = 2 where row1 = 1;

-- now the value for row3 is incorrect.
[16 Mar 2007 12:48] Hartmut Holzgraefe 
mysqltest test case

Attachment: bug27127.tgz (application/x-gtar, text), 1012 bytes.
[20 Mar 2007 10:17] Antonio Sindona 
As a workaround You could change the default for the field. In this case the side effect
doesn't happens. See below for an example.
The workaround has been tested on 5.0.27 ndbcluster, on a ProLiant DL380 with RHEL Red
Hat Enterprise Linux ES release 4 (Nahant Update 4) Kernel 2.6.9-42.ELsmp

mysql> desc test_results;
+------------+-------------+------+-----+---------------------+-------+
| Field      | Type        | Null | Key | Default             | Extra |
+------------+-------------+------+-----+---------------------+-------+
| test_name  | varchar(10) | YES  | MUL | NULL                |       |
| start_test | timestamp   | NO   |     | CURRENT_TIMESTAMP   |       |
| stop_test  | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| duration   | float       | YES  |     | NULL                |       |
+------------+-------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec) 

mysql> insert into test_results (test_name, start_test) values ('test',sysdate());
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_results;
+-----------+---------------------+---------------------+----------+
| test_name | start_test          | stop_test           | duration |
+-----------+---------------------+---------------------+----------+
| t3        | 2007-03-16 01:52:17 | 2007-03-16 01:51:20 |        0 |
| t12       | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t5        | 2007-03-16 01:52:17 | 2007-03-16 01:52:09 |        0 |
| t2        | 2007-03-16 01:52:17 | 2007-03-16 01:51:20 |        0 |
| t11       | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| test      | 2007-03-20 09:41:57 | 0000-00-00 00:00:00 |     NULL |
| t8        | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t10       | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t1        | 2007-03-16 01:52:17 | 2007-03-16 01:51:04 |        0 |
| t7        | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t4        | 2007-03-16 01:52:17 | 2007-03-16 01:51:35 |        0 |
| t6        | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t9        | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
| t13       | 2007-03-16 01:52:17 | 2007-03-16 01:52:17 |        0 |
+-----------+---------------------+---------------------+----------+
14 rows in set (0.04 sec)

mysql> update test_results set stop_test=sysdate();
Query OK, 14 rows affected (0.04 sec)
Rows matched: 14  Changed: 14  Warnings: 0

mysql> select * from test_results;
+-----------+---------------------+---------------------+----------+
| test_name | start_test          | stop_test           | duration |
+-----------+---------------------+---------------------+----------+
| t3        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t12       | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t5        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t2        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t11       | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| test      | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |     NULL |
| t8        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t10       | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t1        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t7        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t4        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t6        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t9        | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
| t13       | 2007-03-20 09:42:38 | 2007-03-20 09:42:38 |        0 |
+-----------+---------------------+---------------------+----------+
14 rows in set (0.04 sec)

mysql> alter table test_results modify start_test timestamp default '0000-00-00
00:00:00';
Query OK, 14 rows affected (1.46 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> desc test_results;
+------------+-------------+------+-----+---------------------+-------+
| Field      | Type        | Null | Key | Default             | Extra |
+------------+-------------+------+-----+---------------------+-------+
| test_name  | varchar(10) | YES  | MUL | NULL                |       |
| start_test | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| stop_test  | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| duration   | float       | YES  |     | NULL                |       |
+------------+-------------+------+-----+---------------------+-------+
4 rows in set (0.01 sec)

mysql> update test_results set stop_test=sysdate();
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14  Changed: 14  Warnings: 0

mysql> select * from test_results;
+-----------+---------------------+---------------------+----------+
| test_name | start_test          | stop_test           | duration |
+-----------+---------------------+---------------------+----------+
| t3        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t6        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t10       | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t4        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t7        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t2        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t12       | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| test      | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |     NULL |
| t5        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t11       | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t8        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t13       | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t1        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
| t9        | 2007-03-20 09:42:38 | 2007-03-20 09:50:32 |        0 |
+-----------+---------------------+---------------------+----------+
14 rows in set (0.00 sec)
[22 Mar 2007 5:23] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22553

ChangeSet@1.2486, 2007-03-22 12:16:52+08:00, Justin.He@dev3-240.dev.cn.tlan +1 -0
  Bug#27127, Incorrect behaviour of timestamp column with DEFAULT CURRENT_TIMESTAMP
  correct the bitmap_set_bit when a field is timestamp and described 
  with default CURRENT_TIMESTAMP or on update CURRENT_TIMESTAMP,
  then it will reduce a little time cost when the field doesnot need 
  to write.
[7 Apr 2007 9:01] Bugs System 
Pushed into 5.1.18-beta
[10 Apr 2007 8:07] Jon Stephens 
Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.18 changelog.