rows are missing while loading to mysql

Five Stars

rows are missing while loading to mysql

Hi,

I was reading a JSON file with 1441 records and tried to load it to MySQL server. After loading, I can see that only 1369 rows are inserted. I checked schema to see whether there is any truncation issue and found no issues. Can anyone help me with this?


Accepted Solutions
Nine Stars

Re: rows are missing while loading to mysql

Hello 

After you tmssql component use tjavarow

and try to use this code

Number of records successfully loaded : " + ((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_INSERTED"))

In talend studio to the left of it, you will have outline in that all the components will be appeared try to use them

Please find picture attached

insertsintomssqloutput.PNG

View solution in original post


All Replies
Nine Stars

Re: rows are missing while loading to mysql

Hi @amruthqs4talend,

Can you pls check the connection in the sql builder of the tmysqloutputbulkexec component.. and pls check 

append option and action on table and action on data....

Here is the link for output component: https://help.talend.com/reader/hCrOzogIwKfuR3mPf~LydA/7cjWwNfCqPnCvCSyETEpIQ

One of the reason could also be that the after the insertion the commit for them isnt done.

Pls do get back to Community,if necessary.

Thanks,

Ankit.

Five Stars

Re: rows are missing while loading to mysql

I am using tdboutputbulkexec component and it has "action on table" option, not the "action on data". I am using drop table if exist and create option. 

Nine Stars

Re: rows are missing while loading to mysql

Hi @amruthqs4talend,

Which Talend version are you using ???

I ll try to look from my end. How i can help 

Thanks,

Ankit

Five Stars

Re: rows are missing while loading to mysql

I am using Talend Open Studio for Data Integration Version: 7.1.1.

Thanks a lot..

Nine Stars

Re: rows are missing while loading to mysql

Hi @amruthqs4talend,

I have a query though, why exactly are you using tlogrow in between json object and output component?

is the schema checked??

I apologise,but i use talend 6.3.2 where i dont have this tdb - component.?

But i will try to look..what can be done.

Thanks,

Ankit.

Highlighted
Five Stars

Re: rows are missing while loading to mysql

Hi,

Some older community messages suggested the usage of tlogrow. It shows me the records in talend output window and prints 1441 records. 

Thanks,

Amrutha

Seven Stars

Re: rows are missing while loading to mysql

Hey Can you check the length of the data field in the output. may be this can help.

Five Stars

Re: rows are missing while loading to mysql

hi,

I have checked the length of columns and it was same as auto-selected by schema. This was my first guess when things did not work out.

Thanks,

Amrutha

Employee

Re: rows are missing while loading to mysql

Hi,

 

   Could you please try some basic verifications as stated below?

 

a) Try to replace the bulk component with tMysqlOutput component with Die on Error option enabled and retaining current schema setup. This will show the error you are facing if its related to schema or data errors.

b) Setup the schema length according to target columns in DB. If necessary, add a schema check component in between.

c) Once these verification are over, replace the output component with Bulk component and run the flow again and see the output.  

 

Hope it should help to identify the root cause.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: rows are missing while loading to mysql

Hi,

I have tried the first idea suggested by you and it did not change the output.

However, when am writing the data to a csv or excel format, am getting all the rows. How could that be possible?

Employee

Re: rows are missing while loading to mysql

Hi,

 

     Do you have records in incoming flow with same key values of database tables? In the case of Database, it will be overwritten but in the case of files, they will be considered as multiple rows.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Employee

Re: rows are missing while loading to mysql

@amruthqs4talend 

 

If the answer has helped you, could you please mark the topic as resolved?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: rows are missing while loading to mysql

That was a good suggestion but it did not solve my problem.

Employee

Re: rows are missing while loading to mysql

Hi,

 

   Are you saying all the records are having unique keys and they do not have any duplicates?

 

   Also did you try to use tDBOutput with Die on Error option enabled? If there are any data issues, it will reveal them by failing at that record.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

Nine Stars

Re: rows are missing while loading to mysql

Hello 

After you tmssql component use tjavarow

and try to use this code

Number of records successfully loaded : " + ((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_INSERTED"))

In talend studio to the left of it, you will have outline in that all the components will be appeared try to use them

Please find picture attached

insertsintomssqloutput.PNG

View solution in original post

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog