ORA-01451: column to be modified to NULL cannot be modified to NULL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Symptoms
The following appears in the atlassian-jira.log:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2014-11-21 11:10:12,159 lexorank-executor-thread-0 WARN [java.ao.db.OracleDatabaseProvider] Error in schema creation: ORA-01451: column to be modifi
ed to NULL cannot be modified to NULL
; attempting to roll back last partially generated table
2014-11-21 11:10:12,160 lexorank-executor-thread-0 ERROR [service.lexorank.balance.LexoRankBalancingService] There was a SQL exception thrown by the
Active Objects library:
Database:
name:Oracle
version:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
minor version:2
major version:11
Driver:
name:Oracle JDBC driver
version:11.2.0.4.0
java.sql.SQLException: ORA-01451: column to be modified to NULL cannot be modified to NULL
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
name:Oracle
version:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
minor version:2
major version:11
Driver:
name:Oracle JDBC driver
version:11.2.0.4.0
java.sql.SQLException: ORA-01451: column to be modified to NULL cannot be modified to NULL
Diagnosis
Usually occurs when restoring from another Oracle DB backup.
Cause
The above error message is an Oracle-specific problem, as described in this KB article below
http://www.techonthenet.com/oracle/errors/ora01451.php
There are a few reasons this can occur.
Reusing sequences from another instance
Permissions level
Multiple Schemas with similar structures on the same database
Solution
Resolution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
There are multiple resolutions depending on the root cause:
Scenario 1 (Sequence Constraints):
Drop the problematic AO tables and sequences so the JIRA Agile will re-create them again upon restart
Drop the NOT NULL constrain from the problematic AO tables so the upgrade task can continue successfully
ℹ️ Another approach would be to create a new USER and restore the XML backup onto that. This will re-create the schema and restore the data with no data loss.
Scenario 2 (Permissions Level Issue):
Grant appropriate permissions to DB user
1
2
3
4
grant connect to <user>;
grant create table to <user>;
grant create sequence to <user>;
grant create trigger to <user>;
Scenario 3 (Multiple Schemas):
Restore on a fresh Database with only 1 similar schema
Was this helpful?