MySQL Bugs: #27127: incorrect behaviour of timestamp column with DEFAULT CURRENT

Bug #27127	incorrect behaviour of timestamp column with DEFAULT CURRENT_TIMESTAMP
Submitted:	14 Mar 2007 11:22	Modified:	10 Apr 2007 6:07
Reporter:	Robert Grellmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1.16	OS:	Any
Assigned to:	justin he	CPU Architecture:	Any
Tags:	current_timestamp, ON UPDATE, timestamp

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.

mysqltest test case

Attachment: bug27127.tgz (application/x-gtar, text), 1012 bytes.

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)

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.

Pushed into 5.1.18-beta

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.