CODEWITHSUNDEEP
mysqljdbcsymmetricds
SMSk
SMSk

Reputation: 705

SymmetricDS messes up with tripple foreign key

I have a database which is supposed to be synchronized using SymmetricDS. There are following queries, building the base:

CREATE TABLE `defectstdreference` (
  `std_doc_id` bigint(20) NOT NULL COMMENT 'Идентификатор НТД',
  `improve_notice_doc_id` bigint(20) NOT NULL COMMENT 'Идентификатор предписания',
  `defect_id` int(11) NOT NULL COMMENT 'Идентификатор дефекта',
  `paragraph` varchar(4000) NOT NULL COMMENT 'Пункт НТД',
  `reference_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор ссылки на пункт НТД',
  `client_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`reference_id`),
  KEY `Relationship70` (`std_doc_id`),
  KEY `Relationship72` (`improve_notice_doc_id`,`defect_id`,`client_id`),
  CONSTRAINT `Relationship70` FOREIGN KEY (`std_doc_id`) REFERENCES `std` (`doc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Relationship72` FOREIGN KEY (`improve_notice_doc_id`, `defect_id`, `client_id`) REFERENCES `improvementnoticedefect` (`doc_id`, `defect_id`, `client_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='Ссылки на НТД выявленных нарушений';

and the table which is referenced:

CREATE TABLE `improvementnoticedefect` (
  `description` varchar(20000) NOT NULL COMMENT 'Описание нарушения',
  `defect_id` int(11) NOT NULL COMMENT 'Номер нарушения в рамках предписания',
  `doc_id` bigint(20) NOT NULL COMMENT 'Номер документа "Предписание"',
  `client_id` bigint(20) NOT NULL,
  PRIMARY KEY (`doc_id`,`defect_id`,`client_id`),
  KEY `Relationship68` (`doc_id`,`client_id`),
  CONSTRAINT `Relationship68` FOREIGN KEY (`doc_id`, `client_id`) REFERENCES `improvementnotice` (`doc_id`, `client_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Дефект';

As you can see, the order of columns in primary key of table impovementnoticedefect and in fk constraint 72 of defectstdreference are the same.

Yet, when the synchronization is started, SymmetricDS gives me the error:

Cannot add foreign key constraint.  Failed to execute: ALTER TABLE `defectstdreference`
    ADD CONSTRAINT `Relationship72` FOREIGN KEY (`improve_notice_doc_id`, `defect_id`, `client_id`) REFERENCES `improvementnoticedefect` (`doc_id`, `defect_id`, `client_id`)

and when I try to dump sql for the table SymmetricDS created I get the following:

CREATE TABLE `improvementnoticedefect` (
  `defect_id` int(11) NOT NULL,
  `doc_id` bigint(20) NOT NULL,
  `client_id` bigint(20) NOT NULL,
  `description` varchar(20000) NOT NULL,
  PRIMARY KEY (`defect_id`,`doc_id`,`client_id`),
  KEY `Relationship68` (`doc_id`,`client_id`),
  CONSTRAINT `Relationship68` FOREIGN KEY (`doc_id`, `client_id`) REFERENCES `improvementnotice` (`doc_id`, `client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you can see, the order of fields in primary key is different.

I tried running the erroneous query manually, and it gave an error, but when I changed the order of fields, everything was fine.

How can I fix this? Is there any configuration I should do or smth? I'm really desperate here, can anybody help?

Update I have realized, that the problem is that SymmetricDS uses XML to send tables, and PK is created from XML: 

<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="improvementnoticedefect">
         <column name="defect_id" primaryKey="true" required="true" type="INTEGER" size="10">
             <platform-column name="mysql" type="INT" size="10"/>
         </column>
         <column name="doc_id" primaryKey="true" required="true" type="BIGINT" size="19">
             <platform-column name="mysql" type="BIGINT" size="19"/>
         </column>
         <column name="client_id" primaryKey="true" required="true" type="BIGINT" size="19">
             <platform-column name="mysql" type="BIGINT" size="19"/>
         </column>
         <column name="description" required="true" type="VARCHAR" size="20000">
             <platform-column name="mysql" type="VARCHAR" size="20000"/>
         </column>
         <foreign-key name="Relationship68" foreignTable="improvementnotice">
             <reference local="doc_id" foreign="doc_id"/>
             <reference local="client_id" foreign="client_id"/>
         </foreign-key>
     </table>
</database>

And you can see, that from this XML primary key is generated in order of created columns, which is actually wrong

Upvotes: 2

Views: 321

Answers (1)

Pavel_JM
Pavel_JM

Reputation: 11

if anyone came here because of similar error in the logs, this particular issue had been fixed in the version 3.14.2. https://downloads.jumpmind.com/symmetricds/doc/3.14/html/release-notes.html

Now, that is not the latest version, so don't rush to install it ;-)

Upvotes: 1

Related Questions