Upgrade to 15.8 fails: ORA-01400: cannot insert NULL into ("CLARITY_USER"."CMN_SCH_JOBS"."ID")
Article ID: 188743
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);
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
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
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
Feedback
Yes
No
Powered by
