org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193

Pentaho BI Platform Tracking | Alexander Thiel | 5 years ago
  1. 0

    The tools in the PDI suite (spoon, pan, kitchen, carte) work in a way that if the variuos logging variables (KETTLE_CHANNEL_LOG_*, KETTLE_JOB_LOG_*, etc.) are defined in the kettle.properties file and the corresponding fields are left blank in the jobs and transformations, the variables are substituted automatically and logging is enabled. This is very convenient, especially if you have a large number of jobs, since it makes logging enabled by default. However, the Pentaho Enterprise Console does not seem to use this mechanism. I have configured my system as described above and the jobs execute just fine, logging into the corresponding tables. The status page in the PEC does not display anything and in the logs I see the error message org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193 at org.pentaho.di.core.database.Database.openQuery(Database.java:1880) at org.pentaho.di.core.database.Database.openQuery(Database.java:1807) at org.pentaho.di.core.database.Database.openQuery(Database.java:1802) at com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.a(SourceFile:424) at com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.getExecutionInfo(SourceFile:663) As you can see, the variable KETTLE_JOB_LOG_TABLE does not seem to get substituted. Interestingly, KETTLE_JOB_LOG_DB obviously does, since I get the error from the database.

    Pentaho BI Platform Tracking | 5 years ago | Alexander Thiel
    org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193
  2. 0

    The tools in the PDI suite (spoon, pan, kitchen, carte) work in a way that if the variuos logging variables (KETTLE_CHANNEL_LOG_*, KETTLE_JOB_LOG_*, etc.) are defined in the kettle.properties file and the corresponding fields are left blank in the jobs and transformations, the variables are substituted automatically and logging is enabled. This is very convenient, especially if you have a large number of jobs, since it makes logging enabled by default. However, the Pentaho Enterprise Console does not seem to use this mechanism. I have configured my system as described above and the jobs execute just fine, logging into the corresponding tables. The status page in the PEC does not display anything and in the logs I see the error message org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193 at org.pentaho.di.core.database.Database.openQuery(Database.java:1880) at org.pentaho.di.core.database.Database.openQuery(Database.java:1807) at org.pentaho.di.core.database.Database.openQuery(Database.java:1802) at com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.a(SourceFile:424) at com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.getExecutionInfo(SourceFile:663) As you can see, the variable KETTLE_JOB_LOG_TABLE does not seem to get substituted. Interestingly, KETTLE_JOB_LOG_DB obviously does, since I get the error from the database.

    Pentaho BI Platform Tracking | 5 years ago | Alexander Thiel
    org.pentaho.di.core.exception.KettleDatabaseException: An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193
  3. 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'
  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

    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)

    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

      An error occurred executing SQL: SELECT ID_JOB, CHANNEL_ID, JOBNAME, STATUS, LINES_READ, LINES_WRITTEN, LINES_UPDATED, LINES_INPUT, LINES_OUTPUT, LINES_REJECTED, ERRORS, STARTDATE, ENDDAT E, LOGDATE, DEPDATE, REPLAYDATE FROM WHERE STATUS = 'end' AND JOBNAME = ? ORDER BY REPLAYDATE ERROR: syntax error at or near "WHERE" Position: 193

      at org.pentaho.di.core.database.Database.openQuery()
    2. org.pentaho.di
      Database.openQuery
      1. org.pentaho.di.core.database.Database.openQuery(Database.java:1880)
      2. org.pentaho.di.core.database.Database.openQuery(Database.java:1807)
      3. org.pentaho.di.core.database.Database.openQuery(Database.java:1802)
      3 frames
    3. com.pentaho.pac
      RepositoryUtils.getExecutionInfo
      1. com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.a(SourceFile:424)
      2. com.pentaho.pac.server.pdi.model.rep.RepositoryUtils.getExecutionInfo(SourceFile:663)
      2 frames