MySQL Bugs: #70548: timestamp column being set to current time

Bug #70548	timestamp column being set to current time
Submitted:	7 Oct 2013 22:37	Modified:	8 Oct 2013 18:06
Reporter:	Don Cohen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.9-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
probably related to:
 alter table samplecollection change column processtime processtime timestamp;
which (to me inexplicably) results in 
 `collectiontime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

initially: CREATE TABLE `samplecollection` (
  `sampleid` varchar(16) DEFAULT NULL,
  `monkeyid` varchar(4) DEFAULT NULL,
  `collectiontime` text,
  `samplecondition` text,
  `consistency` text,
  `contentcode` text,
  `fruitcode` text,
  `specialcontentcode` text,
  `processing` text,
  `thermos` text,
  `groupname` text,
  `collector` text,
  `collectorcomment` text,
  `closetlabnotes` text,
  `processor` text,
  `processtime` text,
  `remaining` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 LOAD DATA LOCAL INFILE ...

then the alter table above.

now watch this:
mysql> select substring(collectiontime,1,4) y,count(*) from samplecollection group by y;
+------+----------+
| y    | count(*) |
+------+----------+
| 2006 |       38 |
| 2007 |       77 |
| 2008 |      167 |
| 2009 |      206 |
| 2010 |      175 |
| 2011 |      504 |
| 2012 |      155 |
| 2013 |     5414 |
+------+----------+
8 rows in set (0.07 sec)

mysql> update samplecollection set processtime = concat(substring(processtime,6,4), substring(processtime,2,4),substring(processtime,1,1)) where length(processtime)=9 ;
Query OK, 1382 rows affected (0.16 sec)
Rows matched: 1382  Changed: 1382  Warnings: 0

mysql> select substring(collectiontime,1,4) y,count(*) from samplecollection group by y;
+------+----------+
| y    | count(*) |
+------+----------+
| 2013 |     6736 |
+------+----------+
1 row in set (0.07 sec)

Notice the update was for a different column, processtime, and should not have had any effect on the collection time!  

How to repeat:
It's repeatable for me.
It stopped happening when I changed the column from timestamp to datetime.
If it's supposed to work this way please tell me why.

I suppose this testcase demonstrates the same thing?

----
drop temporary table if exists t1;
create temporary table t1(a int, b timestamp)engine=innodb;
insert into t1(a) values (1);
select * from t1;
select sleep(2);
update t1 set a=2;
select * from t1;
----

http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

Please see Shane's comments. Thanks.

yes, it does:

mysql> create temporary table t1(a int, b timestamp)engine=innodb;
insert into t1(a) values (1);
select * from t1;
Query OK, 0 rows affected (0.39 sec)
mysql> Query OK, 1 row affected (0.05 sec)
mysql> +------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2013-10-08 09:44:29 |
+------+---------------------+
1 row in set (0.02 sec)

mysql> update t1 set a=2;
select * from t1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> +------+---------------------+
| a    | b                   |
+------+---------------------+
|    2 | 2013-10-08 09:45:17 |
+------+---------------------+
1 row in set (0.03 sec)

mysql>

Ok, the url explains why it's auto updating given that the table definition says
 `collectiontime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Now I want to know why that is the result of doing this:
 alter table samplecollection change column processtime processtime timestamp;

It seems like a very bad default.  I don't yet even see how to override it.
I hope that's not what I get when I do
  create table ... ,col1 timestamp, ...

ok, I now see that is exactly what I get -- in other words don't use timestamp
as the type unless you want that behavior...  (which I never have wanted before)
Now I get to look through all my earlier code for timestamp columns.

Thank you for the feedback.

Closed as "Not a Bug", because this is well-defined, described behavior.