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: | |
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
[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.