Jira 6.2.3 SQL exception on start

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

3 answers

1 accepted

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.

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

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

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

What does your dbconfig.xml file actually say?

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

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

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

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

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.

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

Welcome!

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

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.

0 vote

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?

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

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

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Sunday in Agility

You asked for it, so we delivered: images on issues have arrived

A picture tells a thousand words. And agility boards have just released their latest feature: cover images on issues – so now your board can tell a story at first glance. Upload attachmen...

218 views 2 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you