What are the challenges you face when working across database platforms? Take the survey
Options

Synchronise incorrectly flags NOT NULL defaults

senorplanktonsenorplankton Posts: 12
edited September 4, 2012 8:39AM in MySQL Compare
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` &#40;
  `id` int&#40;10&#41; unsigned NOT NULL auto_increment,
  `name` varchar&#40;45&#41; NOT NULL,
  `new_field` varchar&#40;45&#41; default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `bool` bit&#40;1&#41; NOT NULL default b'0',
  `deftext` varchar&#40;45&#41; NOT NULL default 'nothing',
  `nullable` varchar&#40;45&#41; default NULL,
  PRIMARY KEY  &#40;`id`&#41;
&#41; ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

<< EDIT - This sometimes only happens when 'Force Column Order' is set >>

Comments

  • Options
    Did you ever get to the bottom of this as I have the same issue.
  • Options
    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
  • Options
    Still have this issue
  • Options
    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
  • Options
    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` &#40;
    	`id` INT&#40;10&#41; NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR&#40;10&#41; NULL,
    	`active` TINYINT NOT NULL DEFAULT '1',
    	PRIMARY KEY &#40;`id`&#41;
    &#41;
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;
    
    CREATE TABLE `db2`.`test` &#40;
    	`id` INT&#40;10&#41; NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR&#40;10&#41; NULL,
    	PRIMARY KEY &#40;`id`&#41;
    &#41;
    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.
  • Options
    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
  • Options
    Sorry for the delay, but the recent release should contain a fix for this issue. Please do let us know!
  • Options
    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.
  • Options
    bstewart wrote:
    I downloaded the new version and this does seem to be fixed now. Thanks.

    Fantastic, thanks for the speedy response!
Sign In or Register to comment.