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

[codelab expert · Accepted solution]

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 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?

[MilanCiti]

I think you get me to the point, in dbconfig.xml there is 'public' for <schema-name> . Let me correct and try again.

[Nic Brough -Adaptavist-]

Ahh, that would be it. The number of times I get that file wrong, it's the first thing I look at when there's any database issues...

[MilanCiti]

Finally, it's working. Thank you for your support and hints.

[MilanCiti]

Indeed, even I was careful for PROD, I missed that in test env. Now, it's resolved, so I can continue with some additinal testing. Once more thank you for your prompt feedback and support !!

Regards,

Milan

[codelab expert]

Ok, nice to hear! Please be so kind and mark this question as resolved. So other people at Atlassian Answers knows what to do in the same situation.

[MilanCiti]

Since, its my first time to ask question , how can I put it to reslove state ? Or it is already resolved , cause I accepted the answer, question has green icon. Was that action enough ? Thank you.

Milan

[codelab expert]

Welcome!

That's right. If an answer is green the question is marked as resolved. You earn some karma for doing this :-)

[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.

[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?

[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:

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

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?