MySQL Bugs: #76009: Pubtime is set as current if other columns are modified

Bug #76009	Pubtime is set as current if other columns are modified
Submitted:	23 Feb 2015 16:00	Modified:	8 Apr 2015 15:21
Reporter:	Wei Huang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.5	OS:	Windows
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any

Description:
Suppose there is a table of news items for a web site, and the administrator needs to update the title (column 'title') without modifying the publicated time (column 'pubtime'). If one update command is used in the C# source code, the publicated time will be reset to current time. If the updated command is repeated, the publicated time remains unchanged (in fact the second update execution set the column pubtime to its original value).

How to repeat:
Here is the table struct:
CREATE TABLE `news` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `author` varchar(100) NOT NULL,
  `board` varchar(255) NOT NULL,
  `b_urgt` bit(1) NOT NULL,
  `b_impt` bit(1) NOT NULL,
  `attname` varchar(30) DEFAULT NULL,
  `attsize` int(10) NOT NULL,
  `general` varchar(255) DEFAULT NULL,
  `title` varchar(120) DEFAULT NULL,
  `content` text,
  `pubtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `enabled` bit(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `PK_news_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=380 DEFAULT CHARSET=utf8

Suppose there is some data:
Id   Title          PubTime               ...
===  ===========    ==========            ...
1    Test           2015-02-03 23:30:01   ...

If update command is execute only once with Column 'Title' is changed:
string sql = "select * from " + tableName + " where id=" + item.Id;
this.dbConn.Open(sql);
row = this.dbConn.DataRowsInFirstTable[0];
row["Title"]="test2";
this.dbConn.UpdateDS();   // once

Then the updated table is as follows:
Id   Title          PubTime               ...
===  ===========    ==========            ...
1    Test2          2015-02-03 23:53:36   ...

If update command is execute twice and in the second round the PubTime is reset to the original file leaving other columns unchanged:
string sql = "select * from " + tableName + " where id=" + item.Id;
this.dbConn.Open(sql);
row = this.dbConn.DataRowsInFirstTable[0];
row["Title"]="test2";
this.dbConn.UpdateDS();
this.dbConn.Open(sql);    // the first time
row2 = this.dbConn.DataRowsInFirstTable[0];
row2["PubTime"]=row["PubTime"];  // reset to original value
this.dbConn.UpdateDS();   // the second time

Then the updated table is as follows:
Id   Title          PubTime               ...
===  ===========    ==========            ...
1    Test2          2015-02-03 23:30:01   ...

Here is the update command:
public void UpdateDS()
{
    MySqlDataAdapter Sda = new MySqlDataAdapter(Sql, SqlConn);
	MySqlCommandBuilder sqlCB = new MySqlCommandBuilder(Sda);
	MySqlCommand sqlc = sqlCB.GetUpdateCommand();
	Sda.UpdateCommand = sqlc;
    try
    {
        Sda.Update(ds.Tables[0]);
    }
    catch (Exception)
    {
    }
    Sda.Dispose();
}

Suggested fix:
Maybe in the update command execution, if other columns is changed, columns of the type of timestamp will be update to current time. Otherwise, if no other columns is changed, columns of the type of timestamp will be update to the new time.
Or the unchanged timestamp will be set to current time. Please check.

Hi Wei,

This behavior is defined at the server level. You can check the documentation about how this automatic initialization is handled here:

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

Please check it out and let us know if there is any problem with the automatic initialization.

Looking forward to your answer.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm sorry that I didn't recieve the email alert for this bug. So I reply to you so late.
I've read your feedback and the web page URL you provided. However, I don't mean the "Automatic Initialization and Updating for TIMESTAMP and DATETIME" problem. I think it is a problem about default value of a column, a NULL TIMESTAMP problem. But in my case, there is updating the row and try not to affect the TIMESTAMP column.
If I want to update only the title column without affecting the pubtime column, I can't use only one update function. Is it somewhat strange? And there is no similar problem in JDBC.

hi Wei,

The pubtime is defined in your table like an auto-updated column. So the behavior is that the value of this column is going to be updated automatically every time  the value of any other column in the row is changed from its current value.

If you want to prevent an auto-updated column from updating when other columns change, explicitly set it to its current value.

As I understood that is what you would have to do if you do not want that the value changes in the same update operation when you are updating the Title column.

Looking forward to your answer.

Yeap, you're right. I used SQL Server before, hence it takes me some time to notic this problem (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP). Thank you.