AnsweredAssumed Answered

Invalid Dates in Transformation

Question asked by Bruce L on Oct 4, 2018
Latest reply on Oct 6, 2018 by Bill Moore


This happens randomly.  If run my transformation and it has to update/insert multiple tables, and it will randomly throw up errors about bad dates.   Example below.  However, if I run the same job, unmodified in 5-10 minutes, everything works great. 

 

My workaround has been to put blocking steps before each insert so they're not all running together.  (See pic below).  This problem only happens when data is transferring at a high rate (db server and pentaho server are in datacenter).   If i run locally, the data has to go through the wire and is updating at a slower rate, so no problem.

 

 

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : Because of an error, this step can't continue:

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ERROR (version 8.1.0.0-365, build 8.1.0.0-365 from 2018-04-30 09.42.24 by buildguy) : org.pentaho.di.core.exception.KettleException:

2018/10/04 12:32:41 - Insert Lease Transactions.0 - Error inserting row into table [lease_transactions_new] with values: [1629198], [1], [1300744], [1998/02/20 00:00:00.000000000], [1997/01/08 00:00:00.000000000], [2000/01/08 00:00:00.000000000], [null], [2000/01/08 00:00:00.000], [null], [3], [Y], [1997/02/18 00:00:00.000000000], [1534], [OPR], [820], [98171], [Larry Holte], [Petroleum Company of America], [1.0], [null], [Original Lease], [P], [P], [P], [P], [P], [P], [P], [P], [P], [P], [new], [1963074], [1963074], [4364715], [4364715]

2018/10/04 12:32:41 - Insert Lease Transactions.0 -

2018/10/04 12:32:41 - Insert Lease Transactions.0 - Error inserting/updating row

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ERROR: date/time field value out of range: "1998-02-29 00:00:00-06"

2018/10/04 12:32:41 - Insert Lease Transactions.0 -

2018/10/04 12:32:41 - Insert Lease Transactions.0 -

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:385)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at java.lang.Thread.run(Thread.java:745)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException:

2018/10/04 12:32:41 - Insert Lease Transactions.0 - Error inserting/updating row

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ERROR: date/time field value out of range: "1998-02-29 00:00:00-06"

2018/10/04 12:32:41 - Insert Lease Transactions.0 -

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1319)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:262)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ... 3 more

2018/10/04 12:32:41 - Insert Lease Transactions.0 - Caused by: org.postgresql.util.PSQLException: ERROR: date/time field value out of range: "1998-02-29 00:00:00-06"

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - at org.pentaho.di.core.database.Database.insertRow(Database.java:1286)

2018/10/04 12:32:41 - Insert Lease Transactions.0 - ... 4 more

Attachments

Outcomes