Issue with Demo from MS SQL => MySQL

[Nick Albright]

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:

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

ADD CONSTRAINT fk_price_item_id FOREIGN KEY (item_id) REFERENCES EVEREST_ALT.dbo.item (item_id)

And it should jsut be: EVEREST_ALT.item (item_id)

How can I fix?

Thanks! :)
Nick

[efraim machado]

did you set the source/target schema/catolog?

[Nick Albright]

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

[Nick Albright]

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">
<database name="dataextractor" catalog="everest_alt">
        <table name="item">
                <column name="item_id" primaryKey="true" required="true" type="INTEGER" size="10">
                        <platform-column name="mssql2008" type="int" size="10"/>
                </column>
                <column name="name" type="VARCHAR" size="100">
                        <platform-column name="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">
<database name="dataextractor">
        <table name="item_selling_price">
                <column name="item_id" primaryKey="true" required="true" type="INTEGER" size="10">
                        <platform-column name="mssql2008" type="int" size="10"/>
                </column>
                <column name="store_id" primaryKey="true" required="true" type="VARCHAR" size="5">
                        <platform-column name="mssql2008" type="varchar" size="5"/>
                </column>
                <column name="price" required="true" type="DECIMAL" size="10,2">
                        <platform-column name="mssql2008" type="decimal" size="10" decimalDigits="2"/>
                </column>
                <column name="cost" type="DECIMAL" size="10,2">
                        <platform-column name="mssql2008" type="decimal" size="10" decimalDigits="2"/>
                </column>
                <foreign-key name="fk_price_item_id" foreignTable="item" foreignTableCatalog="EVEREST_ALT" foreignTableSchema="dbo">
                        <reference local="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

[Mark Michalek]

Nick, what version are you running with?