Synchronise incorrectly flags NOT NULL defaults

Compares and synchronizes MySQL database schemas.

Moderators: Chris Auckland, eddie davis, Michael Christofides

Synchronise incorrectly flags NOT NULL defaults

Postby senorplankton » Fri Nov 05, 2010 12:49 pm

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: Select all
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 >>
senorplankton
 
Posts: 8
Joined: Thu Jul 15, 2010 6:11 pm

Postby awestrope » Fri Sep 16, 2011 5:07 pm

Did you ever get to the bottom of this as I have the same issue.
awestrope
 
Posts: 2
Joined: Fri Sep 16, 2011 5:05 pm

Postby neil.anderson » Mon Sep 19, 2011 1:33 pm

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
neil.anderson
 
Posts: 64
Joined: Tue Sep 28, 2010 2:17 pm

Postby awestrope » Wed Sep 28, 2011 4:18 pm

Still have this issue
awestrope
 
Posts: 2
Joined: Fri Sep 16, 2011 5:05 pm

Postby neil.anderson » Wed Dec 21, 2011 2:15 pm

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
neil.anderson
 
Posts: 64
Joined: Tue Sep 28, 2010 2:17 pm

Postby bstewart » Tue Jul 31, 2012 10:04 am

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: Select all
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.
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Postby Michael Christofides » Mon Aug 06, 2012 6:29 pm

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
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Postby Michael Christofides » Tue Sep 04, 2012 11:28 am

Sorry for the delay, but the recent release should contain a fix for this issue. Please do let us know!
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software

Re:

Postby bstewart » Tue Sep 04, 2012 1:31 pm

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.
bstewart
 
Posts: 11
Joined: Tue Jul 31, 2012 9:40 am

Re:

Postby Michael Christofides » Tue Sep 04, 2012 1:39 pm

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


Fantastic, thanks for the speedy response!
Michael Christofides
 
Posts: 94
Joined: Wed Apr 20, 2011 6:37 pm
Location: Red Gate Software


Return to MySQL Compare

Who is online

Users browsing this forum: No registered users and 0 guests