Mysql updating timestamp

* * 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, * My SQL updates the TIMESTAMP and then My SQL reports that 1 row has been changed.

The fix I suggested above does not work and the suggestion that the bug is related to bug #43617 is now unfounded. This is not a good fix since if you specified Inno DB tables, you probably need them.

Here are the "fixes" I have been able to figure out. 2) If you include "SET timestamp=timestamp", where timestamp is a column with TIMESTAMP(14) or something like that, then My SQL will not change the timestamp field.

Take away one of these, and the problem disappears.

It doesn't get any udpate in t_task table, and I'm not getting any error.* * 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 Inno DB 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 linked Table; DELETE FROM test Table; # set up our data elements INSERT INTO linked Table (test Table Id) VALUES (1); INSERT INTO test Table (id,data) VALUES (1,'original data'); SELECT * FROM linked Table LEFT JOIN test Table ON test Table Id=id; # Do an update to test Table linked to linked Table which should not change anything UPDATE linked Table LEFT JOIN test Table ON test Table Id=id SET data='original data' WHERE id=1; SELECT * FROM linked Table LEFT JOIN test Table ON test Table Id=id; /************* * COPY 3 END *************/ /* * Wait at least 1 second */ /******************************************************************************************** * COPY 4 START ********************************************************************************************/ # Do an update to test Table linked to linked Table which should not change anything UPDATE linked Table LEFT JOIN test Table ON test Table Id=id SET data='original data' WHERE id=1; SELECT * FROM linked Table LEFT JOIN test Table ON test Table Id=id; /************* * COPY 4 END *************/ /* * Notes.

Leave a Reply