Options

Timestamp fields with ON UPDATE set don't compare properly

JamesC2JamesC2 Posts: 5
edited December 22, 2011 10:35AM in MySQL Compare
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`;
USE `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`;
USE `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?

Thanks.

PS Otherwise, it's a brilliant product!

Comments

  • Options
    Hi JamesC2,

    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.

    Neil
  • Options
    Thanks for such a quick reply.

    Sadly, I was on the latest version (1.0.0.292), and am still finding this bug. Let me know if I can provide any more details to help.
  • Options
    Hi JamesC2,

    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.
    -- Script generated by MySQL Compare 1.0.0.292 on 22/12/2011 13:16:26
    
    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';
    
    USE `dummy2`;
    
    ALTER TABLE `tablename1`
      MODIFY COLUMN `onupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
    
    SET FOREIGN_KEY_CHECKS=@ORIGINAL_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@ORIGINAL_UNIQUE_CHECKS;
    SET SQL_MODE=@ORIGINAL_SQL_MODE;
    

    Which I believe is what you would expect?
  • Options
    Sorry, I should have thought of providing that info.

    I'm on 5.0.77, on CentOS 5.5 (and 5.7 on a dev box).

    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!
  • Options
    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.
Sign In or Register to comment.