------------------------------------------------------------------------- Error details --------------------------------------------------------------------------
Component [DriverHistory:DRIVER_HISTORY] finished with status ERROR. (Out0: 30758 recs)
SQLException when reading resultSet
Communications link failure
The last packet successfully received from the server was 0 milliseconds ago. The last packet sent successfully to the server was 1,009,296 milliseconds ago.
Can not read response from server. Expected to read 58 bytes, read 36 bytes before connection was unexpectedly lost.
------------------------------------------------------------------------------------------------------------------------------------------------------------------
This happens after a while of running my graph which, overall, deals with a couple of hundred thousand records.
"DriverHistory" is a DBInputTable node.
Does anyone know about this or have any insight into why it might be happening?
-
Hi,
The issue is probably caused by the target database connection interruption (most probably a MySQL instance (1, 2)). Here are some hints how to possibly overcome this issue.- 1. The DBInputTable component has a default value of the Fetch size property set to 20 as you are reading hundred of thousands records, you should try to increase this number significantly. The database may silently kill the connection due to some timeout and this might help to read all the records in time.
2. There is a attribute that may renew your connection after it is killed by the database. You can use it by adding the following to your database connection string (URL): autoReconnect=true. So the resulting connection string will look like this:
This example is for MySQL database as this issue seems to be related to it.jdbc:mysql://hostname:3306/database?autoReconnect=true
3. If your project is a CloverETL Server project, you can also try to configure a JNDI connection pool to manage the database connections. Here is a description of steps necessary to set the JNDI up: https://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html#JDBC_Data_Sources See the list of properties of the pool and focus on those that could help you kill the connections, especially (but not only) these: maxWait, validationQuery, validationQueryTimeout, removeAbandoned, removeAbandonedTimeout and so on. Think carefully about what values you need, how many connections can be opened at the same time etc. These settings will have serious impact on the overall performance.
JNDI resource can be set in server.xml of your Tomcat. Just add a new Resource there and restart the Tomcat. The values mentioned below are just example values, you have to try what values suit you best.<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<Resource name="jdbc/myConnection"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/yourDatabaseName"
username="clover" password="clover" maxActive="50"
maxIdle="15" maxWait="600000"/>
</Context>
You need to edit the password, username and URL parameters to fit your database.
Then, in your graphs, you create a new DB connection filling nothing else but JNDI property with this: java:comp/env/jdbc/myConnection
In case the above doesn't help, please provide me with the following.
-
1. Full console output of your graph.
2. Version of your Designer and CloverETL Server (if you have one).
3. Vendor and version of your database.
4. Your graph and the connection (if it's externalized). Feel free to remove username and password.
5. Does this happen at every graph run or just occasionally?
Hope this helps.
Please sign in to leave a comment.
Comments 1