Jira 6.2.3 SQL exception on start

MilanCiti May 15, 2014

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

1 vote
Answer accepted
codelab expert
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 15, 2014

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2014

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 May 15, 2014

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2014

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

What does your dbconfig.xml file actually say?

MilanCiti May 15, 2014

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2014

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 May 15, 2014

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

MilanCiti May 15, 2014

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 15, 2014

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 May 15, 2014

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 15, 2014

Welcome!

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

2 votes
Rudy Holtkamp
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2017

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 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2014

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 May 15, 2014

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 15, 2014

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 15, 2014

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