MySQL Bugs: #47453: InnoDB incorrectly changes TIMESTAMP columns when JOINed during an UPDATE

Bug #47453	InnoDB incorrectly changes TIMESTAMP columns when JOINed during an UPDATE
Submitted:	19 Sep 2009 13:46	Modified:	18 Jun 2010 1:28
Reporter:	Peter Waltner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.30, 5.1.45, 5.5.2-m2	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	innodb, join, timestamp, UPDATE

Description:
This may be related to bug #43617 as the fix for bug #43617 works for this bug too.

MySQL incorrectly updates the TIMESTAMP field when updating joined INNODB tables when there is actually no change being performed to the rows. 

In other words, even if you are not changing the value of a non TIMESTAMP column with your update, if the UPDATE is done with joined INNODB tables, MySQL updates the TIMESTAMP column which results in the row being changed and reported as changed the joined INNODB tables.  The reporting of the number of rows is correct, however, MySQL should not update the TIMESTAMP column in the first place.

It looks like when doing an UPDATE on joined InnoDB tables, that the InnoDB engine updates the TIMESTAMP columns regardless of if any other columns have changed. 

It looks like this problem only exists if during an UPDATE if you are using InnoDB  && JOIN && a TIMESTAMP column.  Take away one of these, and the problem disappears.

How to repeat:
/*
 * Since this bug involves a timing issue with TIMESTAMP, we need to do the following statements
 * in groups.  These groups are noted below.
 */

/********************************************************************************************
 * COPY 1 START
 * copy all of the following statements down to the line COPY 1 END into a MySQL command line
 * session (or execute the following statements right after eachother such that they execute
 * quick enough that the internal clock which drives TIMESTAMP(14) does not tick over a whole
 * second)
 * 
 * This first section shows that if you do not JOIN the INNODB tables during the UPDATE, 
 * that the bug does not present itself
 ********************************************************************************************/

CREATE TABLE linkedTable (
        testTableId INT
    ) ENGINE = INNODB;
CREATE TABLE testTable (
        id INT, 
        data CHAR(50), 
        timestamp TIMESTAMP(14)
    ) ENGINE = INNODB;

INSERT INTO linkedTable (testTableId)   VALUES (1);
INSERT INTO testTable   (id,data)       VALUES (1,'original data');
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

# Do an update to testTable directly which does not change anything
UPDATE testTable SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 1 END
 *************/

/*
 * Wait at least 1 second
 */

/********************************************************************************************
 * COPY 2 START
 ********************************************************************************************/

# Do an update to testTable directly which does not change anything
UPDATE testTable SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 2 END
 *************/

/*
 * Notes.
 * 
 * Up to this point, the update seems to be working as advertised.  We are updating a row,
 * but we are updating it with a value which does not change the value of anything in the row.
 *
 * Additionally, when we wait 1 second to allow the internal clock to tick over to the next
 * whole second, we still get the expected behavior of no rows being updated
 */

/********************************************************************************************
 * COPY 3 START
 * 
 * This section demonstrates how the InnoDB engine updates the TIMESTAMP field when
 * doing an update on JOINed tables even when our update is not updating any other field.
 ********************************************************************************************/

# clean up the tables
DELETE FROM linkedTable;
DELETE FROM testTable;

# set up our data elements
INSERT INTO linkedTable (testTableId)   VALUES (1);
INSERT INTO testTable   (id,data)       VALUES (1,'original data');
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

# Do an update to testTable linked to linkedTable which should not change anything
UPDATE linkedTable LEFT JOIN testTable ON testTableId=id SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 3 END
 *************/

/*
 * Wait at least 1 second
 */

/********************************************************************************************
 * COPY 4 START
 ********************************************************************************************/

# Do an update to testTable linked to linkedTable which should not change anything
UPDATE linkedTable LEFT JOIN testTable ON testTableId=id SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 4 END
 *************/

/*
 * Notes.
 * 
 * Notice that if we did not let the clock tick over, that we get the expected behavior.
 *
 * However, after allowing the clock to tick over to the next whole second, when we do the
 * update with a joined table, then even though our update is not updating anything,
 * MySQL updates the TIMESTAMP and then MySQL reports that 1 row has been changed.
 *
 * MySQL is correct in reporting how many rows are being changed since the TIMESTAMP is
 * changing, however, MySQL should not update the TIMESTAMP if nothing is actually changing. 
 */

/********************************************************************************************
 * COPY 5 START
 *
 * Example of how this same problem does not exist when using MyISAM tables
 *
 ********************************************************************************************/

DROP TABLE linkedTable;
DROP TABLE testTable;

CREATE TABLE linkedTable (
        testTableId INT
    ) ENGINE = MyISAM;
CREATE TABLE testTable (
        id INT, 
        data CHAR(50), 
        timestamp TIMESTAMP(14)
    ) ENGINE = MyISAM;

INSERT INTO linkedTable (testTableId)   VALUES (1);
INSERT INTO testTable   (id,data)       VALUES (1,'original data');
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

# Do an update to testTable linked to linkedTable which does not change anything
UPDATE linkedTable LEFT JOIN testTable ON testTableId=id SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 5 END
 *************/

/*
 * Wait at least 1 second
 */

/********************************************************************************************
 * COPY 6 START
 ********************************************************************************************/

# Do an update to testTable linked to linkedTable which does not change anything
UPDATE linkedTable LEFT JOIN testTable ON testTableId=id SET data='original data' WHERE id=1;
SELECT * FROM linkedTable LEFT JOIN testTable ON testTableId=id;

/*************
 * COPY 6 END
 *************/

Suggested fix:
SET engine_condition_pushdown=OFF;

Concise example of Bug #47453

Attachment: BUG_47453_Example_1.sql (application/octet-stream, text), 1.51 KiB.

My apologies.  The fix I suggested above does not work and the suggestion that the bug is related to bug #43617 is now unfounded.  Here are the "fixes" I have been able to figure out.

1) Problem only exists with InnoDB tables.  Use MyISAM tables and the problem goes away. This is not a good fix since if you specified InnoDB tables, you probably need them.

2) If you include "SET timestamp=timestamp", where timestamp is a column with TIMESTAMP(14) or something like that, then MySQL will not change the timestamp field. Therefore, MySQL now reports the correct number of updates on the joined INNODB tables, however, this is not a good fix since your updated fields have an incorrect timestamp since we are forcing the old timestamp to be used after update.

3) There is probably a convoluted SQL statement to get the desired behavior when doing an UPDATED of INNODB tables with a TIMESTAMP column of:
      a) updating the timestamp only when the row is updated
      b) reporting the correct number of updated rows
      c) having the timestamp reflect when the row was changed.
but I have not had the time to work that one out yet.

A workaround by protecting the UPDATE with the addition to the WHERE clause.

Attachment: BUG_47453_Workaround_1.sql (application/octet-stream, text), 1.98 KiB.

The workaround posted in the attached file above gets around the immediate concern by adding an additional constraint to the WHERE clause.  

However, notice that the expected behavior of UPDATE is to match and not change if we are attempting to update a row to an already existing value.  This workaround violates this expected behavior of UPDATE, but may solve the more immediate issue of InnoDB updating TIMESTAMP columns even when the row is otherwise unchanged after the UPDATE.

Results from the workaround posted above. Note that it is important to wait >1s as documented in the workaround example.

Attachment: BUG_47453_Workaround_1_output.txt (text/plain), 4.82 KiB.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/timestamp.html about default value for first TIMESTAMP field in the table:

 In a CREATE TABLE  statement, the first TIMESTAMP column can be declared in any of the following ways:
...
    * With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

another testcase

Attachment: bug47453.sql (application/octet-stream, text), 3.00 KiB.

This bug silently makes Slave out of sync in replication from 5.0 to 5.1 (Because on 5.0 Master TIMESTAMP is not updated).

Also it silently makes Slave out of sync in STATEMENT 5.1 replication if tables use different engines on master and slave (InnoDB->MyISAM or MyISAM->InnoDB)

Even more problems if ON UPDATE triggers exists in examples above.

Please find one more testcase with identical conclusions (test47453.sql):
1. Bug is InnoDB related (not MyISAM)
2. "Changed:" is not zero only if TIMESTAMP column present in table. 
3. Bug has timing issues (in immediate update "Changed:" is reported zero)
4. 2) and 3) above make me think that root cause is related to detection if TIMESTAMP field updated.

Please note that fixing this bug most probably will make problems in replication from older 5.1 versions (so TIMESTAMP will be changed and triggers will be executed on Master, but not on Slave where bug is fixed).

Here is the the smallest testcase, (but I advise review all other testcases because they demonstrate complex conditions for the problem).

CREATE TABLE linkedTable (testTableId INT) ENGINE = INNODB;
CREATE TABLE testTable (id INT, data CHAR(50), timestamp TIMESTAMP(14) ) ENGINE = INNODB;

INSERT INTO linkedTable (testTableId)   VALUES (1);
INSERT INTO testTable   (id,data)       VALUES (1,'original data');

select * from linkedTable;
select sleep(1);

UPDATE linkedTable JOIN testTable ON testTableId=id 
SET data='original data' WHERE id=1;

select * from linkedTable;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105464

3452 Georgi Kodinov	2010-04-13
      Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when JOINed 
      during an UPDATE
      
      Extended the fix for bug 29310 to multi-table update:
      
      When a table is being updated it has two set of fields - fields required for
      checks of conditions and fields to be updated. A storage engine is allowed
      not to retrieve columns marked for update. Due to this fact records can't
      be compared to see whether the data has been changed or not. This makes the
      server always update records independently of data change.
        
      Now when an auto-updatable timestamp field is present and server sees that
      a table handle isn't going to retrieve write-only fields then all of such
      fields are marked as to be read to force the handler to retrieve them.

Please change 'set' in the first sentence to 'sets'.

Otherwise, it looks good to go.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106817

3452 Georgi Kodinov	2010-04-28
      Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when JOINed 
      during an UPDATE
      
      Extended the fix for bug 29310 to multi-table update:
      
      When a table is being updated it has two set of fields - fields required for
      checks of conditions and fields to be updated. A storage engine is allowed
      not to retrieve columns marked for update. Due to this fact records can't
      be compared to see whether the data has been changed or not. This makes the
      server always update records independently of data change.
        
      Now when an auto-updatable timestamp field is present and server sees that
      a table handle isn't going to retrieve write-only fields then all of such
      fields are marked as to be read to force the handler to retrieve them.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106818

3360 Georgi Kodinov	2010-04-28
      Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when JOINed 
      during an UPDATE
      
      Extended the fix for bug 29310 to multi-table update:
      
      When a table is being updated it has two set of fields - fields required for
      checks of conditions and fields to be updated. A storage engine is allowed
      not to retrieve columns marked for update. Due to this fact records can't
      be compared to see whether the data has been changed or not. This makes the
      server always update records independently of data change.
        
      Now when an auto-updatable timestamp field is present and server sees that
      a table handle isn't going to retrieve write-only fields then all of such
      fields are marked as to be read to force the handler to retrieve them.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:joro@sun.com-20100428125554-sympdx16ghfmlh7a) (merge vers: 5.1.47) (pib:16)

Noted in 5.1.47 changelogs.

For updates to InnoDB tables, TIMESTAMP columns could be updated even
when no values actually changed.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)