3

When trying to load one month data from DW by the last day of the month or second last day of the month, while loading the data, communication link error comes and it stops the running job. due to this last few set of data is not loading.

About transformation steps used:

Table Input-> DB JOIN -> Stream Lookup-> Table output

please help on this. We are stuck with this.. 

2019/01/11 05:42:18 - Table input.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected error closing query : org.pentaho.di.core.exception.KettleDatabaseException: 
2019/01/11 05:42:18 - Table input.0 - Couldn't close query: resultset or prepared statements
2019/01/11 05:42:18 - Table input.0 - **Communications link failure**
2019/01/11 05:42:18 - Table input.0 - 
2019/01/11 05:42:18 - Table input.0 - **The last packet successfully received from the server was 109 milliseconds ago.  The last packet sent successfully to the server was 22 milliseconds ago.**
Sathish D
  • 4,854
  • 31
  • 44
  • just a very wild guess, but failures that happen on very specific dates sometimes are caused by batch jobs scheduled to run on those days. Like a backup job, a long-running database dump, some cleanup routine or similar stuff. I'd first check whether the database server restarted roughly at that time. – Dirk Trilsbeek Jan 11 '19 at 19:52
  • which pentaho version you are using? and which mysql jdbc connection jar file version you are using? – Helping Hand.. Jan 15 '19 at 02:39
  • @WorkingHard.. we are using pentaho 7.1 and mysql-connector-java-5.1.46.jar for mysql jdbc connection – Sathish D Jan 16 '19 at 05:02

2 Answers2

0

This problem can be caused because of many reasons. I would check the following points if I were you.

  1. Problems with MySQL - you should check if there are any errors in the mysql log during your process execution (https://dev.mysql.com/doc/refman/5.6/en/error-log.html)
  2. It can be a memory issue. Try to increase Java heap size for Spoon. The similar problem is described on https://communities.bmc.com/docs/DOC-100146
  3. Try to optimize MySQL or JDBC parameters. For example, you can increase net-write-timeout and net-read-timeout on MySQL server, or rewriteBatchedStatements=true and useCompression=true on JDBC driver.
jbgorski
  • 1,824
  • 9
  • 16
0

This kind of issue also raise due to long running queries and this happens due to net_write_timeout which is set as by default for 60 seconds. this parameter found in my.ini where you can change the default value of net_write_timeout to some larger value which can help you to recover from this issue.

Kandy
  • 673
  • 9
  • 21