org.pentaho.di.core.exception.KettleDatabaseException: Error inserting row ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated

Pentaho BI Platform Tracking | Cor Winckler | 9 years ago
  1. 0

    Most of the error information you display in the error log is useful to a Kettle Java Developer, but not to the ETL developer (ie, the user) who is using the tool. Here is an example of a lookup that fails: 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Because of an error this step can't continue: 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Error inserting row 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : org.pentaho.di.core.exception.KettleDatabaseException: 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Error inserting row 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1324) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1211) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.dimInsert(DimensionLookup.java:768) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:323) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:182) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.run(DimensionLookup.java:1245) 2008/03/25 16:36:36 - LKU_MSISDN_FROM.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Caused by: java.sql.SQLException: ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated As a tool user I see 2 useful pieces of information. 1) It was the LKU_MSISDN step that failed. 2) There was an Oracle Error, namely a unique constraint called UK_DIM_GSM_SERVICE) This is a valid error, not a mistake by the Java Programmer on your side, so all the logging like at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.run(DimensionLookup.java:1245) is not useful to me at all, although I understand that it would be to you if the error had been a java error. This failed after say 1453424 (1.4 million) rows. Just telling me that a unique constraint has been violated is not great (its a start). If I had the VALUES or the insert statement printed together with the error, as well as perhaps either the entire row or the key attributes (i realise Kettle has no way of knowing which the keys are at this point, so lets just make it the ENTIRE ROW). That would immediately allow me to diagnose what went wrong. Right now, I have to try and find the row in the table, to understand why the lookup failed, and why the new row seems to already exist, but finding row nr 1453424 in a table that is constantly changing is going to be a huge problem.... Here is the enhancement request: When a legitimate error occurs, ad much context (data context) as possible should be printed out in the log, to make it practical for the tool user to find the problem in the data. I logged a similar call earlier about writing out the line nr for text file input errors. I see that one has been done. Its the same kind of request.... I want as much info about the context of the error as possible. Just like you want a complete java stack dump to see where in the SOFTWARE the problem occured, I want to see ecactly where in the DATA the problem occured.

    Pentaho BI Platform Tracking | 9 years ago | Cor Winckler
    org.pentaho.di.core.exception.KettleDatabaseException: Error inserting row ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated
  2. 0

    Value '0000-00-0' can not be represented as java.sql.Date

    Google Groups | 2 years ago | Tomo Suryolaksono
    org.pentaho.di.core.exception.KettleDatabaseException: 2014/10/30 10:19:47 - Transaksi.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Couldn't get row from result set 2014/10/30 10:19:47 - Transaksi.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : Value '0000-00-00' can not be represented as java.sql.Timestamp 2014/10/30 10:19:47 - Transaksi.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 11:32:36) : 2014/10/30 10:19:47 - Transaksi.0 - ERROR (version 3.1.0, build 826 from
  3. 0

    Apache thrift transportaion TTransportException error while using Hive data integration using Pentaho

    Stack Overflow | 2 years ago | Jiten Patel
    org.pentaho.di.core.exception.KettleDatabaseException: Error occured while trying to connect to the database</p> <p>Error connecting to database: (using class org.apache.hadoop.hive.jdbc.HiveDriver) org.apache.thrift.transport.TTransportException</p> <pre><code>at org.pentaho.di.core.database.Database.normalConnect(Database.java:428)
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Reproduction: - In Spoon 4.4 create a log table in a simple transformation (see attachment) - let it run in Spoon 4.4 - look at the execution history (refresh) and see one log line - Close Spoon 4.4 - Open Spoon 5.0 (on Windows, use SpoonConsole.bat to see the exception mentioned below) - it automatically opens the previous transformation - It opens the Execution history but this is empty The following exception is shown on the console: Unable to get rows of data from logging table test_dbx-log_table4_5_test org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDATE, LOGDATE, DEPDATE, REPLAYDATE, LOG_FIELD, EXECUTING_SERVER, EXECUTING_USER, START_JOB_ENTRY, CLIENT FROM log_table4_5_test WHERE JOBNAME LIKE ? ORDER BY ID_JOB DESC Unknown column 'EXECUTING_SERVER' in 'field list' at org.pentaho.di.core.database.Database.openQuery(Database.java:1610) [...] Note: - We added the fields EXECUTING_SERVER, EXECUTING_USER and CLIENT to the log table in 5.0 - I propose to make these fields disabled by default to keep compatible with previous log tables

    Pentaho BI Platform Tracking | 4 years ago | Jens Bleuel
    org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDATE, LOGDATE, DEPDATE, REPLAYDATE, LOG_FIELD, EXECUTING_SERVER, EXECUTING_USER, START_JOB_ENTRY, CLIENT FROM log_table4_5_test WHERE JOBNAME LIKE ? ORDER BY ID_JOB DESC Unknown column 'EXECUTING_SERVER' in 'field list'
  6. 0

    IO Error: Socket read timed out when using the Table Input in transformation

    pentaho.com | 8 months ago
    org.pentaho.di.core.exception.KettleDatabaseException: - Couldn't get row from result set - IO Error: Socket read timed out

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.pentaho.di.core.exception.KettleDatabaseException

      Error inserting row ORA-00001: unique constraint (MART.UK_DIM_GSM_SERVICE) violated

      at org.pentaho.di.core.database.Database.insertRow()
    2. org.pentaho.di
      DimensionLookup.run
      1. org.pentaho.di.core.database.Database.insertRow(Database.java:1324)
      2. org.pentaho.di.core.database.Database.insertRow(Database.java:1211)
      3. org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.dimInsert(DimensionLookup.java:768)
      4. org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:323)
      5. org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:182)
      6. org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.run(DimensionLookup.java:1245)
      6 frames