View Issue Details

IDProjectCategoryView StatusLast Update
0005063SymmetricDSBugpublic2021-07-21 17:58
Reporterrjna Assigned To 
Prioritynone 
Status newResolutionopen 
Product Version3.12.11 
Summary0005063: Unable to import Oracle DB schema and data into MariaDB
DescriptionI was able to export the schema and data from my Oracle DB using the command:

bin/dbexport -p /path_to_my_oracle_properties_file --schema myschema > my_output.sql

Then, bin/dbexport --help lists some arguments to be used with the compatible option: db2, db2zos, derby, firebird, greenplum, h2, hsqldb, hsqldb2, informix, interbase, mssql, mysql, oracle, postgres and sybase.

Since MariaDB is not listed as an argument, I tried to use mysql.

Then I tried to import the my_output.sql file into my MariaDB:

bin/dbimport -p path_to_my_mariaDB_properties_file my_output.sql

This works up to a point (e.g. some tables are created), but it gives the following error:

[] - DefaultDatabaseWriter - Failed to run the following sql: ALTER TABLE `HR`.`EMPLOYEES`
    ADD CONSTRAINT `EMP_DEPT_FK` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `HR`.`DEPARTMENTS` (`DEPARTMENT_ID`)

java.sql.SQLException: Can't create table `HR`.`EMPLOYEES` (errno: 150 "Foreign key constraint is incorrectly formed")

Is there a way to make the dbexport output compatible with MariaDB?

Also, here: https://www.symmetricds.org/doc/3.4/javadoc/org/jumpmind/symmetric/io/data/DbExport.Compatible.html MariaDB is listed as an option, but I was unable to use it as a compatible argument of dbexport (tried to write it in different ways and tried to use mysql/mariadb).

Thank you very much!
Ricardo
Tagsdata conversion;, dbexport;, dbimport/dbexport;, dialect: mysql/mariadb;, dialect: oracle;

Activities

rjna

2021-07-21 15:47

reporter   ~0001966

Hi guys!

I was able to solve this by manually editing the my_output.sql file.

The issue was that I had 2 tables that had a FK constrain with each other.

So, one of the constrains was being created before the another table was created, and that was the source of the error.

Solved it by moving the FK constrain creation to a point after the second table creation.

Thank you!
Ricardo

rjna

2021-07-21 15:47

reporter   ~0001967

Hi guys!

I was able to solve this by manually editing the my_output.sql file.

The issue was that I had 2 tables that had a FK constrain with each other.

So, one of the constrains was being created before the another table was created, and that was the source of the error.

Solved it by moving the FK constrain creation to a point after the second table creation.

Thank you!
Ricardo

pmarzullo

2021-07-21 17:58

developer   ~0001968

This is really a SymmetricDS issue.

What needs to be done is to export the table creates first, then the indexes, then the constraints.

Issue History

Date Modified Username Field Change
2021-07-21 09:25 rjna New Issue
2021-07-21 09:25 rjna Tag Attached: data conversion;
2021-07-21 09:25 rjna Tag Attached: dbexport;
2021-07-21 09:25 rjna Tag Attached: dbimport/dbexport;
2021-07-21 09:25 rjna Tag Attached: dialect: mysql/mariadb;
2021-07-21 09:25 rjna Tag Attached: dialect: oracle;
2021-07-21 15:47 rjna Note Added: 0001966
2021-07-21 15:47 rjna Note Added: 0001967
2021-07-21 17:58 pmarzullo Note Added: 0001968