Communications link failure due to underlying exception

For long running queries on MySQL, you might get this exception:

Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
etc.

The MySQL server parameter "net_write_timeout" defaults to 60 seconds.

I found that by increasing this to a larger value, the exception goes away.
Then we do not have to turn off the result set cursor and we don't need
to read the entire result set into memory by turning off result set streaming (cursor emulation)

This parameter is set in my.ini. I set it to 1800, but probably a
smaller value would suffice.

Please also see the chapter with the nice title "MySQL server has gone away" over here:
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

Another reported solution was: Set the tcpKeepAlive setting in the MySQL connecto (since MySQL Connector/J 5.0.7, please see about JDBC driver versions).

JDBC driver versions

We found several times that v5.x of the MySQL JDBC driver doesn't play along nicely with Kettle.

vanayka found out the same in this thread:  http://forums.pentaho.org/showthread.php?p=184717

Using an alias

There was a change in the usage of getColumnLabel and getColumnName in the JDBC drivers version 4.0 and later.

This has e.g. impact for SQL queries with an alias for the fieldname like:

SELECT column AS aliasName from table

With JDBC drivers version 4.0 and later you will eventually not get the aliasName.

PDI-2905 is adressing this issue, see also http://bugs.mysql.com/bug.php?id=35610
"In Connector/J 5.1 the behaviour was changed, so that instead of allowing only index
number or column label, only index number or column name are accepted."

This bug was fixed by MySQL and lead to a changed JDBC behaviour in this regard.

Character encoding issues

Make sure that you set the correct JDBC options like for example in the case of a Unicode target database:

useUnicode = true
characterEncoding = utf8
characterSetResults = utf8

 Even if you don't have a target Unicode database, it makes sense to set the encodings explicitly in those situations where source and target encodings are different etc.

Exception: Lock wait timeout exceeded; try restarting transaction

This is a MySQL issue and we found the following solution for this:

Change the value in my.ini from "innodb_lock_wait_timeout=2"(default
value) to a higher value, e.g. "innodb_lock_wait_timeout=5".

If you are running in a MySQL cluster:

The innodb_lock_wait_timout variable has no bearing on the NDB tables.

This timeout is administered within the NDBD nodes. You need to
update the config.ini file with a higher value for
TransactionDeadlockDetectionTimeout.

http://dev.mysql.com/doc/mysql-cluster-excerpt/5.1/en/mysql-cluster-ndbd-definition.html#mysql-cluster-param-ndbd-definition-transactiondeadlockdetectiontimeout