Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: MySQL connection settings at java level

  1. #1

    Post MySQL connection settings at java level

    Where we can set "Autoreconnect ='true' " for MySQL connection at java level to avoid the connection timeout error(Idle connection timeout if we keep kettle idle for 8hours).

  2. #2
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi sandeep,

    according to the mysql-reference, you just should add the property "autoReconnect"
    to the JDBC-Url:

    http://dev.mysql.com/doc/refman/5.0/...roperties.html

    So it would be something like this:
    jdbc:mysql://[host]:[port]/[database]?autoReconnect=true

    HTH,

    Tom
    Last edited by TomS; 12-24-2009 at 08:18 AM. Reason: removed smiley ;)

  3. #3

    Default

    Hi Tom,

    Thanks for your answer.

    The JDBC Url setting which you r suggesting needs to be done at mysql level or Kettle level.

    We are using Kettle-3.2.0 version and we didn't find any such JDBC Url.
    We tried by setting autoReconnect=true while connecting to repostory through Kettle. But it didn't work.
    Please see the picture,which shows how we have set autoRconnect=true.

    Please let me know where exactly we need to set this property.
    Attached Images Attached Images  

  4. #4
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi Sandeep,

    already tried to enter them into the "options" section?
    Name:  pdi_db_connection_options.jpg
Views: 1158
Size:  21.4 KB

    HTH,

    Tom

  5. #5

    Default

    Hi Tom,

    Tried this option also. No success.
    If we are keeping Kettle screen idle for more than 8hrs we are getting below error if we try open anything.

    ---------------------------------------------------------
    An error occurred executing SQL:
    SELECT ID_TRANSFORMATION FROM R_TRANSFORMATION WHERE NAME = ? AND ID_DIRECTORY = ?
    Communications link failure due to underlying exception:
    ** BEGIN NESTED EXCEPTION **
    java.net.SocketException
    MESSAGE: Broken pipe
    STACKTRACE:
    java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2739)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2650)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1581)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1137)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1231)
    at org.pentaho.di.core.database.Database.openQuery(Database.java:1750)
    at org.pentaho.di.core.database.Database.openQuery(Database.java:1713)
    at org.pentaho.di.core.database.Database.openQuery(Database.java:1708)
    at org.pentaho.di.core.database.Database.getOneRow(Database.java:3476)
    at org.pentaho.di.repository.Repository.getIDWithValue(Repository.java:1074)
    at org.pentaho.di.repository.Repository.getTransformationID(Repository.java:933)
    at org.pentaho.di.ui.spoon.job.JobGraph.openTransformation(JobGraph.java:1710)
    at org.pentaho.di.ui.spoon.job.JobGraph.openTransformation(JobGraph.java:1208)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:616)
    at org.pentaho.xul.EventHandler.handleMenuEvent(EventHandler.java:42)
    at org.pentaho.xul.swt.menu.PopupMenu.handleMenuEvent(PopupMenu.java:40)
    at org.pentaho.xul.swt.menu.MenuChoice.handleMenuEvent(MenuChoice.java:77)
    at org.pentaho.xul.swt.menu.MenuChoice.handleEvent(MenuChoice.java:73)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1007)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:6116)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:425)

    ** END NESTED EXCEPTION **

    Last packet sent to the server was 1 ms ago.
    ----------------------------------------------------------

    Please suggest some solution.

  6. #6
    Join Date
    May 2009
    Posts
    3

    Default

    According to the MySQL connection it will still report and exception on the first query on the stale connection. It will the reconnect on a subsequent query. This is not very useful for this problem. Has anyone been able to work around this other than just setting larger timeouts in mysql? It seems to me the kitchen should close connections to the database between jobs to avoid this problem.

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Think about it for a second. It is impossible to recover from time-out exceptions thrown by the server. You can't even be sure that the last commit you did was successful or not. It might be fine for read-only connections but in the general case, re-connecting is not a safe option. The only option IMO is to increase the time-out and make the exception go away in the first place.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.