MySQL / ETL error

[moon]

Hi there,

we are getting an error after between 5 and 10 minutes of an ETL starting. The stack trace is always the same (except how many ms it takes to fail!).

Our ETL takes MySQL VIEW data and transforms it from its resident database to a new database as a different table structure. This ETL we have written for different tables with 100% success. There seems to be a MYSQL related issue with this so we are wondering if anyone has some insight that might help. First the stack trace:

2006/10/10 14:28:54 - STAGE_ORDER.0 - linenr 15000
2006/10/10 14:29:02 - ROUTE_ROWS_WITH_ERRORS.0 - linenr 15000
2006/10/10 14:29:10 - Dummy (do nothing).0 - Linenr 15000
2006/10/10 14:29:17 - DIMENSION_ORDER_CAPTURE.0 - linenr 15000
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Unexpected error : be.ibridge.kettle.core.exception.KettleDatabaseExc eption:
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Couldn't get row from result set
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Communications link failure due to underlying exception:
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : ** BEGIN NESTED EXCEPTION **
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : java.io.EOFException
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : STACKTRACE:
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : java.io.EOFException
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934 )
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO. java:2433)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2909)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:798)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1316)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDy namic.java:370)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic. java:360)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at com.mysql.jdbc.ResultSet.next(ResultSet.java:5897)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at be.ibridge.kettle.core.database.Database.getRow(Da tabase.java:2637)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at be.ibridge.kettle.core.database.Database.getRow(Da tabase.java:2619)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at be.ibridge.kettle.trans.step.tableinput.TableInput .processRow(TableInput.java:116)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : at be.ibridge.kettle.trans.step.tableinput.TableInput .run(TableInput.java:276)
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : ** END NESTED EXCEPTION **
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Last packet sent to the server was 367027 ms ago.
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : be.ibridge.kettle.core.exception.KettleDatabaseExc eption:
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Couldn't get row from result set
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) : Communications link failure due to underlying exception:
2006/10/10 14:29:35 - STAGE_ORDER.0 - ERROR (version 2.3.1, build 63 from 2006/09/14 12:04:05 @ sam) :

Java version:
java -version
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_06-b05, mixed mode)

mysql --version
mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (x86_64) using readline 5.0

We are using mysql-connector-java-3.1.13-bin.jar for the jdbc driver. One thought is that it insists on the old org.gjt.mm.mysql.Driver. This is hard-coded in KETL.

This message makes us think that one of the mysql dbs is not being talked to for the whole time we are running the job. But this does not make sense as both show activity for the whole time the job is running:

"Last packet sent to the server was 415404 ms ago."

We have tried altering the connection parameters:

autoReconnect=true

Is short, we are running out of thoughts and wonder if there is a guru that can solve this without us pulling apart the ETL code.

TIA

Ming

[MattCasters]

Communications link failure due to underlying

It's almost 100% certain a communications problem.
It might be networking problem, for example I know that some linux network card drivers choke on high volume traffic. Try to scp a few large files to a nearby system over the same network card.

Another (remote) option is that for some reason the MySQL drops the connection on the server. Search the MySQL bug list for that one.
A few years ago I was batteling with the same problem on Oracle and in that case we really couldn't figure out what it was. Afther an Oracle upgrade we got the correct message from the server: Rollback to small error.

YMMV I guess.

We are using mysql-connector-java-3.1.13-bin.jar for the jdbc driver. One thought is that it insists on the old org.gjt.mm.mysql.Driver. This is hard-coded in KETL.

Just for reference, this is not the KETL forum. Second, we find time and time again that the 3.1. series is the most stable JDBC driver. The 5.0 driver series chokes on far too many issues to be useful for us. We use the "org.gjt.mm.mysql.Driver" class name to be backward compatible in case someone wants to use an old JDBC driver with an old database. (3.x, 4.x versions)
For that reason we keep using this class unitl it is no longer available. It doesn't make a difference though.

autoReconnect=true

That doesn't do you any good as the connection is dropped in the middle of the operation and the operation failes for that reason.

HTH,

Matt

[moon]

Thank you very much for your help.

Actually, I tried to use my laptop to do the whole task in one machine. The same error still showed up.

But when move the tables from mysql to postgre, the transfermation worked successfully. I thought there must be some thing behind mysql.

However, it might not be the key. Previously, the input table and output table are located in different mysql databases. Yesterday, I test transfermation in a same database and it works great, i.e. faster and successful. I am the both mysql databases are OK. Is there any interesting issues related to communication between two mysql databases might related the previous problem.


TIA

Moon

[MattCasters]

Related issues?

http://www.google.com/search?hl=nl&q...gle+zoeken&lr=

Knock yourself out ;-)

Cheers,
Matt