nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Jira 6.2.3 SQL exception on start

MilanCiti

I got SQL excpetion while starting Jira 6.2.3

SQL Exception while executing the following:SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, propertytype FROM public.propertyentry WHERE ENTITY_NAME=? AND ENTITY_ID=? (ORA-00903: invalid table name)

In atlassian-jira.log some other sql exception are present, starting with this one:

WARN [core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database

ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "public.jiraaction"

ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:

CREATE TABLE public.jiraaction (ID NUMBER(18,0) NOT NULL, issueid NUMBER(18,0), AUTHOR VARCHAR2(255 CHAR), actiontype VARCHAR2(255 CHAR), actionlevel VARCHAR2(255 CHAR), rolelevel NUMBER(18,0), actionbody CLOB, CREATED DATE, UPDATEAUTHOR VARCHAR2(255 CHAR), UPDATED DATE, actionnum NUMBER(18,0), CONSTRAINT PK_jiraaction PRIMARY KEY (ID))

Error was: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

I've prepared empty Oracle Db , driver version is 11.2, dbconfig.xml present , Java 1.7.

Any advice, hint what else I should set or modify.

Thank you.

Regards,

Milan

Accepted answers

codelab expert

Yes, Nic is right. Public is reserved word in an oracle database. Please look at http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm

You should use another name for database.

All comments

Nic Brough -Adaptavist-

Two things to look at - first "public" is an odd name for a Jira database - are you sure that's correct?

Second, have you set up the database to use case sensitive names?

codelab expert

Yes, Nic is right. Public is reserved word in an oracle database. Please look at http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm

You should use another name for database.

Nic Brough -Adaptavist-

Ooh, I didn't know that,

I just find it good practice to use indicative and clear names for databases. I was actually thinking that maybe he'd picked an existing database that had some form of constraints.

(Right, I've learned new stuff today, may I go home now? :-) )

MilanCiti

Hello

Thank you for your feedback and here is my answer :

1) case sensitive set by default

2) database name is not public, for sure, it is something like xxxxxxxxDBU1 ( for out test env )

To add more details, I used this Oracle DB to run JIRA 4.3.4 for our test purpose, everything was fine. I clear all objects that belongs to schema we our using, to make it empty. Maybe this cause a trouble ? What do you think ?

Also, I tried again to start it , same error, but this time I need to meniton that ( like in previous case ) from log this is the first WARN and ERROR

INFO [core.entity.jdbc.DatabaseUtil] Database Product Name is Oracle

INFO [core.entity.jdbc.DatabaseUtil] Database Product Version is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

INFO [core.entity.jdbc.DatabaseUtil] Database Driver Name is Oracle JDBC driver

INFO [core.entity.jdbc.DatabaseUtil] Database Driver Version is 11.2.0.2.0

INFO [NoModule] Getting Table Info From Database

WARN [core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database

ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "public.jiraaction"

ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:

Error was: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

Sorry , if it was mislead at first.

Regards,

Milan

Nic Brough -Adaptavist-

Right, you need to fix both problems - first the database naming should be case INsensitive, and secondly, as Mathias points out, you need to stop using the "public" name for it.

Create a new database schema called Jirauser, set it to ignore case on the table names and use that. Ideally, follow https://confluence.atlassian.com/display/JIRA/Connecting+JIRA+to+Oracle

codelab expert

Hm...is your database user a dbowner / dbcreator? Does he have all rights to create tables?

MilanCiti

Hello Mathias,

My user has following :

PRIVILEGE

-------------------------------

CREATE MATERIALIZED VIEW

CREATE TABLE

CREATE OPERATOR

CREATE TRIGGER

CREATE INDEXTYPE

CREATE TYPE

CREATE PROCEDURE

CREATE VIEW

CREATE CLUSTER

CREATE SEQUENCE

We are not using 'public' , at all, so I don't know from where this is coming Could not create table "public.jiraaction"

Exactly, I follow instruction from provided link 'Connecting Jira to Oracle', paying special attention to dbconfig.xml file settings. Also I've been through all upgrade notes from 4.3.4-6.2 not to miss some DB modification/changes.

Regards,

Milan

Nic Brough -Adaptavist-

You most clearly ARE using public. You need to fix that.

What does your dbconfig.xml file actually say?

Rudy Holtkamp

Maybe a bit late to the party, but if anyone run into this problem.

Remove

<schema-name>'public'</schema-name>

From your dbconfig.xml.

And restart JIRA.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration