MySQL Bugs: #10492: Timestamp field does not change on a SQL update in INNODB tables

Bug #10492	Timestamp field does not change on a SQL update in INNODB tables
Submitted:	9 May 2005 19:23	Modified:	1 Jun 2005 16:10
Reporter:	Steve Puluka	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Windows (XP and Max OSX)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The behavior of a timestamp field automatically changing on a record hit with an "update" statement does NOT work with innodb format tables. The timestamp field does update on an insert command in innodb tables, but not on the update.

When using Myisam tables to behavior is just as advertised, the timestamp value does change automatically on update of a record.

How to repeat:
I was adding a timestamp to an innodb table.  The field would record the timestamp for a new record, but would not change when the sql update command ran on an existing record.  

I changed to table type to myisam and ran the same web pages and the timestamp field changed with each update.

Suggested fix:
The work around is to manually update the timestamp field during the record update.

I was unable to repeat could you please provide a test case?

miguel@hegel:~/dbs/4.1$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table myt (id int, myt timestamp)engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into myt (id) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from myt;
+------+---------------------+
| id   | myt                 |
+------+---------------------+
|    1 | 2005-05-09 16:42:27 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update myt set id=24 where id=1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from myt;
+------+---------------------+
| id   | myt                 |
+------+---------------------+
|   24 | 2005-05-09 16:43:11 |
+------+---------------------+
1 row in set (0.00 sec)

mysql>

I had this same issue, tried the dev's test and it worked, yet my table didn't.

In short the issue was that I had a default value and not null on the col.

  `LastModified` timestamp NOT NULL default '0000-00-00 00:00:00',

fixed with a manual change to

  `LastModified` timestamp,

MySQL Query Browser FTW! If I had manually typed this in I would have been too lazy to type in more then "`LastModified` timestamp,", nor would it even ever occure to me to set not null or a default on a timestamp. Hope this helps someone else pulling their hair out over something that should always just work. If that is invalid syntax it should puke the CREATE TABLE back at the user with an nive lil error message.

To go into more detail, to fix this do a "SHOW CREATE TABLE tablename" and take its output. Drop the old table... hope ya didn't have data. Now load into write.exe (bad chars in notepad) and modify it, then run it as a query. It will restore the table in a properly working state. You may want to decruft other elements of it before you do. Now timestamps work. 

FYI I think only one timestamp will be autoupdated per table, so if you have two and only one is updated this is not likely you problem. However this is somewhat senseless as you could always just use the first timestamp... don't look at me like that. Someone actually had this problem.