Whole Database Migration (MSSQL->MySQL) using Dynamic Schema

[Anonymous]

Hi,

I just recently tried using Talend Cloud Data Integration (Talend Studio 7.2)

and I'm figuring how to dynamically migrate a whole MSSQL server database to MySQL(blank DB).  

So far, I heard that it's possible to do that with Dynamic Schema.

However, I only found a tutorial for a table (data) migration, or some post that didn't specifically explained much about settings and components to use.

Here's my job design (UPDATED!!!)

I'm using global variable to dynamically define the table, however, I'm not sure how i can define the schema dynamically.

**I supposed my query is right.

Can you let me know how I can define the schema dynamically here?

Output Component

Spoiler

The other option I found was to use job template, however, it doesn't help deal with syntax error that occurred during migration like the following.

sample1)

Exception in component tDBOutput_1 (Job_tbl_mem)
java.sql.SQLSyntaxErrorException: BLOB, TEXT, GEOMETRY or JSON column 'msrepl_tran_version' can't have a default value
	at com.mysql.cj.jd

sample2)

[FATAL]: local_test.job1_tbl_mem_0_1.Job1_tbl_mem - tDBOutput_4 Invalid default value for 'mobile_inq'
java.sql.SQLSyntaxErrorException: Invalid default value for 'mobile_inq'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at 

In addition, I was hoping to find a solution where one job could perform a whole database migration. Eg) Iterating tables in a source DB then generate an output a destination DB.

Please let me know either if I should proceed with Dynamic Schema or Job Template + job design/components/setting needed.

Thank you

[Anonymous]

Yes, dbo is a schema in source db (MSSQL) and I have it in my output MySQL db.

However, database and schema seems to be treated similarly in MySQL, so I just called it a db name.

<Source MSSQL>  Database: TRAVELCOJP_DB // Schemas: dbo, traveltour

<Output MySQL> Database/Schema: dbo, traveltour

Here I was wondering if you knew something about the issue below... 

*the data of the last table(of a schema) is not migrated... 

---

@crotmn wrote:

Here I just notice another problem, when I migrate a database with the following job design, I noticed that only the last table's data is not copied (only the table structure and column is migrated). I tried modifying the query but it doesn't work.

Do you have any idea how I can improve the query statement?

dbinput's component

traveltour schema has two tables: tbl_inq and tbl_mem, however only the first one (tbl_inq) has its data migrated while the second table (tbl_mem) only has a blank table.

*I tested this with other database and the result is the same. I also check the source db table, and there is a data.

first table(have data)second table(no data)

Thank you so muchh

---

[JohnRMK]

No idea ! sorry 

[Anonymous]

@JohnRMK 

Thank you very much for helping me out on the other day.

Here I have additional question.

Here I tried to add foreign key, but seems like there's a syntax error. 

I added a foreign with its references, but seems like I might not put it correctly,

"ALTER TABLE "+row16.table_view+" ADD CONSTRAINT "+row16.constraint_name+" FOREIGN KEY ("+ row16.details+ ") 
REFERENCES ("+ row16.details+ ");" 

Here's the error message.

[statistics] connecting to socket on port 3860
[statistics] connected
dbo.AREA|ALTER TABLE dbo.AREA ADD CONSTRAINT PK_AREA PRIMARY KEY (CD);
Table Name = dbo.AREA 
 AND the Query is = ALTER TABLE dbo.AREA ADD CONSTRAINT PK_AREA PRIMARY KEY (CD);

||||
dbo.AREA|ALTER TABLE dbo.AREA ADD CONSTRAINT FK_AREA_DOM_AIRLINE 
FOREIGN KEY (dbo.DOM_AIRLINE) REFERENCES (dbo.DOM_AIRLINE);
Table Name = dbo.AREA 
 AND the Query is = ALTER TABLE dbo.AREA ADD CONSTRAINT FK_AREA_DOM_AIRLINE 
FOREIGN KEY (dbo.DOM_AIRLINE) REFERENCES (dbo.DOM_AIRLINE);
||||
Exception in component tDBRow_3 (test03)
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '.DOM_AIRLINE) REFERENCES (dbo.DOM_AIRLINE)' at line 1

I know it's been a while but would appreciate your help alot. 

[JohnRMK]

Hello,

I helped you previously and I answered more times and even developed jobs for you.

But, I'm sorry, that's a lot. You are new to talend and you are tackling things that are managed by a whole team and that takes months of development under talend with good experience.

You have monopolized the forum and you are at the maximum of the number of response. Each time you solve one problem, you have another. If I have one last piece of advice to give you, start with the basics and best practices.

I will answer your question one last time, concerning the last error. You are trying to add a foreign key to a table in the same way as a primary key! and it's wrong. This is another type of query that must be generated and added the reference to the key in the other table. Review the basics of SQL Server and the referential integrity a bit, you will find the solution.

Wish you a Good luck.

[Anonymous]

Thanks for getting back to me.
I appreciate all the effort you have provided me.
Regarding the foreign key, sorry for not updating it here but I figured out a few days ago. At that point, I assumed that I will need to get foreign key(column), reference table and column, so I wanted to asked you how I could modified the DBInput's statement or if there's other way to modified it.

I have been doing self-study alot both through documentation and community discussion, however, there are times where I could not figure out by myself so I asked in the forum. I am doing a research and experiment on behalf of my team, and we need to quickly figure it out so I'm kinda hurried on figuring out the problems...

I will try to study even more and post less problems.
And again, thank you so much for helping me out previously.

[archienesss]

Hello rhall,

Thanks for the response! I hope this link can be made available again as this will really help me with the issue I am having with Dynamic schema. Thanks!