Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Using active objects alias causes error ORA-00904 invalid identifier Edited

Hello,

on a customers system our plugin (version 0.5 to 1.1.1) and jira (?? to 7.5.0) were upgraded.

Since then the plugin started throwing exceptions when we access a Active Objects table like this:

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "issueShare")
.where("issueShare.ISSUE_ID = ?", issue.getId())
.order("issueShare.ID DESC")
);

 The error message is

java.sql.SQLSyntaxErrorException: ORA-00904: "issueShare"."ID": invalid identifier

It looks to me like the alias is not resolved when the sql is built, but why?

The same piece of code worked on the old system before the update and on other systems with the same Plugin Version that was installed during the upgrade.

Two fields were added to the IssueShare table between version 0.5 and 1.1.1, thats everything that  has changed.

Thanks in Advance!

Jens

2 answers

1 accepted

Changing the alias to uppercase seems to fix the problem for oracle 12.

The lowercase alias causes the same problems.

We will do some regression testing to see if this causes trouble for MySQL, PSQL or HSQL.

1 vote
Eduard M Atlassian Team Nov 23, 2017

Hello Jens,

The ORA-00904 invalid identifier means that Oracle didn't like the column name. Since it's printed in the error as a quoted identifier, I would examine how Oracle is reporting the table/column name and possibly if it changed recently. Also you may try to trace the invalid SQL to look for anything unexpected. Hope you can find something interesting. 

Hi Eduard,

no the ID column did not change.

How can the SQL be traced?

Eduard M Atlassian Team Nov 24, 2017

Hi Jens,

OK, what I was wondering is if Oracle's dictionary is storing the tablename in uppercase, that would clash with the quoted identifier usage in the code. You can check with DESCRIBE command in sqlplus if that seems to be the case. 'SHOW DESCRIBE' will give you the current settings and 'DESCRIBE issueShare' will give info about that table.

There are some examples of tracefiles on the net although an Oracle dba might be needed if they are not enabled by default. The example I found was pointing to an existing trace file:

SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
---------------------------------------------------------------------
/u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc

SQL> host tail -10 /u00/app/oracle/diag/rdbms/dba112/DBA112/trace/DBA112_ora_31964.trc
-------------------------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : CHA.EMP
Policy name    : EMP_POLICY
Policy function: CHA.EMP_RESTRICT
RLS view  :
SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "CHA"."EMP"   "EMP" WHERE ('SCOTT' = enamee)
ORA-00904: "ENAMEE": invalid identifier
-------------------------------------------------------------

 The pre-upgrade JVM version and current Oracle version and odbc driver version might be relevant info as well.

Hi Eduard,

we checked the traces and found this example of a not working query:

Unbenannt.PNG

It seems like the problem is caused by the double quotes in the order by clause.

How can we get rid of those?

The current Oracle Version is Oracle 12.1.0.2.0  und the driver filename is ojdbc7.jar

The pre-update system is not accessible any more as the machine is now running the upgraded version of Jira.

The oracle version was not changed.

 

best regards

Jens

Eduard M Atlassian Team Nov 27, 2017

Hi Jens,

The double quotes are forcing case-sensitivity, without them Oracle would not throw the error. Does the query run correctly if you change to:

ORDER BY "ISSUESHARE"."ID" DESC

If so, I would either look at updating the java code (but then you'll need to do regression testing) or checking again in the old version how that worked (I would guess an older odbc driver converted the case automatically, which won't be a workaround). 

Hi Eduard, thanks for the fast reply!

we will try that.

Is there any point in raising that as an issue somewhere?

If so where?

kind regards

Jens

Eduard M Atlassian Team Nov 27, 2017

Hi Jens,

There isn't any resolution available other than observing the behaviour which leads to the problem. Oracle will match case-sensitivity whenever the identifiers are double-quoted, as the exact case must match the description of the objects as they are referenced in Oracle (usually all upper case). In the contrary case where quoting is not used then this is not a problem, but it looks from the code that the quoting is enforced by java as it builds the query so you have no option but to match the case exactly.

Regards,

Eduard

Hi Eduard,

we tried both your suggestions:

Using 

Ojdbc7.jar.14112017 again instead of Ojdbc7.jar.28112017 did not help.

We also changed the alias in the java code to

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "issueshare")
.where("issueshare.ISSUE_ID = ?", issue.getId())
.order("issueshare.ID DESC")
);

but that didnt help either.

Do you have any more ideas?

I will try to find out what SQL was generated that is causing the error.

Eduard M Atlassian Team Dec 15, 2017

Hi Jens,

It would be worth a try to change the code to use all upper case for the tablename and check for a different result.

For the SQL generated, it's possible to enable SQL trace logging in Jira:

https://confluence.atlassian.com/jirakb/how-to-enable-detailed-sql-logging-in-jira-665224933.html

If possible do this also in the previous version where it worked, to see if there is any visible difference.

Hi Eduard,

i tried enabling the logs but i have the impression that SQL statements that fail to parse are not getting logged.

I see the exception in the jira log but not corresponding sql in the sql log, but the sql log contains lots of queries with a matching timestamp.

Cheers

Jens

We will try making the alias uppercase.

Using lowercase problem is that the alias is enclosed in double quotes in the order by clause:

fail.PNG

The query works once they are removed:

work.PNG

Cheers 

Jens

Can you post the code?

Hi Roy,

here you go

IssueShare[] foundResult = this.activeObjects.find(IssueShare.class, Query.select()
.alias(IssueShare.class, "ISSUESHARE") //alias must be uppercase as a workaround for an driver issue (PH-669)
.where("ISSUESHARE.ISSUE_ID = ?", issue.getId())
.order("ISSUESHARE.ID DESC")
);

 

kind regards

Jens

Suggest an answer

Log in or Sign up to
This widget could not be displayed.
TAGS

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you