I'm having issues getting the the initial data sync using the Demo from the docs. The source is a MS SQL DB, and I'm trying to sync it over to a MySQL DB. It creates the table fine from what I can, but had issue with a constraint. The line seems to be:
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `item_selling_price`(
`item_id` INTEGER NOT NULL,
`store_id` VARCHAR(5) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`cost` DECIMAL(10,2),
PRIMARY KEY (`item_id`, `store_id`)
);
ALTER TABLE `item_selling_price`
ADD CONSTRAINT `fk_price_item_id` FOREIGN KEY (`item_id`) REFERENCES `EVEREST_ALT`.`dbo`.`item` (`item_id`);
[store-001] - JdbcSqlTemplate - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`item` (`item_id`)' at line 2. Failed to execute: ALTER TABLE `item_selling_price`
ADD CONSTRAINT `fk_price_item_id` FOREIGN KEY (`item_id`) REFERENCES `EVEREST_ALT`.`dbo`.`item` (`item_id`)
Feels like the issue is that it is adding dbo in the line:
I hadn't! :) But I just did! I set the target_catalog_name, target_schema_name and then changed use_source_catalog_schema to 0 in sym_router for the 'sql server' machine to 'mysql' machine, but it is still trying to use the source catalog / schema..
Do I need to do anything after I make the changes in sym_router?
Thanks for your time!
NIck
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, so this is really weird. I added the target_catalog_name; NULL'd the target_schema_name and set the use_source_catalog_schema=0 for the mysql machine. I then tell it to reload the node and I get a fresh set of table creates. The item table goes great, using this xml:
<?xml version="1.0"?><!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"><databasename="dataextractor"catalog="everest_alt"><tablename="item"><columnname="item_id"primaryKey="true"required="true"type="INTEGER"size="10"><platform-columnname="mssql2008"type="int"size="10"/></column><columnname="name"type="VARCHAR"size="100"><platform-columnname="mssql2008"type="varchar"size="100"/></column></table></database>
However, the second table doesn't sepcify the catalog in the database tag, and then for the foreign key it uses the source schema and catalog:
<?xml version="1.0"?><!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"><databasename="dataextractor"><tablename="item_selling_price"><columnname="item_id"primaryKey="true"required="true"type="INTEGER"size="10"><platform-columnname="mssql2008"type="int"size="10"/></column><columnname="store_id"primaryKey="true"required="true"type="VARCHAR"size="5"><platform-columnname="mssql2008"type="varchar"size="5"/></column><columnname="price"required="true"type="DECIMAL"size="10,2"><platform-columnname="mssql2008"type="decimal"size="10"decimalDigits="2"/></column><columnname="cost"type="DECIMAL"size="10,2"><platform-columnname="mssql2008"type="decimal"size="10"decimalDigits="2"/></column><foreign-keyname="fk_price_item_id"foreignTable="item"foreignTableCatalog="EVEREST_ALT"foreignTableSchema="dbo"><referencelocal="item_id"foreign="item_id"/></foreign-key></table></database>
And then of course bombs with this error:
[store-001] - MariaDBDatabasePlatform - Running alter sql:
ALTER TABLE `item_selling_price`
ADD CONSTRAINT `fk_price_item_id` FOREIGN KEY (`item_id`) REFERENCES `EVEREST_ALT`.`dbo`.`item` (`item_id`);
[store-001] - JdbcSqlTemplate - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.`item` (`item_id`)' at line 2. Failed to execute: ALTER TABLE `item_selling_price`
ADD CONSTRAINT `fk_price_item_id` FOREIGN KEY (`item_id`) REFERENCES `EVEREST_ALT`.`dbo`.`item` (`item_id`)
I've no idea why it would use the right catalog and schema for the item table, but not the item_selling_price table..
Thoughts? Thanks!
Nick
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello!
I'm having issues getting the the initial data sync using the Demo from the docs. The source is a MS SQL DB, and I'm trying to sync it over to a MySQL DB. It creates the table fine from what I can, but had issue with a constraint. The line seems to be:
Feels like the issue is that it is adding
dbo
in the line:ADD CONSTRAINT
fk_price_item_id
FOREIGN KEY (item_id
) REFERENCESEVEREST_ALT
.dbo
.item
(item_id
)And it should jsut be:
EVEREST_ALT
.item
(item_id
)How can I fix?
Thanks! :)
Nick
did you set the source/target schema/catolog?
I hadn't! :) But I just did! I set the target_catalog_name, target_schema_name and then changed use_source_catalog_schema to 0 in sym_router for the 'sql server' machine to 'mysql' machine, but it is still trying to use the source catalog / schema..
Do I need to do anything after I make the changes in sym_router?
Thanks for your time!
NIck
OK, so this is really weird. I added the target_catalog_name; NULL'd the target_schema_name and set the use_source_catalog_schema=0 for the mysql machine. I then tell it to reload the node and I get a fresh set of table creates. The item table goes great, using this xml:
However, the second table doesn't sepcify the catalog in the database tag, and then for the foreign key it uses the source schema and catalog:
And then of course bombs with this error:
I've no idea why it would use the right catalog and schema for the item table, but not the item_selling_price table..
Thoughts? Thanks!
Nick
Nick, what version are you running with?