MySQL Bugs: #62225: Update with unique column of type TIMESTAMP

Bug #62225	Update with unique column of type TIMESTAMP
Submitted:	22 Aug 2011 9:28	Modified:	22 Aug 2011 10:36
Reporter:	Dirk Schäfer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.1.53	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any

Description:
I created and filles a database and table with MySQL QueryBrowser. The same happens with ODBC. I just reduced the sample to the minimum.

create database test

create table if not exists `test`.`values` (Date TIMESTAMP not null, Value Float, Status int, unique key(`date`))

insert into `test`.`values` (Date, Value, Status) Values( '1999-07-01 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-02 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-03 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-04 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-05 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-06 00:00:00', 100, 1)
insert into `test`.`values` (Date, Value, Status) Values( '1999-07-07 00:00:00', 100, 1)

7 Rows are in the table. No I tried to update Status for the first 4 rows.

update `test`.`values` set Status=0 where (Date >= '1999-07-01 00:00:00' AND Date <= '1999-07-04 00:00:00')

This gives error 1062:
Duplicate entry '2011-08-22 11:15:23' for key 'Date'

The shown time is my local time when I tried the update.
The same with ODBC and C++.

So I tried:
update `test`.`values` set Status='0' where Date = '1999-07-01 00:00:00'

This added the row
2011-08-22 11:26:00, 100, 0
to the table.

Do I made a mistake?

How to repeat:
Just copy create and insert into the QueryBrowser and try.

Forget the ' in set Status='0'. It was just a try.

I tried:

update `test`.`values` set Status=0 where Date = '1999-07-01 00:00:00'

This deletes the line with date 1999-07-01 and adds the line

2011-08-22 11:26:00, 100, 0

So update deletes the line and inserts a new one with the actual local time.

This is how your table is created:

mysql> show create table `values`\G
*************************** 1. row ***************************
       Table: values
Create Table: CREATE TABLE `values` (
  `Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
P,
  `Value` float DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  UNIQUE KEY `Date` (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

Note ON UPDATE CURRENT_TIMESTAMP clause, that is the reason for the attempt to set same current timestamp value for the (UNIQUE) column in several rows for your first case.

Why this clause is added? Because it is a documented feature of TIMESTAMP daa type. Read http://dev.mysql.com/doc/refman/5.1/en/timestamp.html:

"In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

    With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

    With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. 

..."