Deployment Wizard doesn't work for my schema

skolbunov

I keep getting "MySQL Data Compare - Aborting" message for all my attempts to deploy changes either using MySQL Data Compare or by creating a deployment script.

The error message is:

Bug report generated at 7/23/2013 2:38:35 PM
Program version 1.0.0.473
System.NullReferenceException: Object reference not set to an instance of an object.
at #xjmb.#7JJ.#aA2(#9x2 x, #9x2 y)
at #Py2.#Jzr.#urb[#NrPb](IEnumerable`1 items, #Oy2 comparer)
at #xjmb.#wjmb.#um()
at #5jmb.#4jmb.#t.#n2Y.#s2Y()
at RedGate.Shared.Controls.ProgressDialogEx.e()

Deployment seems to work for some simple schemas, but does not work for more complex one.

Michael Christofides

Hi, thank you for your message and sorry to hear you're hitting issues.

It would be really helpful if you could:

1. Turn on verbose logging (instructions below)
2. Recreate the problem
3. Send in the log files (mysql@red-gate.com)

Verbose logging instructions: (the example listed is SQL Data Compare, but it's the same process in Data Compare for Oracle)
http://www.red-gate.com/supportcenter/c ... _log_files

Best regards,
Michael

skolbunov

Finally I've found some time to investigate the problem. I knew it is something in my schema that preventing deploying.
I found out that this is inter-database foreign keys which I use extensively. Seems that these foreign keys are treated by the tool the same way as broken foreign keys to non-existing table, while a foreign key to a table in other database is a valid key.
Specifically, only such foreign keys at the target database are the problem, source database may have them.
What complicates the situation is that the MySQL Data Compare does not give a relevant response of what causes the abort message. I've sent my detailed logs to the provided email and bug report was created at your side, but it was not resolved.
Now I'm attaching the script to recreate the problem. It will create four databases: db_source, db_ref_source,db_target, and db_ref_target. table_1 in db_source has foreign key to table_ref in db_ref_source and table_1 in db_target has foreign key to table_ref in db_ref_target. Now try to deploy changes from db_source to db_target and get "Aborting" message right away.
Dropping inter-database foreign key at db_target solves the problem. I may use that as walk-around for now, but it would be much better to don't have this restriction. Also, MySQL Data Compare should give better explanation of why deployment is aborted.

CREATE DATABASE IF NOT EXISTS `db_ref_source`;
USE `db_ref_source`;
DROP TABLE IF EXISTS `table_ref`;
CREATE TABLE `table_ref` (
  `idtable_ref` int(11) NOT NULL,
  `table_refcol` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtable_ref`),
  UNIQUE KEY `table_refcol_UNIQUE` (`table_refcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table_ref` VALUES (11,'eleven'),(12,'twelve');

CREATE DATABASE IF NOT EXISTS `db_source`;
USE `db_source`;
DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
  `idtable_1` int(11) NOT NULL,
  `idtable_ref` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtable_1`),
  KEY `table_ref_fk_idx` (`idtable_ref`),
  CONSTRAINT `table_ref_fk` FOREIGN KEY (`idtable_ref`) REFERENCES `db_ref_source`.`table_ref` (`idtable_ref`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table_1` VALUES (1,11),(3,11),(2,12);


CREATE DATABASE IF NOT EXISTS `db_ref_target`;
USE `db_ref_target`;
DROP TABLE IF EXISTS `table_ref`;
CREATE TABLE `table_ref` (
  `idtable_ref` int(11) NOT NULL,
  `table_refcol` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtable_ref`),
  UNIQUE KEY `table_refcol_UNIQUE` (`table_refcol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table_ref` VALUES (11,'eleven'),(12,'twelve');

CREATE DATABASE IF NOT EXISTS `db_target`;
USE `db_target`;
DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
  `idtable_1` int(11) NOT NULL,
  `idtable_ref` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtable_1`),
  KEY `table_ref_fk_idx` (`idtable_ref`),
  CONSTRAINT `table_ref_fk` FOREIGN KEY (`idtable_ref`) REFERENCES `db_ref_target`.`table_ref` (`idtable_ref`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table_1` VALUES (1,11),(2,12);

Michael Christofides

Hi, thank you for doing this! Unfortunately we don't have a team actively on the MySQL tools at the moment, but we'll log this. I'm glad to hear you have a workaround for now, and thanks again for sharing it for any others who hit this.
Best regards,
Michael

skolbunov

Hi Michael,
I really like the MySQL Comparison tools and use them daily. They give exactly what I need with the great user interface and without overload of "bells and whistles".
It is so pity to hear that you are not actively support them. I just hope your team will continue to develop these tools.
Thank you,
Sergey