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

[JohnRMK]

The tDBRow component is used to execute query or functions. It does not need a schema and is not a read or write component. So, you just have to put the request generate (the one displayed in the System.out.prinln of tJava) in the code and use a connection to an existing database.

Try to take the request generated in tLogRow and execute it in SQL Workbench to see if it works. A priori yes I tested it on SQL Server

For the result I have no idea, the code I gave you works on a database that I use. The error comes either from the way you created your keys in the source database (instead of grouping two columns as one key, you create two constraints with two different names) or something else. I don't really master databases

Here is an example of PK with multiple  values

Table Name = dbo.FactInternetSales 
 AND the Query is = 
ALTER TABLE dbo.FactInternetSales 
ADD CONSTRAINT PK_FactInternetSales_SalesOrderNumber_SalesOrderLineNumber 
PRIMARY KEY (SalesOrderNumber, SalesOrderLineNumber);

[Anonymous]

@JohnRMK 

Thanks for your prompt response.

I have change tDBRow Component as you mentioned, 

dbrow

However, the following errors occured.

dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH 
ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH,PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH 
PRIMARY (mem_id, tour_id, money_start_date, money_end_date,mem_id, tour_id, money_start_date, money_end_date);
Table Name = dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH 
 AND the Query is = ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT 
PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH,PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY 
(mem_id, tour_id, money_start_date, money_end_date, mem_id, tour_id, money_start_date, money_end_date);

[FATAL]: local_test.job_tbl_mem_0_1.Job_tbl_mem - tDBRow_1 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 ',PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY (mem_id, tour_id, money_start_date,' at line 1

When I traced the job, I found that from roll 6(aggregateRow => tMap), constraint name contained a duplicated 'PK___' values...

Does this something to do with the error above??

row6 trace

dynamic PK's job design

About the repeating PK values, it currently happens in case of one PK columns,,

I haven't check in MYSQL Workbench yet, as I haven't finish this yet.

[JohnRMK]

You changed the code that I gave you
Normally, you must have .<...Unique_Name PRIMARY KEY (val1, val2)>
And, in the tAggregateRow, you have to aggregate in list the details and column name and group by the table name.

I can't do anything more, because I test on my computer and it works.

[JohnRMK]

As you can see in the picture, a single key name and a list of fields
That’s the way you create your keys maybe, sorry I don’t know what to do with a DBA

[Anonymous]

@JohnRMK 

I just realized the problem was because of TableList component I used,

I thought it was necessary to iterate through list of table .....

There's another error related to DBRow.

It will summed up the points in the next post.

I went back to check my code, but I didn't change anything from what you gave me.

Below here, I tried using the job(project file) you sent over, but it didn't work either.

The only minor changes I made here is that I added a TableList component and connect to DBConnection and HashInput with OnSubJobOk, instead of DBInput, as I will need to iterate over list of tables.

Does this have anything to do with the changes in constraint name?

From tracing, it seems like constraint_name got repeated twice from tAggregateRow (row5) but I'm didn't make any changes there. 

my aggregaterow  component(no change from your code)

aggregaterow-component

Job_tbl_mem.zip

[Anonymous]

@JohnRMK 

I went back to check my code, but I didn't change anything from what you gave me.

The problems seems to be related to the TableList component I uses to iterate over list of tables.

Here's I have a few points to confirm.

In my cases I'm migrating from MSSQL => MySQL.

So after migrating database without keys.

The next step is to inject the keys into MySQL database(??), which is the code you gave me previously.

For DBinput I used the source (MSSQL) and database for DBRow and DBCommit I used a MySQL database.

I added MySQL DBConnection as the folllowing.

However an error 'unknown database' occurred. 

DbRow 

Is this because of the way I set up my job design/dbconnections??

Or I should named the output DB (MySQL) exactly the same as the input DB(MSSQL)?

Please let me know if anything is unclear.

Thank you very much

[Anonymous]

@cbma_1994 　(I don't know why my reply was deleted a couple of times lol)

I went back to check my code, but I didn't change anything from what you gave me.

The problems seems to be related to the TableList component I uses to iterate over list of tables.

Here's I have a few points to confirm.

In my cases I'm migrating from MSSQL => MySQL.

So after migrating database without keys.

The next step is to inject the keys into MySQL database(??), which is the code you gave me previously.

For DBinput I used the source (MSSQL) and database for DBRow and DBCommit I used a MySQL database.

I added MySQL DBConnection as the folllowing.

However an error 'unknown database' occurred. 

DbRow

Is this because of the way I set up my job design/dbconnections??

Or I should named the output DB (MySQL) exactly the same as the input DB(MSSQL)?

Please let me know if anything is unclear.

Thank you very much

[JohnRMK]

[Anonymous]

@JohnRMK 

Thanks for your reply.

It worked when I change the output database to the one with the exact same name.

ex) input DB with schema: 'dbo' ==> output database: 'dbo'

Though I still need to make some additional adjustment to make sure one schema is processed at a time.

*As my database contain two schemas, I have to use WHERE statement (table_view like 'dbo%') in DBInput when processing 'dbo' schema.

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 removing '+' but it doesn't work.

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

dbinput's component

traveltour schemas 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]

dbo is the name of the schema, not the database. It is hierarchical (Database (HR or Finance => schema (dbo or Fact, Dim) => tables (Person or Address))
The schema definition is done in the tDBConnection component and by default is dbo.