Red Gate forums :: View topic - Timestamp fields with ON UPDATE set don't compare properly
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
MySQL Compare
MySQL Compare forum

Timestamp fields with ON UPDATE set don't compare properly

Search in MySQL Compare forum
Post new topic   Reply to topic
Jump to:  
Author Message
JamesC2



Joined: 20 Dec 2011
Posts: 4

PostPosted: Tue Dec 20, 2011 8:09 pm    Post subject: Timestamp fields with ON UPDATE set don't compare properly Reply with quote

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!
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Wed Dec 21, 2011 2:06 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
JamesC2



Joined: 20 Dec 2011
Posts: 4

PostPosted: Thu Dec 22, 2011 1:01 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Thu Dec 22, 2011 2:18 pm    Post subject: Reply with quote

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.

[code]
-- 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;
[/code]

Which I believe is what you would expect?
Back to top
View user's profile Send private message
JamesC2



Joined: 20 Dec 2011
Posts: 4

PostPosted: Thu Dec 22, 2011 3:10 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
JamesC2



Joined: 20 Dec 2011
Posts: 4

PostPosted: Thu Dec 22, 2011 4:35 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group