MySQL is awkward in how it handles timestamps, but I seem to come across an unfortunate (and repeatable) problem. In summary, MySQL COmpare never seems to include an "ON UPDATE" clause in its table definitions, even though for timestamp fields it's frequently needed. I suspect the real issue is that the ON UPDATE attribute doesn't appear in the information_schema database, which I guess is where MySQL compare draws its data - and I suspect it may therefore be tricky to rectify. If that's not clear, let me give an example:

These two tables behave differently - in database 'dummy', the timestamp field (onupdate) is updated every time the text field is changed. In database 'dummy2', the timestamp field (misleading called 'onupdate') holds the timestamp the record was created, but the definition does not include the 'on update' clause, so the field is never updated. (I've included the textfield field, so you can try adding rows and see if necessary).

So the database definitions are different. However, MySQL Compare considers them the same.

That's a bit of a problem.

But it gets worse. Let's make a slight change - change the name of the timestamp field, in dummy2, to more accurately reflect its purpose.

Run MySQL Compare on the resulting databases, and - naturally - it picks up on the different field name. But, whichever way you compare them (whether dummy=>dummy2, or dummy2=>dummy), there's no way to get an ON UPDATE attribute - if I try to make dummy2 the same as dummy, here's the script MySQL Compare produces:

- as a result of this, the column name will be the same, but because there's no ON UPDATE in the definition, it won't work as expected.

And, finally, when comparing databases, there's a danger that timestamp fields which worked fine before, may be inadvertently changed, and have ON UPDATE removed from their definition - which might not be spotted immediately (it's not noticed at all by MySQL Compare), so could mess things up.

Thanks for your detailed bug report. I'm happy to be able to tell you that this has been fixed in the latest version of the MySQL tools. If you Check for Updates from the Help menu you will receive the latest or alternatively you can download it from the www.mysqlcompare.com website.

I'm glad you are enjoying using the tools. Please let us know if you find that this bug is not actually fixed.

We really want to make sure the MySQL tools are of the highest quality so feedback such as this is very important to us. Thanks for your time.

Thanks for getting back to me. Can you please tell me what version of MySQL you are using? On version 5.1.53 with your example Compare picks up the difference in the onupdate field and produces the script below.

SET @ORIGINAL_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @ORIGINAL_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @ORIGINAL_SQL_MODE=@@SQL_MODE, SQL_MODE='ALLOW_INVALID_DATES,NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER';

I'll try to upgrade MySQL, at least on one box, and see if that helps. I'd say it's perfectly reasonable not to support older versions, but given that 5.0.77 is the latest package available in CentOS/RHEL 5 standard repository, there may be plenty others out there stuck in the past like us!

I did investigate upgrading MySQL, but it's quite a big job, so after a couple of attempts in a VM snapshot, decided not to go further at the moment, so can't confirm whether that would fix it - we're probably both assuming it would.