Error moving issues after upgrade to Jira Server 7.50 - Cannot insert the value NULL into column 'ID

[Infrastructure Team]

We recently upgraded Jira Server from 7.1 to 7.5 and since doing so while Jira works mostly we have encountered the following issues:

When attempting to move issues between projects we encounter an error snippet of which follows below:

Activity stream is incredibly slow to load when opening Jira - 1 minute plus.

Anyone with suggestions as to what else can be done? I have not logged this on bug tracker with atlassian yet but a search there has not yielded any clues.

Referer URL: https://jiraserver_fqdn:8443/secure/MoveIssue!default.jspa?id=58969

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:

Database:

        - name:Microsoft SQL Server

        - version:11.00.5592

        - minor version:0

        - major version:11

Driver:

        - name:Microsoft JDBC Driver 6.2 for SQL Server

        - version:6.2.1.0

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'ID', table 'Jira750.jiraschema.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.

Found this link which seems to detail the issue we're facing so we truncated the Lexorank DB then dropped and re-added. Restarted Jira ok but the LexoRank system page does not show any administrstion buttons, just this message:

The LexoRank system is part of JIRA Software. If you have never used JIRA Software before, you won't need to configure anything on this screen. Otherwise, you can manage the ordering of issues via this screen, but we recommend that you contact our support team before doing so.

Also found this link but following steps there and reindexing made no difference. 

And tailing the catalina.out log while moving an issue we see the following:

2017-10-09 12:31:34,743 https-jsse-nio-8443-exec-4 ERROR [o.a.c.c.C.[.[localhost].[/].[action]] Servlet.service() for servlet [action] in context with path [] threw exception [com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Microsoft SQL Server
- version:11.00.5592
- minor version:0
- major version:11
Driver:
- name:Microsoft JDBC Driver 6.2 for SQL Server
- version:6.2.1.0

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'ID', table 'Jira750.jiraschema.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.] with root cause
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'ID', table 'Jira750.jiraschema.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at net.java.ao.DatabaseProvider.executeInsertReturningKey(DatabaseProvider.java:1947)
at net.java.ao.db.SQLServerDatabaseProvider.insertReturningKey(SQLServerDatabaseProvider.java:356)
at net.java.ao.EntityManager.create(EntityManager.java:366)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:92)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:266)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy2722.create(Unknown Source)
at com.atlassian.greenhopper.manager.lexorank.collation.CollationIntegrityDao.createCollationCheckRows(CollationIntegrityDao.java:49)
at com.atlassian.greenhopper.manager.lexorank.collation.CollationIntegrityCheckerImpl.getLexoRankDaoContext(CollationIntegrityCheckerImpl.java:60)
at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.getContext(LexoRankDaoImpl.java:191)
at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.findByFieldAndIssueId(LexoRankDaoImpl.java:225)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:153)
at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.execute(LexoRankOperation.java:120)
at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.performRankOperation(LexoRankManagerImpl.java:300)
at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.getRankOrRankInitially(LexoRankManagerImpl.java:86)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:121)
at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:29)
at com.atlassian.jira.issue.fields.ImmutableCustomField.getValue(ImmutableCustomField.java:350)
at com.atlassian.jira.issue.fields.ImmutableCustomField.doesFieldHaveValue(ImmutableCustomField.java:1179)
at com.atlassian.jira.issue.fields.ImmutableCustomField.needsMove(ImmutableCustomField.java:1058)
at com.atlassian.jira.web.action.issue.MoveIssueUpdateFields.doDefault(MoveIssueUpdateFields.java:67)
... 3 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)
at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)
... 2 filtered
at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:63)
... 7 filtered
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
... 52 filtered
at com.atlassian.greenhopper.jira.filters.ClassicBoardRouter.doFilter(ClassicBoardRouter.java:62)
... 12 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 58 filtered
at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
... 1 filtered
at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
... 36 filtered
at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
... 10 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 4 filtered
at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
... 26 filtered
at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
... 23 filtered
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

[Andy Heinzer]

Hi team,

Sorry to hear there are SQL exception here.  Given the steps you have followed so far, I would like to confirm what data exists in your database right now.  It sounds like your problem is similar to that of https://jira.atlassian.com/browse/JSWSERVER-10975

From that page there are steps to follow to make sure this is setup correctly.   Specifically I am interested to see the results of step #3 and then learn if you formatted the insert statements as such:

1. Backup JIRA before making any changes.
2. Stop JIRA.
3. Find out the field ID or IDs for LexoRank fields:

   select id from customfield where customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-lexo-rank';
   

4. For each field ID returned by the previous query, run a query like below, replacing <FIELD_ID> by the field ID:

   INSERT INTO "AO_60DB71_LEXORANK" ("ID", "FIELD_ID", "ISSUE_ID", "RANK", "TYPE") VALUES (DEFAULT, <FIELD_ID>, -9223372036854775808, '0|000000:', 0);
   INSERT INTO "AO_60DB71_LEXORANK" ("ID", "FIELD_ID", "ISSUE_ID", "RANK", "TYPE") VALUES (DEFAULT, <FIELD_ID>, 9223372036854775807, '0|zzzzzz:', 2);
   

5. Start JIRA.

Please try these steps and let me know the results.

Andy