MySQL Bugs: #59771: TIMESTAMP NOT NULL column erroneously changed to current time on UPDATE

Bug #59771	TIMESTAMP NOT NULL column erroneously changed to current time on UPDATE
Submitted:	27 Jan 2011 5:52	Modified:	31 Jan 2011 19:19
Reporter:	Chris Besant	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.5.8 GA	OS:	Windows (Server 2008 R2)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	current time, timestamp, UPDATE

Description:
If a table is created with a timestamp value either without nullability declaration (defaults to NOT NULL), or explicit NOT NULL, the first timestamp in the table is set to the current time when an UPDATE statement is run updating an entirely different column.

This is definitely a bug as if there are multiple timestamps involved, only the first one is munged.  This does not happen if the timestamp column is declared NULLable.

How to repeat:
Easily demonstrated with the following script:

/*
 * tsbugtest.sql: script for testing out the TIMESTAMP NOT NULL update bug in MySQL
 */
 -- The following is saved in the file version, requiring edit after load.  This script DROPS THE WHOLE CDR DATABASE so BE SURE!!!
-- ARE YOU DAMN SURE???

DROP DATABASE IF EXISTS tsbugtest;
CREATE DATABASE tsbugtest DEFAULT CHARSET ASCII DEFAULT COLLATE ascii_bin;
USE tsbugtest;

/*
 * msc_cdrs table: permanent record of voice and SMS CDRs recorded by the WCS 5060 MSC
 */
CREATE TABLE tstest
(
	StartTime TIMESTAMP NOT NULL,
	EndTime TIMESTAMP NOT NULL,
	some_number MEDIUMINT NOT NULL,
	another_time TIMESTAMP NULL DEFAULT NULL
)
ENGINE=INNODB DEFAULT CHARSET=ASCII;

INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:00:00', '2010-01-01 00:10:00', 1, '2010-01-01 00:05:00');
INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:20:00', '2010-01-01 00:30:00', 1, '2010-01-01 00:25:00');
INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:40:00', '2010-01-01 00:50:00', 1, '2010-01-01 00:35:00');

SELECT * FROM tstest;
UPDATE tstest SET some_number = 2 WHERE some_number = 1;
SELECT * FROM tstest;

DROP TABLE tstest;
CREATE TABLE tstest
(
	StartTime TIMESTAMP NULL,
	EndTime TIMESTAMP NOT NULL,
	some_number MEDIUMINT NOT NULL,
	another_time TIMESTAMP NULL DEFAULT NULL
)
ENGINE=INNODB DEFAULT CHARSET=ASCII;

INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:00:00', '2010-01-01 00:10:00', 1, '2010-01-01 00:05:00');
INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:20:00', '2010-01-01 00:30:00', 1, '2010-01-01 00:25:00');
INSERT INTO tstest (StartTime, EndTime, some_number, another_time) VALUES ('2010-01-01 00:40:00', '2010-01-01 00:50:00', 1, '2010-01-01 00:35:00');

SELECT * FROM tstest;
UPDATE tstest SET some_number = 2 WHERE some_number = 1;
SELECT * FROM tstest;

Suggested fix:
There should be no corruption of a NOT NULL timestamp column when an UPDATE query is run that does not include that column

WHOOPS, getting tired.  I left a couple of comments at the top from the actual database creation script for the database in which I discovered this bug.  I edited that script down to demonstrate with a simplified script.  Please ignore the two lines in the script that start with the '--' comment delimiter.

Work Around:  I decided that to work around this bug I'm adding an UpdateTime column as the first TIMESTAMP type column in my tables with the NOT NULL attribute.  This has the affect of protecting the previous first timestamp column.  It also means no code changes as since the code doesn't have that column on inserts, it gets automatically set to the time of insert.  Finally, any UPDATE queries that don't know about the column automagically get it set appropriately due to this bug :-)

Doesn't this manual page, http://dev.mysql.com/doc/refman/5.5/en/timestamp.html, explain the results you get?

Jeez, Valeriy, do you ever sleep? :-)

After carefully reading that manual page, I guess there are some clues there, but as the manual page does not specify the behaviour on UPDATE, and it is totally different than the way other databases work, and it is very easy to miss the fact that there is certain different behaviour for the FIRST timestamp column, I'd say it's still a bug.

However, it could be moved to the category of documentation.  I really didn't know where to stick it in the first place, and DML may not have been right even if it was truly a bug.

To me, the way MySQL handles TIMESTAMP columns is ridiculous -- they should default to nullable like everything else.  This unique behaviour is retarded in a world where we programmers regularly have to work (and make the same code work) with multiple databases.

My suggestion is that the example script I provided in the bug report (with edits -- I left some garbage in there) be added to the manual page, and there be more emphasis on this special behaviour for the first timestamp declared for a table.  The word "update" doesn't even show up in that manual page.  The mention of the "first column" is made once and far down in the page in a very de-emphasized way.  I'd re-read that dang page twice before when I first discovered that the timestamp columns were weird.

Can you move this bug report to documentation category?  The bug database doesn't let the OP change the header values.

I think it makes sense to add your example with some comments about UPDATE behavior to that manual page.

@Chris 

Maybe using DATETIME and not TIMESTAMP columns would be a better option here? This behavior of the first TIMESTAMP NOT NULL in the table was likely kept that way for compatibility with early (3.x and 4.x) MySQL servers.

The first "TIMESTAMP NOT NULL" becomes "TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp". It was like that since 4.1 or 5.0 (don't remember) and if you execute SHOW CREATE TABLE you will see.

Don't expect it changed because it will break thousands of MySQL applications to change it and a hurricane of protests would appear soon. It should at least be optional then (best implemented by adding one more sql_mode I think).

Peter
(not a MySQL person)

"The word "update" doesn't even show up in that manual page."

I count 72 instances of update or close variants.

It appears to me that the primary concern here is that TIMESTAMP does not behave in MySQL the way it does in other DBMSs. But it simply doesn't, and http://dev.mysql.com/doc/refman/5.5/en/timestamp.html is pretty specific about the ways in which it does behave.

I echo Peter's suggestion that DATETIME might be a better fit for your application.

I still wonder why NOT to add examples with UPDATE to that manual page... to demonstrate at least the behavior without explicit ON UPDATE clauses.

Thanks, Peter, but there are other very specific reasons why I use the timestamp in these uses.  Just sticking another timestamp in up front is fine.  Doesn't hurt to have an auto-update "time of update" column.