Insert/update CSV files to database (MySQL char limit/ last table text garbled)

Highlighted
Eight Stars

Insert/update CSV files to database (MySQL char limit/ last table text garbled)

Hi there,

 

I wanted to use CSV to insert/update DB record (using dynamic schema)  with the following job design.

(Insert and Update Record will shared the same 1st job design)

 **the 1st job has a problem, so I will only explain this one.

 

**If there's a way to directly update a record (with dynamic schema), please let me know.

 

FYI, the input source will be from CSV and there's also original source in MSSQL while the final output should be in MySQL DB.

 

Insert/Create New Record from CSV

CSV InputCSV Input

However, there are two major problems here (insert case).

 

1) When I set the tDBOutput as MySQL, the row limit error occurred.

 

Exception in component tDBOutput_1 (Data_Update1)
java.sql.SQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some
columns to TEXT or BLOBs

Is there a way to make it work in MySQL?

Some columns has 1000 characters while Talends only allows 100-255 characters per column.

Is there a way to dynamically set the column character limit? or make it all 1000 characters/column?

 

2) When I set tDBOutput to MSSQL, there's no problem with character limit.

However the last table's text data is not encoding properly, resulting in garbled text.  Encoding is already set to UTF-8.

encoding settingencoding setting

 

When trying the update, primary key(in my case, a compose pk and pk fields) is needed, however, I do not know how I can set PK dynamically, like how dynamic column is. 
Does anyone know how to do so?

 

Right now, I'm working on a lengthy way to update record as the following:

1st Job: create and store record in temp DB

2nd Job: inject constraint into temp DB's tables

3rd job: migrate to real DB

 

If there's better way than this, please let me know.

And if there's anything unclear please let me know.

 

Thank you

 

 

Highlighted
Moderator

Re: Insert/update CSV files to database (MySQL char limit/ last table text garbled)

@crotmn , have you verified below link?

 

https://community.talend.com/t5/Design-and-Development/MySQLSyntaxErrorException-Row-size-too-large/...

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Insert/update CSV files to database (MySQL char limit/ last table text garbled)

@manodwhb

Yes, I did.
It works fine when migrating database from MSSQL to MySQL, however it doesn't work when importing from CSV...
Is there anyway to make this work? I think I heard about setting single row, but to be honest I'm not sure how that works.

 

For you reference, this is an error when I tried to import a single CSV to database.

Exception in component tDBOutput_1 (testconnection)
java.sql.BatchUpdateException: Data truncation: Data too long for column 'comment' at row 14
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Talend Cloud Developer Series – Updating Context Variables

This video will show you how to add context parameters to a job in Talend Cloud

Watch Now

Talend Cloud Developer Series – Deploying First Job to Cloud

This video will show you how to run a job in Studio and then publish that job to Talend Cloud

Watch Now

Talend Cloud Developer Series – Fetching Studio License

This video will help someone new to using Talend Studio get started by connecting to Talend Cloud and fetching the Studio License

Watch Now