Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: Error Handling

  1. #1

    Default Error Handling

    I also have a question on error handling. I have made an error table which is used to store all the rows that causes errors. I don't want the original transformations to stop, just to log the fault rows in the error table.

    I've put the error handling behind the output table. But the only error i get, is java exception error. I want to see the error row.

    any ideas?

  2. #2

    Default

    HI Maskerade,
    If yoy use table output step, make sure that "Ignore errors" option is checked.

    Rgds

    Samatar

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

    Default

    Rather, make another hop from the table output step and define proper error handling on it. (right click: "define error handling...")

  4. #4

    Default

    thank you for your input... i did use the right click and then error handling thing. Only thing is: i don't see the description of the error i want. I see a java exception error. Also the error code is always TP001 or something.

  5. #5
    Join Date
    May 2006
    Posts
    4,882

    Default

    Well.... we can only "forward" what we get as error in Kettle ourselves... and sometimes the database errors are pretty horrible.

    What do you get as error, and what were you expecting?

    Regards,
    Sven

  6. #6

    Default

    I was expecting a constraint violation error, but all i see is the java exception error.

    also the error code is always the same. This is correct?

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

    Default

    We pass the exception along. You do need to pick it up by supplying an "error description" field name.

  8. #8

    Default

    Great, i've got the description now.. i need to look further ...

    but now another "problem": after 1 record is recorded in my error table ALL THE NEXT records are REJECTED... why is that.

    I've attached a screen shot of my log. You can see that there are 109124 records read correctly and after that 1 record is in error (constraint error). But the rest of the records are OK, although they all get rejected: 9226 records.

    help?

    [IMG]file:///C:/DOCUME%7E1/RE3E4%7E1.SMI/LOCALS%7E1/Temp/moz-screenshot.jpg[/IMG][IMG]file:///C:/DOCUME%7E1/RE3E4%7E1.SMI/LOCALS%7E1/Temp/moz-screenshot-1.jpg[/IMG]
    Attached Images Attached Images  

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

    Default

    That is interesting. What version are you using by the way?

  10. #10

    Default

    I'm using 2.5.0...

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

    Default

    And the database? Postgres by any chance? Don't be afraid to tell us something about the actual problem too. :-)

  12. #12

    Default



    Okay, i'll try to tell the whole "tale":

    i'm using spoon 2.5.0 on a postgres database version 8.1. In the database we have 4 schema's: extraction, staging, public and log. In the extraction schema we only have tables which contain data of back office systems. after extraction we put the data in the staging fase, which is rearranging the data so that our database can use it.
    In our database we have constraints, i.e. a person identification number is linked to an id in the person table. When trying a person id into a table, but that id doesn't exists in the person table, i expect a single row in error state. The rest should go on.

    The problem now is, that i do get the correct error, but all the rows processed after the error, are rejected (see the attachment).

    I hope you have more information now.

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

    Default

    Postgres is an annoying database with respect to error handling. I think that by default it requires a rollback after any error occurred. This is obviously a spoiler for the exception handling.
    I'll have a quick look to see if we can change this behavior.

    Matt

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

    Default

    Sorry. it's a PostgreSQL problem. From what I read on the mailing list, they are not very interested in fixing it either. "Too complex" was what I read.
    There is a setting in psql called "ON_ERROR_ROLLBACK" that you can set to 'on' that would help us out, but it seems we can't use it over JDBC.


  15. #15

    Default

    how come that when i run the transformation in Kettle, i do see the error.. and Kettle stops on that error.. It shows the row and all... i just want that row to log and then to continue.....

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

    Default

    The Postgres idea of error handling is that if you have an error, any error, the transaction has to be rolled back.
    If we have inserted rows and rejected rows in the same transaction/batch of records, according to the Postgres rules we have to roll back the transaction including the inserted rows.
    Note that these are Postgres rules. To my knowledge it doesn't apply to any other database.

    Matt

  17. #17

    Default

    matt,

    i've been testing that, but i do have rows in my table. my transaction is in error state on row #90001. I do have 90000 rows in my output table. Nothing is rollbacked.

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

    Default

    I'm specifically talking about the error handling. I have a test-case that drops rows on PostgreSQL.
    I investigated it and it's a Postgres problem. And I don't have a workaround for it. It's a sad state of affairs.
    Take care,
    Matt

  19. #19

    Default

    Matt,

    thank you very much for your effort.


    regards,

    Rob

  20. #20
    Join Date
    Nov 1999
    Posts
    459

    Default

    Just a link to the Jira case "Table Output : Error handling on Postgres is flawed" http://jira.pentaho.org/browse/PDI-366

  21. #21
    Join Date
    Jun 2008
    Posts
    3

    Default what about sqlite

    Dears ,
    could it be that the same problem is affecting sqlite? (on pdi 3.0.4/xp)

    i have configured error handling, on the table output step.
    i have the "ignore insert error" flag set.
    but when a unique index is violated all the rows after the erroneous one go to the error output and the transformation stops on error.

    What i am expecting is all the other rows to go to the DB and the transformation to succeed. is there a way to achieve that?

    Note : this happens regardless of the "use batch for inserts option"
    extract from error output :
    Error setting value #1 [HLR String] on prepared statement (String)
    java.sql.SQLException: statement is not executing
    statement is not executing


    and i get the following error message :

    008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) :
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.insertFinished(Database.java:1427)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:455)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:536)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Caused by: java.sql.SQLException: statement is not executing
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.Stmt.checkOpen(Stmt.java:41)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.PrepStmt.clearParameters(PrepStmt.java:52)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.PrepStmt.close(PrepStmt.java:47)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.insertFinished(Database.java:1402)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : ... 2 more
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : org.pentaho.di.core.exception.KettleDatabaseException:
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Unable to commit connection after having inserted rows.
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : statement is not executing
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) :
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.insertFinished(Database.java:1427)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:455)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:536)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Caused by: java.sql.SQLException: statement is not executing
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.Stmt.checkOpen(Stmt.java:41)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.PrepStmt.clearParameters(PrepStmt.java:52)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.PrepStmt.close(PrepStmt.java:47)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.insertFinished(Database.java:1402)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : ... 2 more
    2008/08/06 03:52:19 - Error_SS_rec.0 - Finished processing (I=0, O=7, R=6, W=5, U=0, E=0)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Unexpected error rolling back the database connection.
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : org.pentaho.di.core.exception.KettleDatabaseException:
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Error performing rollback on connection
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : database in auto-commit mode
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) :
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.rollback(Database.java:651)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.rollback(Database.java:625)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:505)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.run(TableOutput.java:536)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : Caused by: java.sql.SQLException: database in auto-commit mode
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.sqlite.Conn.rollback(Conn.java:171)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : at org.pentaho.di.core.database.Database.rollback(Database.java:640)
    2008/08/06 03:52:19 - Table output.0 - ERROR (version 3.0.4, build 53 from 2008/06/24 11:14:00) : ... 3 more
    Last edited by zokt; 08-05-2008 at 10:23 PM.

  22. #22
    Join Date
    May 2006
    Posts
    4,882

    Default

    similar yes... maybe create a new JIRA for it linking to the one jens mentioned. "sql statment is not executing" seems to be when a problem with the statement state (if I look in the source code).

    Regards,
    Sven

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.