Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: Streaming result set is still active. No statements may be issued when any strea....

  1. #1
    Join Date
    Sep 2011
    Posts
    190

    Angry Streaming result set is still active. No statements may be issued when any strea....

    Please see below.

    I sometimes get this error when connecting to MySQL 5.1.52. It occurs intermittantly.

    Can I and should I manage the connection between Kettle 4.1.0-stable and MySQL?

    Also the message 'Last packet sent to the server was 91173 ms ago' surprises me; one-and-a-half minute isn't very long, so what could this be about...?

    I tried adding SQL_NO_CACHE to the TableInput SQL statements. Some of them helped. I also rewrote an order-by SQL clause to a Sort-step, which helped a few times too.

    After living in peace and quiteness, this error returns to haunt me again. Any ideas...?

    Code:
    (snip)
    
    2011/10/11 16:05:12 - Stream lookup particuliere Provider_id.0 - linenr 50000
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Unexpected error : 
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Couldn't get row from result set
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Communications link failure due to underlying exception: 
    
    ** BEGIN NESTED EXCEPTION ** 
    
    java.io.EOFException
    
    STACKTRACE:
    
    java.io.EOFException
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2433)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1316)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:370)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:360)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.ResultSet.next(ResultSet.java:5970)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.core.database.Database.getRow(Database.java:2732)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.core.database.Database.getRow(Database.java:2717)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at java.lang.Thread.run(Thread.java:619)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 
    
    ** END NESTED EXCEPTION **
    
    
    
    Last packet sent to the server was 91173 ms ago.
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.getRow(Database.java:2802)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.getRow(Database.java:2717)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.lang.Thread.run(Thread.java:619)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - Finished reading query, closing connection.
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Unexpected error closing query : org.pentaho.di.core.exception.KettleDatabaseException: 
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Couldn't close query: resultset or prepared statements
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Communications link failure due to underlying exception: 
    
    ** BEGIN NESTED EXCEPTION ** 
    
    java.io.EOFException
    
    STACKTRACE:
    
    java.io.EOFException
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1316)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:370)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:360)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.RowDataDynamic.close(RowDataDynamic.java:150)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.ResultSet.realClose(ResultSet.java:6314)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at com.mysql.jdbc.ResultSet.close(ResultSet.java:749)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.core.database.Database.closeQuery(Database.java:2345)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.closePreviousQuery(TableInput.java:206)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.dispose(TableInput.java:262)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:69)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 	at java.lang.Thread.run(Thread.java:619)
    2011/10/11 16:05:12 - Table input mydb mytable voor restnotas.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 
    
    ** END NESTED EXCEPTION **
    
    
    
    Last packet sent to the server was 91173 ms ago.
    2011/10/11 16:05:12 - Stream lookup particuliere Provider_id.0 - Finished processing (I=0, O=0, R=55519, W=27999, U=0, E=0)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error disconnecting from database:
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : 
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error comitting connection
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Streaming result set com.mysql.jdbc.RowDataDynamic@f346dc is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    2011/10/11 16:05:12 - Add constants dim_bronsysteemCode.0 - Finished processing (I=0, O=0, R=20479, W=20479, U=0, E=0)
    2011/10/11 16:05:12 - Stream lookup dim_soortnotaKey.0 - Finished processing (I=0, O=0, R=20184, W=20181, U=0, E=0)
    2011/10/11 16:05:12 - Stream lookup dim_bronsysteemKey.0 - Finished processing (I=0, O=0, R=20386, W=20382, U=0, E=0)
    2011/10/11 16:05:12 - dwh_fct_gedeclareerdeNotas_mydb.ktr - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Errors detected!
    2011/10/11 16:05:12 - Stream lookup dim_contractKey.0 - Finished processing (I=0, O=0, R=19981, W=19981, U=0, E=0)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error comitting connection
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Streaming result set com.mysql.jdbc.RowDataDynamic@f346dc is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.commit(Database.java:689)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.commit(Database.java:659)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.disconnect(Database.java:550)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableinput.TableInput.dispose(TableInput.java:273)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.step.RunThread.run(RunThread.java:69)
    2011/10/11 16:05:12 - Bron mydb - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.lang.Thread.run(Thread.java:619)
    2011/10/11 16:05:12 - Zonder declaration_id.0 - Finished processing (I=0, O=0, R=23448, W=23448, U=0, E=0)
    (snip)

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hey mbvt,

    I recall this type of symptoms reported for transformations that stalled, and the mysql connection eventually timed out.
    Transformation stalls are usually caused by splitting (or split-copying) a stream, and rejoining/appending later on. Any chance your transformation does something like that?

    If you could post a screenshot of the trans, we might be able to tell.

    Cheers
    Slawo

  3. #3
    Join Date
    Sep 2011
    Posts
    190

    Default

    Thanks! I hadn't thought of that...
    Indeed, I removed one Append step because I'm not really interested in the order of the rows. The jobs is now up and running again.

    Now that I have your attention: is it possible to merge two input streams into a Stream Lookup step w/o the Append step that I'm using now (see attachment; I had to remove sensitive data of a customer)

    TIA!
    Attached Images Attached Images  

  4. #4
    Join Date
    Sep 2011
    Posts
    190

    Default

    Update: when I run this job solo it works, when it's ran in a chain of Transformations from a Job, I'll get the error back again. I replaced the final Append step by a Dummy step, but that doesn't help, so the Append step isn't the culprit...

  5. #5
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi mbvt,

    try replacing all append steps that you don't need for ordering with simple dummy steps and see if it helps.

    Cheers
    Slawo

  6. #6
    Join Date
    Sep 2011
    Posts
    190

    Default

    Thanks.

    I now have no Append steps anymore. Didn't help.

    I changed the order of the Transformations in the Jobs, and now it's running fine.
    Apparently, something of a previous Transformation gets 'stuck' and causes problems...

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

    Default

    Did you make the transformation database transactional? MySQL doesn't support this in combination with cursor emulation (result streaming). Please note the word "emulation" since MySQL doesn't support cursors, certainly not concurrently.

  8. #8
    Join Date
    Sep 2011
    Posts
    190

    Default

    Thanks Matt.

    I checked, no Transformation is set Transactional (so <unique_connections>N</unique_connections> everywhere).

    Do I need to check OFF 'Use Result Streaming Cursor' for all connections, or only the destination connections that are written to?

    PS I checked it OFF for all db connections and promptly ran into an out of heap space error.
    Last edited by mbvt; 10-13-2011 at 04:53 AM.

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

    Default

    Well, it might be a previously undiscovered MySQL JDBC driver problem. Try downgrading the driver to 3.1. (found in PDI < 4.2)

  10. #10
    Join Date
    Sep 2011
    Posts
    190

    Default

    Thanks! I'll give that a try...

  11. #11
    Join Date
    Apr 2008
    Posts
    146

    Default

    We have experienced exactly the same problem described in this thread. We are currently testing our ETL system with the downgraded MySQL JDBC Driver version 3.1. So far the results are positive. I will follow up later in the day with complete results.

  12. #12
    Join Date
    Apr 2008
    Posts
    146

    Default

    We can confirm that downgrading the MySQL JDBC Driver from 5.1 to a 3.x version that was bundled with PDI 4.1.x worked perfectly for us.
    Our back-end MySQL is version 5.1. Our ETL that has evolved from PDI 3.X to 4.1.X and now 4.2.X is now working again.

    I hope that the cause of the problem can be found and fixed by all the parties involved.

    Hat tip to Matt Casters for suggesting use of the old driver. Thanks!

  13. #13
    Join Date
    Jan 2011
    Posts
    6

    Default

    Does downgrading the old driver simply involve swapping the MySQL connector file in /pentaho/design_tools/data-integration/libext/JDBC/ ? my downgraded (PDI 4.1.2) file is called 'mysql-connector-java-3.1.14-bin.jar'. I've copied this file into my 4.2.1 installation and removed the file that came with 4.2.1 and I'm still getting this error.

  14. #14
    Join Date
    Sep 2011
    Posts
    190

    Default

    I still am getting this error too.
    And I'm already (or actually: still) on v.3.1.14.
    I think the solution for me is:
    - Switch off 'Use Result Streaming Cursor' in the MySQL connection
    - Set the 'Commit size' to 0 in the Table Output step as this seems to imply a transaction
    - Make sure that 'Make the transformation database transactional' is switched off in the Transformation Properties.

    This seems to run stable for me now for a while...

    Does anyone know if it makes a difference in using the InnoDB (transactional) or MyISAM (non-transactional) with regard to this error?

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

    Default

    The only important part is : "Disable cursor streaming", the rest is not needed.

  16. #16
    Join Date
    Nov 1999
    Posts
    459

  17. #17
    Join Date
    Feb 2014
    Posts
    22

    Default

    Unchecking the Use Result Streaming Cursor did the trick for me.

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.