Jira shows the error "Cannot insert the value NULL into column 'ID', table 'xxxxx'; column does not allow nulls. INSERT fails".

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

問題

atlassian-jira.log ログに次のエラーが表示される。

2016-08-18 14:06:19,890 http-nio-8080-exec-23 ERROR admin xxxxxxx xxxxx 10.12.3.xxx,78.91.0.xxx /rest/issueNav/1/issueTable [velocity] Exception in macro #issues at templates/jira/issue/table/issuetable-issue.vm[line 6, column 5]
2016-08-18 14:06:19,890 http-nio-8080-exec-23 ERROR admin xxxxxxx xxxx 10.12.3.xxx,78.91.0.xxx /rest/issueNav/1/issueTable [c.atlassian.velocity.DefaultVelocityManager] MethodInvocationException occurred getting message body from Velocity: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Microsoft SQL Server
- version:11.00.2100
- minor version:0
- major version:11
Driver:
- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
- version:1.3.1

java.sql.SQLException: Cannot insert the value NULL into column 'ID', table 'jira.dbo.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Microsoft SQL Server
- version:11.00.2100
- minor version:0
- major version:11
Driver:
- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
- version:1.3.1
java.sql.SQLException: Cannot insert the value NULL into column 'ID', table 'jira_backlog_test.dbo.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:88)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:261)
... 2 filtered
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
...
Caused by: java.sql.SQLException: Cannot insert the value NULL into column 'ID', table 'jira_backlog_test.dbo.AO_60DB71_LEXORANK'; column does not allow nulls. INSERT fails.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)

Environment:

  • JIRA 7.1.x and higher
  • MSSQL サーバー

診断

  • Verify if SET NOCOUNT option is unchecked by running this SQL query:

    DECLARE @NOCOUNT VARCHAR(3) = 'OFF';  
    IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON';  
    SELECT @NOCOUNT AS NOCOUNT;
  • This will return a value of OFF or ON.  This is a database-wide setting.  However, customers who are affected by this issue will still have some tables in the database that do not have the NOCOUNT setting correctly set. 

  • Run the following query to determine whether the table that is cited in the error (in the example above, the table is jira.dbo.AO_60DB71_LEXORANK) is affected by this issue. 

    SELECT 
    [schema] = s.name,
    [table] = t.name
    FROM sys.schemas AS s
    INNER JOIN sys.tables AS t
    ON s.[schema_id] = t.[schema_id]
    WHERE EXISTS 
    (
    SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
    );
  • This will return a list of tables with the correct NOCOUNT option set.  
  • Search the list for the name of the table in the error.  If that table is not on the list, the table is affected.  

原因

This issue has been observed to happen after a database collation change in MS SQL server. As per our Connecting JIRA applications to SQL Server documentation, the SET NOCOUNT option must be turned off prior to the database creation. If this option is not set initially and is corrected later, it may not retroactively apply the change to existing tables. Collation changes may also set this attribute back to the default (SET NOCOUNT ON).  The exact cause has not been determined, but these are the observed triggers. 

ソリューション

データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。


Resolution 1: Fix the Lexorank Table through SQL Queries

(info) Replace <Table_Name> in the statements below with the affected table's name. In the example above, the affected table is jira.dbo.AO_60DB71_LEXORANK.  

  • Truncate the Lexorank table:  

    TRUNCATE TABLE jira.dbo.<Table_Name>
    
    Example:
    TRUNCATE TABLE jira.dbo.AO_60DB71_LEXORANK


    (info) If you are using Agile, you will lose the ranking data.

  • Drop the column ID of the Lexorank table:  

    ALTER TABLE jira.dbo.<Table_Name> DROP COLUMN "ID"
    
    Example:
    ALTER TABLE jira.dbo.AO_60DB71_LEXORANK DROP COLUMN "ID"
  • Re-add it by running these two SQL statements:

    ALTER TABLE jira.dbo.<Table_Name>
       ADD ID INT IDENTITY
    
    Example:
    ALTER TABLE jira.dbo.AO_60DB71_LEXORANK
       ADD ID INT IDENTITY

    constraints warning

    On the recreation of the constraint, please review the correct name of the constraint.

    ALTER TABLE jira.dbo.<Table_Name>
       ADD CONSTRAINT PK_<Table_Name>_ID
       PRIMARY KEY(ID)
    
    Example:
    ALTER TABLE jira.dbo.AO_60DB71_LEXORANK
       ADD CONSTRAINT PK_AO_60DB71_LEXORANK_ID
       PRIMARY KEY(ID)
  • Jira を再起動します。
  • Run Lexorank balancing and full re-indexing.


Resolution 2: Fixing Multiple Tables with XML export/import

(warning) If multiple tables are affected, the easiest way is to generate an XML backup, unzip it and then open the activeobjects.xml in a text editor. From there, you can use the Find functionality to search for <column name="ID" primaryKey="false" autoIncrement="false" and replace it  with <column name="ID" primaryKey="true" autoIncrement="true". Save the file and zip it together with entities.xml and import it to the JIRA instance using a supported collation. 

For example this command will verify the problem:

$ grep '<column name="ID" primaryKey="false"' activeobjects.xml 


And you can fix with this:

sed -i -e 's/<column name="ID" primaryKey="false" autoIncrement="false"/<column name="ID" primaryKey="true" autoIncrement="true"/g' activeobjects.xml 



最終更新日 2023 年 9 月 8 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.