Synchronise incorrectly flags NOT NULL defaults

senorplankton

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.

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 >>

awestrope

Did you ever get to the bottom of this as I have the same issue.

neil.anderson

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

awestrope

Still have this issue

neil.anderson

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

bstewart

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:

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.

Michael Christofides

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

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

bstewart

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.

Michael Christofides

bstewart wrote:

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

Fantastic, thanks for the speedy response!