MySQL Exception

[javvaji]

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.

[hami1]

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.

[javvaji]

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

[javvaji]

Hi,

Can any one help me on this issue.

Thanks,

Bharath.

[hami1]

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,

[javvaji]

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.

FTP_Transfer_Child.jpg

[vapukov]

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