ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Incremental Load DWH fails after 15.9.3 upgrade: syntax error at or near "MODIFY"

book

Article ID: 221993

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The incremental Load DATA WAREHOUSE (DWH) fails after the 15.9.3 upgrade: Couldn't execute SQL: ALTER TABLE DWH_RDM_ROADMAP_ITEM MODIFY ROADMAP_ITEM_NAME 

STEPS TO REPRODUCE:

  1. Run the Incremental Load DWH on a postgres system upgraded from 15.9.2 to 15.9.3
  2. Intermittently, or only in certain environments, it will fail with this error:
    MOD_COL_SQL - org.pentaho.di.core.exception.KettleStepException: Error while running this step!Couldn't execute SQL: ALTER TABLE DWH_RDM_ROADMAP_ITEM MODIFY ROADMAP_ITEM_NAME VARCHAR(768)ERROR: syntax error at or near "MODIFY"  Position: 34

Expected Results: Load DWH incremental completes without Modify syntax error

Actual Results: Load DWH incremental fails with a Modify syntax error

2021/08/17 14:15:19 - MOD_COL_SQL.0 - Couldn't execute SQL: 
ALTER TABLE DWH_RDM_ROADMAP_ITEM MODIFY ROADMAP_ITEM_NAME VARCHAR(768)
2021/08/17 14:15:19 - MOD_COL_SQL.0 - 
2021/08/17 14:15:19 - MOD_COL_SQL.0 - ERROR: syntax error at or near "MODIFY"
2021/08/17 14:15:19 - MOD_COL_SQL.0 -   Position: 34
2021/08/17 14:15:19 - MOD_COL_SQL.0 - 
2021/08/17 14:15:19 - MOD_COL_SQL.0 - 
2021/08/17 14:15:19 - MOD_COL_SQL.0 -  at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
2021/08/17 14:15:19 - MOD_COL_SQL.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2021/08/17 14:15:19 - MOD_COL_SQL.0 -  at java.base/java.lang.Thread.run(Thread.java:829)
2021/08/17 14:15:19 - MOD_COL_SQL.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2021/08/17 14:15:19 - MOD_COL_SQL.0 - Couldn't execute SQL: ALTER TABLE DWH_RDM_ROADMAP_ITEM MODIFY ROADMAP_ITEM_NAME VARCHAR(768)2021/08/17 14:15:19 - MOD_COL_SQL.0 - 
2021/08/17 14:15:19 - MOD_COL_SQL.0 - ERROR: syntax error at or near "MODIFY"

Cause

Defect DE62229 

Environment

Release : 15.9.3

Component : CLARITY DATA WAREHOUSE

Database: Postgres

Resolution

Fixed in 16.0.

Workaround:  Run a Full Load Data Warehouse.  This will correct the issue for future incremental runs