Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Error comitting connection - but only "sometimes"

  1. #1

    Default Error comitting connection - but only "sometimes"

    Kettle version 4.1
    MySQL version 5.1

    I am using Kettle 4.1 and have Jobs and Transformations structured like this:

    JOB_MASTER
    SUB JOB A
    transformation aa1
    SUB JOB B
    transformation bb0 -> green arrow to bb1
    transformation bb1 -> green arrow to bb2
    transformation bb2 -> green arrow to job success

    transformation bb2 is occasionally (not consistently) throwing errors like this:

    Code:
    
    ERROR 22-11 20:59:51,316 - <transformation name> - org.pentaho.di.core.exception.KettleDatabaseException: 
    Error committing connection : <<tranformation bb2 db connection name>>
    The transformations are fairly simply, they are a select from one db (using tablet input) - insert/update block to a different db.
    I have renamed the DB connections in each transformation to ensure the names were unique.
    The transformation DB connections are set to be transactional with results streaming unchecked.

    Any thoughts?

    Any help is much appreciated

  2. #2

    Default

    UPDATE:

    I added a 3 second wait between the transformations.
    This appears to prevent the issue, although I dont know WHY, so that has me worried.

    Given
    Trans 1 [green arrow ->] Trans 2

    Shouldn't Trans 2 wait until Trans 1 is totally complete (including full transaction commit) before it runs?

    The wait eliminating my issue makes me think there is something about Jobs with multiple transformations and how the DB connections commit that I am failing to understand.

  3. #3

    Default

    I had the similar problem with MSSQL but couldnt find a proper solution to fix it. there are 2 temporary fixes that i found to fix this

    1) Add Wait step between the transformations
    2) Uncheck the "Make the transformation database transactional" in the database connections in those transformations.

  4. #4
    Join Date
    Jan 2011
    Posts
    7

    Default

    Is there a jira case for this problem?

  5. #5
    DEinspanjer Guest

    Default

    This problem surfaced at some version after PDI 3.2.

    I was running into a similar issue when I upgraded an ETL server from 3.2 to 4.3 and had a job that executed two transformations that both used the same database connection information.
    After a bunch of debugging, I discovered that the issue is actually that there is a potential race condition where the connection of the previous trans may not be fully closed down before the second transformation tries to open it again.

    I opened a support case on July 18th with Pentaho but after going back and forth with them a while to help them reproduce it, they told me that they would have the engineers look into it and get back to me. That last contact was on July 24th.

    I'd typically go and try to fix it myself at that point, but as you discovered, putting a wait step is a valid workaround, so I haven't attempted a patch.

  6. #6
    Join Date
    Jan 2011
    Posts
    7

    Default

    The Workaround using Wait steps works for me as well, so far...

    I just thought that, as this is a bug in one of Kettle's core features, Pentaho might be interested.

    I agree on the race condition issue. The Problem is that the second transformation doesn't open a new connection at all if the previous transformation didn't close its connection yet. Respectively that the first transformation finishes before the connection is closed. It took me some time to figure this out respectively to connect my problem to this one. I use Postgres the corresponding Error Message is org.postgresql.util.PSQLException: An I/O error occured while sending to the backend. So I first took a journey through my Postgres Log-Files and my Hardware Settings.

    My Kettle Log looks like this:
    Transformation1 - Finished
    Transformation2 - Starting
    Transformation1 - All Connections where commited
    Transformation2 - DB Connection fail (Connection closed by Transformation1)

    My Database Log implies that both transformations use the same connection.

    If I have the time I'll file a Jira case later.

    Thanks for the reply.
    Cheers

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

    Default

    With vanilla Kettle it seems not likely that a closed connection is still open.
    However with connection pooling (turn it off!!), JNDI, web containers and what not you never know.
    And then there are of-course databases out there that "take some time" to commit data (Vectorwise!!)

    So it all depends very much on the situation and therefor... JIRA case with details, version numbers and a reproduction case is very important.

  8. #8
    Join Date
    Jan 2011
    Posts
    7

    Default

    I opened a case now http://jira.pentaho.com/browse/PDI-8298

    I hope the Information is sufficient

Tags for this Thread

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.