Hitachi Vantara Pentaho Community Forums
Results 1 to 33 of 33

Thread: 3.0.0RC1: Call getNextException to see the cause.

  1. #1

    Exclamation 3.0.0RC1: Call getNextException to see the cause.

    I created a transform and when I try to run it I am seeing this error:

    2007/10/07 20:31:28 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Unexpected batch update error committing the database connection: org.pentaho.di.core.exception.KettleDatabaseBatchException:

    2007/10/07 20:31:28 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Error updating batch

    2007/10/07 20:31:28 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Batch entry 0 INSERT INTO mytable (name, id) VALUES ( SMITH, JOHN A , 588439455) was aborted. Call getNextException to see the cause.
    I set logging to row logging but still no help no further information is displayed. How can I see this getNextException?

    It looks like maybe it isn't quoting a comma in the INSERT name field. If that is true how can I get it to quote embedded comma's?

    Gerry
    Last edited by greno; 10-09-2007 at 04:38 PM.

  2. #2

    Default

    I found an option in the db conn under the Advanced tab that forces quotes around all identifiers so I tried this. It does quote the identifiers but it does not quote the VALUES which is where I think the problem lies. Is there any way to get the VALUES quoted?

    Gerry

  3. #3

    Default

    I just tried another of our databases and it too is getting this same problem with INSERT VALUES that are not quoted:

    INSERT ... VALUES ( , , , , , , , , , 0001-01-01 00:00:00.000000 -05:00:00, ...) Call getNextException to see the cause
    And I do not see any way to call getNextException to find out the exact error. This one is probably syntax error because all the strings with SPACES are unquoted and so it just looks like a bunch of commas strung together in the INSERT.

    How can I get all the VALUES quoted? I've been searching the forum and looking around the menues but I do not see any way to do this. This is a big showstopper.

    Gerry

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

    Default

    Quoting is not required for table output as parameters are used to pass data into a prepared statement. What you see as error is not what is being executed.

    Switch of batching mode (it sometimes eats the real error messages in the JDBC driver), run again and attach the resulting stack trace.

    Regards,
    Sven

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

    Default

    OR use error handling to send the offending rows to another step and preview on that.
    If you include the error text, you'll see what the problem was on a row-level.

  6. #6

    Default

    Ok, I added error handling and reran the transform and this time no exception. But the output is not complete.
    Last run the commit size was set to 1000 but this run I set it to 100.

    Code:
    Stepname    Copynr    Read    Written    Input    Output    Updated    Rejected    Errors    Active    Time    Speed (r/s)    input/output
    Table input    0    0    500    500    0    0    0    0    Finished    0.3    1577.2    -
    Select values    0    500    500    0    0    0    0    0    Finished    3.4    147.6    -
    Table output    0    500    384    0    384    0    0    0    Finished    3.4    224.1    -
    Text file output    0    0    0    0    0    0    0    0    Finished    3.4    0.0    -
    Here is what is in the actual table:
    =# select count(*) from mytable;
    count
    -------
    300
    (1 row)

    So 500 rows were processed but only 384 show as being output and only 300 actually ended up in the table. There were no errors and nothing is in the error handling file.

    ????

    Gerry

    And can someone tell me how to get a fixed font output with this forum. I've tried all the tags and the FixedSys font so far without success.
    Last edited by greno; 10-08-2007 at 08:35 AM.

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

    Default

    Hi Gerry,

    I tried a few things with Table Output and error handling, but I couldn't find a problem with it.
    Here is my test-case: Table output error handling.ktr

    The table "error_handling_test" on MySQL is created with:

    Code:
    create table error_handling_test(a int, primary key(a));
    Feel free to create a JIRA case for this or to post your transformation so that we can make sure this issue is fixed in RC2.

    And can someone tell me how to get a fixed font output with this forum. I've tried all the tags and the FixedSys font so far without success.
    The code tags you used do just that.

    Matt

  8. #8

    Default

    Matt,
    I think the error handling was fine. On my second Run there were no errors. My main concern now is that my source data has 500 rows and only 384 of these are showing as output to the target table and yet when I check the target table only 300 are actually in the table. This is not good. See the CODE section in my last post.


    Gerry

  9. #9

    Default

    Ok, I finally have all my field manipulations working in the transform and every step is now reading and writing 500 records (my test db size) EXCEPT for TABLE OUTPUT. It reads 500 records and writes 0 and there are no errors in the log and nothing is in the error file and nothing gets put into the db.

    2007/10/08 15:43:17 - Select values.0 - Finished processing (I=0, O=0, R=500, W=500, U=0, E=0)
    2007/10/08 15:43:17 - Table output.0 - Signaling 'output done' to 0 output rowsets.
    2007/10/08 15:43:17 - PostgreSQL_230_postgres_grpref - Connection to database closed!
    2007/10/08 15:43:17 - Table output.0 - Finished processing (I=0, O=0, R=500, W=0, U=0, E=0)
    Code:
    Stepname    Copynr    Read    Written    Input    Output    Updated    Rejected    Errors    Active    Time    Speed (r/s)    input/output
    Table input    0    0    500    500    0    0    0    0    Finished    1.0    519.7    -
    Add constants    0    500    500    0    0    0    0    0    Finished    9.3    53.9    -
    Modified Java Script Value    0    500    500    0    0    0    0    0    Finished    9.3    53.8    -
    Select values    0    500    500    0    0    0    0    0    Finished    11.2    44.7    -
    Table output    0    500    0    0    0    0    0    0    Finished    11.2    44.6    -
    Text file output    0    0    0    0    0    0    0    0    Finished    11.2    0.0    -

    ????

    Gerry

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

    Default

    Gerry, it's quite a busy time for us. Too busy to be chasing ghosts :-) Please try to understand this.

    Let me ask you again: feel free to create a JIRA case for this or to post your transformation so that we can make sure this issue is fixed in RC2.

    Thanks in advance.

    All the best,

    Matt

  11. #11

    Default

    Matt,
    Just thought you might recognize the issue. I've been trying all day to create a small reproducible testcase for this without any luck so far. It's only when I hook up one of our huge databases that I see the problem. I can't really post this. I'll keep working at this and see if I can get something smaller that will exhibit the problem.

    Gerry

  12. #12

    Default

    I just noticed this in the log:
    Code:
    2007/10/08 20:54:02 - Modified Java Script Value.0 - Value #7 -  [city] is used in the script.
    2007/10/08 20:54:02 - Modified Java Script Value.0 - Value #8 -  [state] is used in the script.
    2007/10/08 20:54:02 - Modified Java Script Value.0 - Value #9 -  [zip] is used in the script.
    2007/10/08 20:54:02 - Modified Java Script Value.0 - Value #30 -  [st] is used in the script.
    The only problem is that I do not use [st] anywhere in the script and have not ever used it in the script. The other three are used. So somehow the JS plugin is confused about the identifiers being used.

    Gerry

  13. #13

    Default

    Matt,
    After running this transform a zillion times, I finally caught something:

    2007/10/09 15:44:51 - Table output.0 - Prepared statement : INSERT INTO mytable (...huge bunch of fields...) VALUES ( ... huge bunch of values... , 33756-5146, 0001-01-01 00:00:00.000000 -05:00:00 BC, 9.999999999999998E10, 806.43, 0.0, function toString() {/*
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : java.lang.String toString()
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : java.lang.String toString(boolean)
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : */}
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : , function toString() {/*
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : java.lang.String toString()
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : java.lang.String toString(boolean)
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : */}
    2007/10/09 15:44:51 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : , 260250.0, N , ) was aborted. Call getNextException to see the cause.
    It is actually trying to insert some java source code into one of my fields!!!

    Gerry

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

    Default

    Too weird. Worse is that the code is not from the PDI project. (and it's not Java either, probably JavaScript or something like that)
    I'm actually quite sure that we print question marks, not the actual values in detailed logging:

    Prepared statement : INSERT INTO mytable (...huge bunch of fields...) VALUES ( ...bunch of ?,?,?... );
    NOT the actual values.

    In short: don't fabricate logs :-)

    Matt

  15. #15

    Default

    Matt,
    Come on now. Do you think I would go to all the trouble to fabricate this? Yes, I removed proprietary information in the form of the field names and most values but this is exactly what is in the logs as far as the java source part. And this is why the Table Output fails to write out rows with this transform. As I said it is only with the real big db's that I see this. Array overrun, stray pointer???

    Gerry

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

    Default

    Come on now. Do you think I would go to all the trouble to fabricate this?
    Of-course not. I was just trying to figure out where the printed text would come from in PDI and I couldn't find the source. As such, PDI doesn't print the row data, it's the JDBC error message we're seeing in the log.

    Array overrun, stray pointer???
    Actually, it's an interesting idea. Except, those are not really possible in a well behaving Java VM. (you're not running on GCJ or any other experimental JRE are you? "java -version" will tell you.)

    With all the issues you told me, I seriously suspect a JDBC driver or database issue.
    Since the JDBC driver is written in Java too, I actually would bet on a database bug more than anything else.
    Either that or a database issue triggered by an error in the JDBC driver.

    That's my gut feeling anyway.

    Matt

  17. #17

    Default

    Here is the java version on the database server:
    java version "1.5.0"
    gij (GNU libgcj) version 4.1.2 20070502 (Red Hat 4.1.2-12)

    and here is the java version on my workstation:
    java version "1.6.0_02"

    Kettle is running on my workstation.

    Gerry

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

    Default

    I'm sure 1.6.0_02 is fine but there have been some "reports" of "issues" with early 1.6.0 releases.
    If you can, upgrade to _03.

    As for GCJ. Well, just don't run anything important on it just yet. If we have issues it's more often than not with GCJ being installed standard on Linux boxes. Since it's on the server, you should be fine though.

    Matt

  19. #19

    Default

    Ok, I upgraded to java 1.6.0_03 but I still see the same errors as before.

    Code:
    Stepname                        Copynr  Read    Written Input   Output  Updated Rejected        Errors  Active  Time    Speed (r/s)     input/output
    Table input                     0       0       638       639     0       0       0       0       Stopped       3.2       197.1       -
    Add constants                   0       638     638       0       0       0       0       0       Stopped       3.2       197.0       -
    Modified Java Script Value      0       441     441       0       0       0       0       0       Stopped       3.2       136.6       -
    Select values                   0       409     408       0       0       0       0       0       Stopped       3.2       126.9       -
    Table output                    0       100     0         0       99      0       0       1       Stopped       3.2       31.4       -
    Text file output                0       0       0         0       0       0       0       0       Stopped       3.2       0.0
    The Table Output stats even look weird. How can it have Output of 99 and Written of 0?

    log attached.


    Gerry
    Attached Files Attached Files
    Last edited by greno; 10-09-2007 at 08:37 PM.

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

    Default

    The Table Output stats even look weird. How can it have Output of 99 and Written of 0?
    Because of the possibility of error handling we can only send rows to the next step IF we are sure the data landed correctly in the database.
    In your case that was not the case so no rows got written.

    Matt

  21. #21

    Default

    What's crazy about this is that sometimes I'll run the transform and all the rows will make it through each step EXCEPT for Table Output.

    Code:
    Stepname                        Copynr  Read    Written Input   Output  Updated Rejected        Errors  Active  Time    Speed (r/s)     input/output
    Table input                     0       0       800     800     0       0       0       0       Stopped 0.6     1420.9  -
    Add constants                   0       800     800     0       0       0       0       0       Stopped 0.9     851.0   -
    Modified Java Script Value      0       800     800     0       0       0       0       0       Stopped 1.5     542.3   -
    Select values                   0       800     800     0       0       0       0       0       Stopped 1.5     538.3   -
    Table output                    0       100     0       0       99      0       0       1       Stopped 1.5     67.5    -
    Text file output                0       0       0       0       0       0       0       0       Stopped 1.5     0.0     -
    I'm still see the issue of the Java Source showing up in the INSERT line. Should I open a JIRA case on this?

    Gerry

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

    Default

    If you can recreate the problem consistently, replace the table output with a text output step and see whether you still see "java source" in there.

    Regards,
    Sven

  23. #23

    Default

    Sven,
    I just reran the transform using Text File Output and yes, the java source code is present in the output:

    snippet from the text file:

    Code:
    33556-2620;0001/01/01 00:00:00.000; 99999999999.99998;41.45; 0.0;function toString() {/*
    java.lang.String toString()
    java.lang.String toString(boolean)
    */}
    ;function toString() {/*
    java.lang.String toString()
    java.lang.String toString(boolean)
    */}
    ; 7000.0;N  ;

    Gerry

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

    Default

    Which proves it's not in table output... remove steps going from right to left (keeping the text output of course) and you will find out which step is responsible... my hunch is the javascript step

    Regards,
    Sven

  25. #25

    Default

    Ok, yes it is the Javascript step because when I modify a couple of the statements the java source changes slightly.

    function toString() { [native code for Number.toString, arity=1] }
    ;function toString() { [native code for Number.toString, arity=1] }

    Here is what I'm doing in the Javascript:
    I'm converting an incoming Number to a String and I've tried it several ways all of which still put java source in output:

    var js_field20 = (field20.getInteger()).toString;
    var js_field21 = (field21.getInteger()).toString;
    or
    var js_field20 = field20.toString;
    var js_field21 = field21.toString;

    Gerry

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

    Default

    Why do you use toString with ()? With compatibility on use the first 2 lines in your post (but with ()).

    Regards,
    Sven

  27. #27

    Default

    Ok, I changed the JS and it got rid of the java source. Now I get all 800 rows (my LIMIT statement value) appearing in the Text File Output but if I reconnect the Table Output it shows a batch error:

    2007/10/10 13:24:43 - Table input.0 - Finished processing (I=800, O=0, R=0, W=800, U=0, E=0)
    2007/10/10 13:24:43 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Because of an error, this step can't continue:
    2007/10/10 13:24:43 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Error batch inserting rows into table [mytable]
    2007/10/10 13:24:43 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) :
    2007/10/10 13:24:43 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Error updating batch

    And every time I run the transform the read, write and output numbers are different.

    Gerry

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

    Default

    Switch batch off and rerun.

    Regards,
    Sven

  29. #29

    Default

    Sven,
    With batch mode OFF:

    2007/10/10 13:54:05 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Because of an error, this step can't continue:
    2007/10/10 13:54:05 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Error inserting row into table [mytable] with values: [Ljava.lang.Object;@a4effe
    2007/10/10 13:54:05 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) :
    2007/10/10 13:54:05 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Error inserting row
    2007/10/10 13:54:05 - Table output.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : ERROR: value too long for type character varying(5)

    When I check the Text File Output from just before TABLE OUTPUT all 800 rows are present and nowhere do I find any text related to java.lang.Object.

    Gerry

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

    Default

    Yeah, I noticed that as well. Printing the correct values on errors will be fixed in RC2. (revision 5369 fixes it)

    Matt

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

    Default

    So there's at least one row in your output in which you have a field which is longer than the column you're trying to save it in. The Object reference is an error in displaying the error message, which Matt currently fixed.

    Regards,
    Sven

  32. #32

    Default

    Yeah, I kinda figured something was wrong in the data after seeing that error and I've been chasing it down for the past two hours. I finally found it. It was again in the javascript where the conversion from numeric to string caused the result to be one char over the maximum field length. A meaningful error message would have gone a long way to helping solve this. Hopefully RC2 will have this.

    Also, is there any mode that I can set that will perform length checking on all fields? This would be great for making one run with a new set of data for a sanity check and then switch it off for regular processing.

    Gerry

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

    Default

    There is no such mode. Personally I believe in the principle of stopping on an error as soon as possible.
    However, as we mentioned earlier, it is possible to take the lines where there is an error aside using error handling.

    We will show you the row of data which the error occurs. However, to get the database column name for example, the JDBC drivers would have to cooperate. Which they don't. You can't believe some of the error messages we're seeing from Oracle, PGSQL, SQL Server, for things that should be very easy to report on.

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.