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:
Consider two databases:
CREATE DATABASE `dummy`;
CREATE TABLE `TableName1` (`textfield` VARCHAR(255) NULL,`onupdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=MYISAM DEFAULT CHARSET=latin1;
CREATE DATABASE `dummy2`;
CREATE TABLE `TableName1` (`textfield` VARCHAR(255) NULL,`onupdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MYISAM DEFAULT CHARSET=latin1;
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.
ALTER TABLE `dummy2`.`TableName1` CHANGE `onupdate` `oncreationonly` timestamp not null DEFAULT CURRENT_TIMESTAMP;
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:
ALTER TABLE `dummy2`.`TableName1` DROP COLUMN `oncreationonly`, ADD COLUMN `onupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
- 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.
Hope that makes some kind of sense?
PS Otherwise, it's a brilliant product!