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

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?

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

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

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.

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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,183 views 13 19
Join discussion

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
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot