How to Fix Oracle Error ORA-01747: invalid user.table.column, table.column, or column specification
Symptoms
JIRA applications attempt to connect to the Oracle database and fail, throwing a java.sql.SQLSyntaxErrorException. This will prevent JIRA applications from starting up.
The following appears in the atlassian-jira.log:
2012-11-14 09:43:10,746 main INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher
2012-11-14 09:43:14,505 main ERROR [jira.upgrade.util.UpgradeUtils] JDBC get version number failed. SQL: SELECT ps.null FROM propertyentry pe, propertystring ps WHERE pe.null='jira.version.patched' AND pe.null=ps.null
java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specificationCause
OFBiz, the persistence framework that JIRA applications use is querying the Oracle database with a column name of null instead of the appropriate column name (propertyvalue). This is a symptom of a non-standard configuration of the Oracle database connection that is not currently supported. For example, the JIRA application DB is created using one user with a full set of permissions and then restarted with another user that does not have the required permissions.
Resolution
It is critical to the operation of JIRA applications that these permissions are provided as we now use the Active Objects persistence ORM in a lot of our plugins and also third-party plugins may use it. If JIRA applications do not have the appropriate permissions to create objects within the database, plugin installations and upgrades can fail, as mentioned ORA-01031 KB article.
- Stop your JIRA applications.
- Ensure the JIRA application DB user has the required permissions, as in Connecting JIRA applications to Oracle.
- Start your JIRA applications.