Debugging methods / Couldn't get row from result set ?

[dyerrington]

A few months ago, I created a transformation that worked in our development environment. When I run the same thing in production, it pukes. It could be that my connection is getting killed after 10 hours of processing or maybe some part of the XML I am fetching from the database is to blame.. if it's the latter, I haven't quite figured out how to effectively debug and capture the source data that is causing the problem.

I've only ran my transformation once since connecting to a new set of DB hosts but here is what the logs tell me:

2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : Unexpected error : 
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : org.pentaho.di.core.exception.KettleDatabaseException: 
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : Couldn't get row from result set
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ORA-01555: snapshot too old: rollback segment number  with name "" too small
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ORA-22924: snapshot too old
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : 
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : 
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.core.database.Database.getRow(Database.java:2861)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.core.database.Database.getRow(Database.java:2776)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:137)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:343)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : Caused by: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number  with name "" too small
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ORA-22924: snapshot too old
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : 
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:930)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4C8TTILob.read(T4C8TTILob.java:205)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.jdbc.driver.T4CConnection.getBytes(T4CConnection.java:1892)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.sql.BLOB.getBytes(BLOB.java:288)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at oracle.sql.BLOB.getBytes(BLOB.java:192)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     at org.pentaho.di.core.database.Database.getRow(Database.java:2820)
2009/01/24 04:03:26 - Table input.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) :     ... 4 more
2009/01/24 04:03:26 - Table input.0 - Finished reading query, closing connection.
2009/01/24 04:03:26 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=232908, W=465816, U=465816, E=0)
2009/01/24 04:03:26 - Select values Track.0 - Finished processing (I=0, O=0, R=3006636, W=3006636, U=3006636, E=0)
2009/01/24 04:03:26 - Select values Album.0 - Finished processing (I=0, O=0, R=232908, W=232908, U=232908, E=0)
2009/01/24 04:03:26 - Get ALBUM TRACK data from XML.0 - Finished processing (I=3006636, O=0, R=232908, W=3006636, U=3006636, E=0)
2009/01/24 04:03:26 - Get ALBUM data from XML.0 - Finished processing (I=232908, O=0, R=232908, W=232908, U=232908, E=0)
2009/01/24 04:03:26 - Table input.0 - Finished processing (I=232909, O=0, R=0, W=232908, U=232908, E=1)
2009/01/24 04:03:26 - Oracle ALBUM_PACKET -> MySQL ALBUM_PACKET - Transformation detected one or more steps with errors.
2009/01/24 04:03:26 - Oracle ALBUM_PACKET -> MySQL ALBUM_PACKET - Transformation is killing the other steps!
2009/01/24 04:03:26 - Table output - ALBUM_PACKET.0 - Finished processing (I=0, O=232908, R=232908, W=232655, U=232655, E=0)
2009/01/24 04:03:26 - Table output ALBUM_PACKET_TRACK.0 - Finished processing (I=0, O=3006570, R=3006570, W=3006503, U=3006503, E=0)
2009/01/24 04:03:26 - Spoon - The transformation has finished!!

Ultimately, I would like to learn how to debug data, from within spoon, that may cause problems in transformations.

Thanks in advance for any advice or links.

[sboden]

Nothing to do with PDI... talk to your DBA, your undo space is too small probably for your query

This is nothing you can debug from within PDI, but if you show the ORA-error to your DBA he should realize the problem.

Regards,
Sven

[dyerrington]

Thanks for the reply sboden. I am still interested in learning more about debugging. I will take some time to re-read the docs.

[dyerrington]

After hearing back from my DBA team, they suggested that I break up the select query into batches. They supplied me with a query that returns the starting and ending points for each chunk of 10,000 rows. So the result looks like this:

Start ID, End ID, Batch Size
-----------------------------------------------
50116, 86025, 10000
87278, 980231, 10000
etc.


If it's possible, could I run each of these rows to an existing transformation which will substitute each of the MIN / MAX variables in a table input? Can I do this within one transformation?

Thanks for the help so far!

[sboden]

2 table inputs... 1 running your "start/end" point calculation, hopping to another table input reading from its input hop and doing something as

select  your query3
where startpoint >= ? and endpoint < ?

If you would only pass start and end point from the first table input.

Regards,
Sven

[dyerrington]

Yes that's the query. Using a "BETWEEN ? AND ?". I have the "insert data from.." selecting the table input with the query that provides the MIN and MAX. Also have lazy conversion and "exec for each row" checked as well.

Tried running the transform a few different ways but this error seems relevant:

2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException: 
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : offending row : [MIN_ALBUM_ID BigNumber], [MAX_ALBUM_ID BigNumber], [BATCH_SIZE BigNumber]
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : 
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : Error setting value #3 [BATCH_SIZE BigNumber] on prepared statement (BigNumber)
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : java.sql.SQLException: Invalid column index
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : Invalid column index
2009/01/27 08:16:12 - Oracle - LDEV - ERROR (version 3.1.0, build 826 from 2008/09/30 18:32:36) : ERROR in part: P Set values

Sven, thanks for your help.

[sboden]

If you only have 2 ?'s in your query you may only have 2 fields in the incoming rows... remove the one you don't use in the query.

Regards,
Sven

[dyerrington]

After putting a "select values" step between the table input for selecting MIN / MAX ID's and the query that ran the transformation, it worked without any problems.

Without your help, It certainly would have taken me allot longer..

Sven, thank you very much for your advice.