MySQL Bugs: #34850: UPDATE query with SET date_updated=NOW() intermittently setting other DATETIMEs

Bug #34850	UPDATE query with SET date_updated=NOW() intermittently setting other DATETIMEs
Submitted:	26 Feb 2008 16:58	Modified:	31 May 2008 14:05
Reporter:	Kev Green	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.32-Debian_7etch3-log	OS:	Linux (Debian 4.0 Etch.)
Assigned to:		CPU Architecture:	Any

Description:
Similar to this bug:

http://bugs.mysql.com/bug.php?id=4977

HOWEVER there are NO TIMESTAMP fields in the data structure, the troublesome fields are both DATETIME.

Query is (Censored due to confidential info in it)...

UPDATE mytable SET [...stuff...],date_updated=now() WHERE reference='XYZ'

The occasionally rogue fields are:

date_created  	datetime NOT NULL DEFAULT "0000-00-00 00:00:00" COMMENT "Date this contact was created"

date_updated 	datetime NOT NULL DEFAULT "0000-00-00 00:00:00" COMMENT "Date this contact was last updated"

We are finding that >75% of the time, the date_created field is being changed as well as the date_updated field, in an update query which makes no reference to the date_created field.

Neither field has any special flags attached to it.

The SQL is generated in PHP code, but dumping out the raw SQL query that is being executed before it is run shows clearly that there are no extraneous bits creeping into the SQL that would cause this, therefore I am filing this as a MySQL bug, not a PHP one.

K.

How to repeat:
Happens frequently, but intermittently running the above SQL query on the data structure in question.

Thank you for the report.

Please provide output of SHOW CREATE TABLE mytable and UPDATE query. You could do it in hidden comment.

I've just tried to find a record (row from the table) in the system in question to get an SQL query for you that causes the issue to show up, but a couple of tries did not yield one.

I will send an example query through as soon as I am able to replicate it again.

K.

Kev,

I'll set this bug to "need feedback" because we are waiting of your tests. Please set it back too open when you have more informations.

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".

Hmmm... forget references to matrix_mailshot_details.

I've checked it through thoroughly, and that's just bad data structure, it should have had a 'DEFAULT CURRENT_TIMESTAMP' and not an 'ON UPDATE CURRENT TIMESTAMP'. I will look into the other one in detail as well.

Kev,

does this mean, we can close this bug report?

Hi,

That means that the second instance where I thought the bug had re-surfaced was a mis-report.

The first instance still stands, although none of our staff have reported an instance of it yet, so I haven't been able to forward it on.

However I have sufficient logging in place to catch the offending query should someone sent me a report, and I'll send it straight on to you.

I would leave this in 'waiting correspondance' state again until then...

K.

Thank you for the feedback.

I can not repeat described behavior with test data.

If I understood correctly you have only problems with table originally created by 3.23 server and not every time you run UPDATE query. Have you run mysql_upgrade on this table? Is problem repeatable if dump/reload this table using mysqldump? Could you also please try current version 5.0.51a and provide real UPDATE query you meet problem with?

Yes, that's correct. A table originally created with MySQL 3.23.x. I will try as you suggest, just in case I haven't already.

Thank you for the update.

We will wait feedback from you.

Thank you for the report.

> I have dumped and reloaded the table, and the problem seems to have gone away.

As you upgrade from version 3.23 to 5.0 and dumping/reloading tables is recommended way of upgrade from major to major version I close this report as "Can't repeat"

According to mysql_upgrade problem please test with current version 5.0.51b and if problem still exists open new bug report.