search cancel

Upgrade to 15.8 fails: ORA-01400: cannot insert NULL into ("CLARITY_USER"."CMN_SCH_JOBS"."ID")

book

Article ID: 188743

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

While upgrading Clarity PPM to 15.8, it fails with the following errors:

4/09/20 3:36 AM (ExecTask) Process - other: SETUP_GROUP_BY_JOB.xml
4/09/20 3:36 AM (ExecTask) com.ca.clarity.jdbc.oraclebase.ddc: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01400: cannot insert NULL into ("CLARITY_USER"."CMN_SCH_JOBS"."ID")
4/09/20 3:36 AM (ExecTask) ORA-06512: at line 16
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask)  at com.ca.clarity.jdbc.oraclebase.dde3.l(Unknown Source)
4/09/20 3:36 AM (ExecTask)  at com.ca.clarity.jdbc.oraclebase.dde3.a(Unknown Source)
4/09/20 3:36 AM (ExecTask)  at com.ca.clarity.jdbc.oraclebase.dde3.executeBatch(Unknown Source)
4/09/20 3:36 AM (ExecTask)  at jdk.internal.reflect.GeneratedMethodAccessor29.invoke(Unknown Source)
4/09/20 3:36 AM (ExecTask)  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
4/09/20 3:36 AM (ExecTask)  at java.base/java.lang.reflect.Method.invoke(Method.java:566)
4/09/20 3:36 AM (ExecTask)  at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
4/09/20 3:36 AM (ExecTask)  at com.sun.proxy.$Proxy6.executeBatch(Unknown Source)
4/09/20 3:36 AM (ExecTask)  at com.niku.dbtools.Utilities.executeAndCommitSql(Utilities.java:2892)
4/09/20 3:36 AM (ExecTask)  at com.niku.dbtools.DriverApp.apply(DriverApp.java:1491)
4/09/20 3:36 AM (ExecTask)  at com.niku.dbtools.DriverApp.installSchemaDriver(DriverApp.java:694)
4/09/20 3:36 AM (ExecTask)  at com.niku.dbtools.Utilities.run(Utilities.java:1968)
4/09/20 3:36 AM (ExecTask)  at com.niku.dbtools.Utilities.main(Utilities.java:1055)
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask) SQL Text:
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask) BEGIN
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask)   DECLARE
4/09/20 3:36 AM (ExecTask)     JOB_EXISTS INTEGER;
4/09/20 3:36 AM (ExecTask)     V_DEF_ID NUMERIC;
4/09/20 3:36 AM (ExecTask)     V_JOB_ID NUMERIC;
4/09/20 3:36 AM (ExecTask)     V_NONCONCUR_ID NUMERIC;
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask)   BEGIN
4/09/20 3:36 AM (ExecTask)     SELECT COUNT(*) INTO JOB_EXISTS FROM CMN_SCH_JOB_DEFINITIONS WHERE job_code = 'TIME_SLICING_SQLCURVE';
4/09/20 3:36 AM (ExecTask)     IF(JOB_EXISTS = 0) THEN
4/09/20 3:36 AM (ExecTask)       CMN_SCH_JOB_DEF_INSERT_SP('Time Slicing Group By','TIME_SLICING_SQLCURVE','Time Slicing for Group By', 'JAVA', 'com.niku.blobcrack.SqlCurveCrack',null,0,0,1,1,0,1,1,'en','niku.com',0,1,null, V_DEF_ID);
4/09/20 3:36 AM (ExecTask) 
4/09/20 3:36 AM (ExecTask)       -- avoid 5000000 due to potential issue in job schedulers
4/09/20 3:36 AM (ExecTask)       SELECT MAX(ID) + 10 INTO V_JOB_ID FROM CMN_SCH_JOBS WHERE ID < 5000000;
4/09/20 3:36 AM (ExecTask)       INSERT INTO CMN_SCH_JOBS ( ID, JOB_DEFINITION_ID, NAME, START_DATE, END_DATE, SCHEDULE_DATE, MINUTES, HOURS, MONTHS, DAYS_OF_MONTH, DAYS_OF_WEEK, PRIORITY, STATUS_CODE, IS_VISIBLE, RECURRENCE_TYPE, OUTPUT_FORMAT, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY )
4/09/20 3:36 AM (ExecTask)         VALUES  ( V_JOB_ID, V_DEF_ID, 'Time Slicing Group By', SYSDATE, null, SYSDATE, '*', '*','*', '*', '*', 1, 'SCHEDULED', 1, 3, null, SYSDATE, 1, SYSDATE, 1);

Cause

This is caused by DE54477

The following select query returns a NULL value and therefore tries to do an insert with a NULL value.
SELECT MAX(ID) + 10 V_JOB_ID FROM CMN_SCH_JOBS WHERE ID < 5000000

Resolution

In 15.8.1 this is not an issue any longer.

When upgrading to 15.8.0 if you encounter this issue, please use resolution below:
Update the CMN_SCH_JOBS table so an ID below the 5000000 ID exists.
Please find below some ID and job name relationship:

Sample of an update query that can be run if all IDS at the table are above 5000000

Update CMN_SCH_JOBS set ID = 1001 where id = 5XXXXXX (This should be an existing ID you get at your DB and is higher than 5000000)


1. Rollback to the previous version:
2. Update the CMN_SCH_JOBS table so an ID below 5000000 exists.
3. Re-run the upgrade

Attachments