MySQL Bugs: #1063: updating one date field updates another date field

Bug #1063	updating one date field updates another date field
Submitted:	16 Aug 2003 1:54	Modified:	17 Aug 2003 1:00
Reporter:	Siva Dirisala	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0-alpha	OS:	Linux (Linux 2.4)
Assigned to:		CPU Architecture:	Any

Description:
Updating one of the date fields is updating another field that is not specified
in the update statement.

How to repeat:
Testcase:

CREATE TABLE TEMP (
CREATION_DATE timestamp NOT NULL,
LAST_UPDATED_BY_ID int(11) NOT NULL default 0,
LAST_UPDATE_DATE timestamp NOT NULL
) type=innodb charset=utf8;

insert into TEMP values(now(),1,now());

update TEMP set LAST_UPDATE_DATE = now(),last_updated_by=1;

Now you would see that the creation_date field is also updated!

That is because your CREATION_DATE uses "timestamp" as column type.

According to http://www.mysql.com/doc/en/Column_types.html the timestamp column type behaves as "A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value.".

You can see timestamp as an auto_increment value that behaves like a datetime (well sort of).

Change the column type of CREATION_DATE into DATE or DATETIME (depending on requested resolution) and you will have solved your problem.

You can leave LAST_UPDATE_DATE as a timestamp due to what I suppose it is designed to show (last time the row was inserted/updated). This means that you dont need to specify Now() in your query for the LAST_UPDATE_DATE column. This will be enough: INSERT INTO TEMP (CREATION_DATE, LAST_UPDATED_BY_ID) VALUES (Now(), 1);

Above is expected behaviour. 
 
according documentation (citation): 
------------------------------------- 
Automatic updating of the first TIMESTAMP column occurs under any of the 
following conditions:  
 
 - The column is not specified explicitly in an INSERT or LOAD DATA INFILE 
statement.  
 - The column is not specified explicitly in an UPDATE statement and some 
other column changes value. (Note that an UPDATE that sets a column to the 
value it already has will not cause the TIMESTAMP column to be updated, 
because if you set a column to its current value, MySQL ignores the update for 
efficiency.)  
 - You explicitly set the TIMESTAMP column to NULL. 
------------------ 
 
We have case 2. 
 
Only first timestamp will be utomatically updated. Changing order of fields 
can help you.

I face this error when I try to make one table with a number field have autoincrement selection and the field type is numeric so when i changed the field type numeric to integer then its working very fine.

thanks 
Izhar Mohiuddin