MySQL Exception

Eight Stars

MySQL Exception

Hi,

 

I was getting following Exception in tMysqlOutput component.

In this job just i was transferring file from file location to FTP location and updating this in MySQL Database.

Below is the Exception Code. Could you guys please help me on this.

 

java.sql.SQLException: Could not retrieve transation read-only status server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:949)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3976)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3947)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1356)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.tFileList_2Process(FTP_Transfer_Child.java:10457)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.tFTPConnection_2Process(FTP_Transfer_Child.java:8134)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.tJava_3Process(FTP_Transfer_Child.java:5074)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.tJava_2Process(FTP_Transfer_Child.java:4896)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.runJobInTOS(FTP_Transfer_Child.java:13161)
at devry.ftp_transfer_child_1_0.FTP_Transfer_Child.runJob(FTP_Transfer_Child.java:12677)
at devry.ftp_transfer_1_0.FTP_Transfer$1tRunJob_1Thread.run(FTP_Transfer.java:3704)
at routines.system.ThreadPoolWorker.runIt(TalendThreadPool.java:159)
at routines.system.ThreadPoolWorker.runWork(TalendThreadPool.java:150)
at routines.system.ThreadPoolWorker.access$0(TalendThreadPool.java:145)
at routines.system.ThreadPoolWorker$1.run(TalendThreadPool.java:122)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 6,819,103 milliseconds ago. The last packet sent successfully to the server was 980,015 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3970)
... 14 more

 

Thanks,

Bharath.

 

Seven Stars skh
Seven Stars

Re: MySQL Exception

Hi Javvaji,

Can you confirm whether you have permissions to access the database. In order to get the more elaborate information on this Error, better you can share us the Mapping Screen-Shot of your entire JOB.

Thanks,

hmd.

Highlighted
Eight Stars

Re: MySQL Exception

Hi skh,

Thanks for your reply.

 

I don't have access to DB. Mapping means the component mapping where i am getting error.

 

Thanks,

Bharath

Eight Stars

Re: MySQL Exception

Hi,

 

Can any one help me on this issue.

 

Thanks,

Bharath.

Seven Stars skh
Seven Stars

Re: MySQL Exception

If you look into the Error it shows Communications link failure .

This Error is due communication/network issue. This indicates that a TCP/IP connection could not be established to the target host. Check all the hosts ,ports, firewall settings of your system. Check whether you are able to access the Mysql Database from the system where you designed your Talend Job.

Please share your Talend Job, so that we can look into the settings.

Thanks,

 

 

Eight Stars

Re: MySQL Exception

Hi skh,

Thanks for your reply.

 

Actually we are transferring 18 files from file location to ftp location. For all the files the status is updating in DB, but for one file i am getting the error. The error file size is 4.3 GB. But finally the file is coping to FTP but not updating in DB and getting error for only this file. Here i am attaching the screen shot of job.

 

Thnaks,

Bharath.

Forteen Stars

Re: MySQL Exception

one of the possible reasons for this - long time for upload this big file (it longer than timeout for connection)

 

You can play with JDBC parameters - keepAlive, but depending from driver version it not always help, working solutions:

 

1) first transfer all files,

than define connection and update all tables

do not define shared connection in tPreJob if use it after long delay - You can define connection at any step in a Job

 

2) stop use connection and use connection parameters in each MySQLOutput

 

 

-----------

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog