pentaho/pentaho-kettle · error · KettleException

e (no own message; error caching lookup rows)

Error message

e (no own message; error caching lookup rows)

What it means

DatabaseLookup.loadAllTableDataIntoTheCache() preloads the whole lookup table into the cache when 'load all data from table' is enabled. Any exception from executing the SQL (db.getRows / putToDefaultCache / putToReadOnlyCache) is rethrown as a bare KettleException wrapping e, with no additional message.

Solutions

  1. Inspect the wrapped cause e — it is the actual KettleDatabaseException/SQLException
  2. Verify the lookup table exists and the connection user can SELECT from it
  3. Disable 'load all data from table' (use cached/db lookup) if the table is too large or has problematic key types
  4. Test the generated SQL in a DB client to reproduce the error

Example fix

// before
} catch ( Exception e ) {
  throw new KettleException( e );
}
// after
} catch ( Exception e ) {
  throw new KettleException( BaseMessages.getString(
    PKG, "DatabaseLookup.ERROR0003.ErrorCachingLookupRows" ), e ); // keep message + cause
}
Defensive patterns

Strategy: try-catch

Validate before calling

// pre-check table readability before enabling 'load all data from table'
RowMetaInterface f = db.getTableFields( meta.getSchemaTable() );
if ( f == null ) throw new KettleException( "Cannot cache; table missing: " + meta.getSchemaTable() );
long cnt = db.getRows( "SELECT 1 FROM " + meta.getSchemaTable() + " LIMIT 1", 1 ).size();

Try / catch

try {
  trans.execute(); trans.waitUntilFinished();
} catch ( KettleException e ) {
  Throwable root = e; while ( root.getCause() != null ) root = root.getCause(); // unwrap to real SQL error
  logError( "Lookup cache load failed: " + root.getMessage(), root );
}

Prevention

When it happens

Trigger: processRow() triggers a full-table cache load and the underlying SELECT against the lookup table throws — bad SQL from generated conditions, missing table, or a JDBC error.

Common situations: Lookup table missing/renamed in the DB; permission denied on the table; type-incompatible key comparison producing invalid SQL; DB connection dropped mid-load.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/6eabb113677c7be3. Report an issue: GitHub.

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/databaselookup/DatabaseLookup.java:507

      sql.append( " FROM " ).append(
        dbMeta.getQuotedSchemaTableCombination( environmentSubstitute( meta.getSchemaName() ),
          environmentSubstitute( meta.getTablename() ) ) );

      // order by?
      if ( !Utils.isEmpty( meta.getOrderByClause() ) ) {
        sql.append( " ORDER BY " ).append( meta.getOrderByClause() );
      }

      // Now that we have the SQL constructed, let's store the rows...
      //

      if ( data.allEquals ) {
        putToDefaultCache( db, sql.toString() );
      } else {
        putToReadOnlyCache( db, db.getRows( sql.toString(), 0 ) );
      }
    } catch ( Exception e ) {
      throw new KettleException( e );
    }
  }

  private void putToDefaultCache( Database db, String sql ) throws KettleDatabaseException {
    final int keysAmount = meta.getStreamKeyField1().length;
    AtomicReference<RowMetaInterface> prototype = new AtomicReference<>();
    AtomicBoolean firstRow = new AtomicBoolean( true );

    db.forEachRow( sql, 0, row -> {
      if ( firstRow.get() ) {
        // Assume that all rows have the same meta; let's reuse it for all rows
        prototype.set( copyValueMetasFrom( db.getReturnRowMeta(), keysAmount ) );
        firstRow.set( false );
      }
      putRowToDefaultCache( prototype.get(), keysAmount, row );
    } );
  }

View on GitHub (pinned to f3058517a1)