Red Gate forums :: View topic - Synchronise incorrectly flags NOT NULL defaults
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

Synchronise incorrectly flags NOT NULL defaults

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



Joined: 15 Jul 2010
Posts: 8

PostPosted: Fri Nov 05, 2010 12:49 pm    Post subject: Synchronise incorrectly flags NOT NULL defaults Reply with quote

When sync'ing between two tables which have any columns marked 'NOT NULL DEFAULT <whatever>' the sync process incorrectly marks the default value of the last NOT NULL column as 'No value specified'.

This seems to be irrespective of the column's type and happens even if the change doesn't affect the column.

In these example tables, 'No value specified' will be raised for 'deftext' even though the change does not affect it and a default is specified.

Code:
CREATE TABLE  `db1`.`test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `new_field` varchar(45) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `bool` bit(1) NOT NULL default b'0',
  `deftext` varchar(45) NOT NULL default 'nothing',
  `nullable` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `db2`.`test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `bool` bit(1) NOT NULL default b'0',
  `deftext` varchar(45) NOT NULL default 'nothing',
  `nullable` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


<< EDIT - This sometimes only happens when 'Force Column Order' is set >>
Back to top
View user's profile Send private message
awestrope



Joined: 16 Sep 2011
Posts: 2

PostPosted: Fri Sep 16, 2011 5:07 pm    Post subject: Reply with quote

Did you ever get to the bottom of this as I have the same issue.
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

PostPosted: Mon Sep 19, 2011 1:33 pm    Post subject: Reply with quote

Hi awestrope,

I believe this to be fixed in the latest version. Please download it from http://mysql-compare.com/download .

If you still have the same problem with the latest version please let me know.

Thanks,
Neil
Back to top
View user's profile Send private message
awestrope



Joined: 16 Sep 2011
Posts: 2

PostPosted: Wed Sep 28, 2011 4:18 pm    Post subject: Reply with quote

Still have this issue
Back to top
View user's profile Send private message
neil.anderson



Joined: 28 Sep 2010
Posts: 64

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

Hi awestrope and senorplankton,

I've tried senorplankton's original example both with the force column order option on and off in the latest version of MySQL Compare and the deployment was successful.

Can either of you try it please to confirm it is fixed? You can get the latest via Check for Updates on the Help menu or download it from www.mysql-compare.com

Thanks,
Neil
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Jul 31, 2012 10:04 am    Post subject: Reply with quote

I'm still seeing this problem. I recently bough MySQL Compare using version 1.0.0.301 and appear to be up to date (running Help->Check For Updates says no updates available).

As a quick test, I created two databases - `db1` and `db2`. Now run this:

Code:

CREATE TABLE `db1`.`test` (
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(10) NULL,
   `active` TINYINT NOT NULL DEFAULT '1',
   PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE `db2`.`test` (
   `id` INT(10) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(10) NULL,
   PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


Upon running a comparison of the two databases, it correctly identifies the new `active` column and shows it as:

`active` tinyint(4) NOT NULL DEFAULT 1,

Upon running the deployment I get a "No value specified for a NOT NULL column". Here I have to manually change the dropdown to "Specify custom value" and change the value to 1.

In a simple example like this, that's not too much of a hassle but when you're using this for larger deployments, this can become quite annoying if there are lots of new NOT NULL columns with DEFAULT values specified.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 85
Location: Red Gate Software

PostPosted: Mon Aug 06, 2012 6:29 pm    Post subject: Reply with quote

Hi,

We're going to be looking into this sometime next week, hopefully we'll have a fix you can try out then, I'll post updates here.

Michael
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 85
Location: Red Gate Software

PostPosted: Tue Sep 04, 2012 11:28 am    Post subject: Reply with quote

Sorry for the delay, but the recent release should contain a fix for this issue. Please do let us know!
Back to top
View user's profile Send private message
bstewart



Joined: 31 Jul 2012
Posts: 11

PostPosted: Tue Sep 04, 2012 1:31 pm    Post subject: Re: Reply with quote

Michael Christofides wrote:
Sorry for the delay, but the recent release should contain a fix for this issue. Please do let us know!


I downloaded the new version and this does seem to be fixed now. Thanks.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 85
Location: Red Gate Software

PostPosted: Tue Sep 04, 2012 1:39 pm    Post subject: Re: Reply with quote

bstewart wrote:

I downloaded the new version and this does seem to be fixed now. Thanks.


Fantastic, thanks for the speedy response!
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