Bug #23719 TIMESTAMP field is updated with DEFAULT value
Submitted: 27 Oct 2006 13:58 Modified: 7 Nov 2006 18:53
Reporter: Gediminas Zukula Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:Ver 14.12 Distrib 5.0.21, for Win32 (ia3 OS:Windows (Windows XP Professional)
Assigned to: CPU Architecture:Any
Tags: DEFAULT, stored procedure, timestamp, UPDATE

[27 Oct 2006 13:58] Gediminas Zukula
Description:
I'm having trouble with TIMESTAMP field which has DEFAULT value set to CURRENT_TIMESTAMP. Remarkably, there isn't "ON UPDATE" statement defined, but when I ran an UPDATE statement without TIMESTAMP field provided (I expect it not to change), it is updated with DEFAULT value.

How to repeat:
I'm experiencing it through calling the UPDATE statement inside Stored Procedure. Unfortunately I couldn't find an easy way to reproduce (in simple case I created, it works the right way).

Suggested fix:
I suppose it has something to do with wrongly interpreted DEFAULT value, as if "ON UPDATE CURRENT_TIMESTAMP" would be in the table field specification. I did a workaround for this problem with inserting "UPDATE... TS_FIELD=TS_FIELD WHERE.." thus timestamp field not (which is not supposed to) being updated.
[27 Oct 2006 14:02] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (namely, http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html) and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
[27 Oct 2006 14:35] Gediminas Zukula
I believe this the case (from http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html) that is violated:

"With a DEFAULT CURRENT_TIMESTAMP  clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated."

There is _no_ "ON UPDATE" (only DEFAULT) clause in the buggy situation, but the field is automatically updated anyway.
[30 Oct 2006 7:55] Valeriy Kravchuk
OK, I misunderstood you report. Please, try to repeat with a newer version, 5.0.27, and, in case of the same result, please, send you exact CREATE TABLE statement and code of that SP that has problematic UPDATE in it.
[30 Oct 2006 9:25] Gediminas Zukula
Found exactly what is the problem.

Say, we create a table with one TIMESTAMP column.

CREATE TABLE `bug` (
  `DATE` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(`DATE`)
)
ENGINE = InnoDB;

Then if you try to add 1 more column via QueryBrowser, it generates this kind of statement:
ALTER TABLE `bug` MODIFY COLUMN `DATE` TIMESTAMP,
 ADD COLUMN `VALUE2` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `DATE`;

Notice this part "MODIFY COLUMN `DATE` TIMESTAMP". Actually you didn't edit the existing column at all but the statement is generated in this way.
And this statement part causes the "ON UPDATE CURRENT_TIMESTAMP" to appear now in the table description.

So basically it is a QueryBrowser bug. 

By the way QueryBrowser wrongly creates the initial table creation statement putting 'CURRENT_TIMESTAMP' into quotes.
[1 Nov 2006 11:56] Valeriy Kravchuk
In my case (5.0.26 + QB 1.2.4) it created the following statement:

ALTER TABLE `test`.`bug` ADD COLUMN `d2` VARCHAR(45) NOT NULL AFTER `d`;

So, no MODIFY clause. Please, check again with latest versions.
[6 Nov 2006 15:03] Gediminas Zukula
I've retested with 5.0.26 - now it works ok. We can close this issue.
[7 Nov 2006 18:53] MySQL Verification Team
Thank you for the feedback.