ORA-01451: column to be modified to NULL cannot be modified to NULL
Symptoms
The following appears in the atlassian-jira.log:
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 structure on same database
Resolution
Always back up your data before performing any modifications to the database. 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
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