PDA

View Full Version : Rollback issue in Kettle



kettle_anonymous
03-27-2006, 06:41 AM
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.

MattCasters
03-27-2006, 08:20 PM
How about setting the commit value in the Table Output step to 9999999?

Munkii
10-27-2009, 09:31 PM
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

MattCasters
10-28-2009, 04:51 AM
Check the option "Make the transformation transactional" in the transformation settings dialog (Misc tab)

Munkii
10-28-2009, 04:44 PM
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.

MattCasters
10-28-2009, 06:57 PM
MySQL? Which engine?

Munkii
10-28-2009, 09:17 PM
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.

rameshsr
10-30-2010, 03:33 AM
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.

rlmagidson
11-09-2010, 11:42 AM
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

kalyanm
03-26-2013, 12:50 PM
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.