Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Rollback issue in Kettle

  1. #1
    Join Date
    Sep 2005
    Posts
    1,403

    Default Rollback issue in Kettle

    Hi,
    We are using Kettle for Migrating data from Sybase to Oracle.Our source table contains 1000 records and if there is any error in the 501 record, Kettle abruptly stops the transaction and 500 record gets committed.So I am unable to execute the script again due to unique key violation in the arget table.Is there anyway I can rollback the transaction.

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

    Default RE: Rollback issue in Kettle

    How about setting the commit value in the Table Output step to 9999999?

  3. #3
    Join Date
    May 2009
    Posts
    13

    Default

    Sorry to did up an old thread, but I am struggling with this same issue. I am trying to use Pentaho in a case where it is important that either all rows get inserted, or none of them do.

    The previous suggestion of setting the commit size on the table output step does not work for me in Spoon 3.2.0. Is there another way to support rollback functionality in my version of spoon?

    If this functionality is not directly supported, has anyone else tried using custom sql script blocks to start a transaction with the database before the table input step runs?

    Any advice would be much appreciated
    Tony

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

    Default

    Check the option "Make the transformation transactional" in the transformation settings dialog (Misc tab)

  5. #5
    Join Date
    May 2009
    Posts
    13

    Default

    I have created a xls file which contains 6 lines of text with lengths ranging from 5 to 43 characters. I then created a transformation which would insert these lines into a database table with a fixed length of 25. The first four lines should insert correctly, but the fifth one should fail and cause all of the first four to be rolled back. The result should be no change for the database table.

    After setting the transformation to be transactional I saw the following new line at the end of the log file:
    2009/10/29 09:27:22 - test-rollback - All transaction of database connection 'BI' where rolled back at the end of the transformation.
    However, the inserts I expected to be rolled back were still present in the database when the job finished executing.

    I have attached my transformation and related files (they're very simple) to this post. If anyone could take a look at them and tell me what I am doing wrong it would be much appreciated.
    Attached Files Attached Files

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

    Default

    MySQL? Which engine?

  7. #7
    Join Date
    May 2009
    Posts
    13

    Default

    Sorry. It turns out that my testing database was using the MyISAM.

    I changed it to InnoDB, and now both the previously mentioned methods for enabling roll backs work perfectly.

  8. #8
    Join Date
    Mar 2007
    Posts
    9

    Default

    The example provided by Munkii has only a single Insert step and so adjusting the commit size and using an InnoDB works. In a practially usable scripts, there will be more than one Insert step in a transformation. In such cases, Kettle doesn't support transactions. One Insert step might have committed some data while the next Insert step would have failed. If someone has a solution to this transactional problem when multiple Insert or Update steps are involved in a transaction, it would help the entire community, as I can see several community members are having this issue without a proper solution.

  9. #9
    Join Date
    Jun 2007
    Posts
    103

    Default

    Hi Everyone

    I caught this post by Matt Casters at http://www.ibridge.be/?p=93. It doesn't appear that anything has changed since then with regards to transactions. What I'm doing is coding the transactional code within SQL Server stored procedures, bypassing Kettle for this set of work. I'm doing much more than just inserting rows; I'm integrating with a 3rd party SQL Server-based application and calling a number of queries, procedures and functions that need to be wrapped in a transaction in order to guarantee complete processing.

    Having Kettle perform transactions would be quite a lot to ask as cross-platform transaction processing is not a simple thing.

    - Russell

  10. #10
    Join Date
    Mar 2013
    Posts
    1

    Default

    Streaming result set com.mysql.jdbc.RowDataDynamic@166a5207 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.

    i m getting this error.

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.