[#SQL-17] SQL Query causes sql: java.sql.SQLException

Confluence SQL Plugin

SQL Query causes sql: java.sql.SQLException

Created: 20/Apr/07 09:53 AM Updated: 16/May/07 02:13 PM

Component/s:

SQL macro

Affects Version/s:

3.2.1

Fix Version/s:

3.2.2

Time Tracking:

Not Specified

File Attachments:	1. atlassian-confluence.log (1.63 Mb) 2. sql-plugin-3.2.2-beta2.jar (96 kb) 3. sql-plugin-3.2.2-beta3.jar (96 kb)
Environment:	« Hide Database querying: Oracle 9i Database Confluence is attached to: PostgreSQL 8.2.3 Confluence Version: 2.4.4 OS: Solaris 10 on X86 platform Show » Database querying: Oracle 9i Database Confluence is attached to: PostgreSQL 8.2.3 Confluence Version: 2.4.4 OS: Solaris 10 on X86 platform

Labels:

Description

« Hide

The sql query: {sql:dataSource=DIT1}
select * from palfvin.docusp_users{sql}

causes:
sql: java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

Once this page is loaded all other pages with SQL queries give the same error until I restart confluence.

NOTE: The view that is being query'd calls upon other views/tables to get its data.
Here is its sql statement:

CREATE OR REPLACE FORCE VIEW "PALFVIN"."USERS_DOCUSP" ("EMPLOYEE_ID_NUMBER", "LOGIN_NAME", "FULL_NAME", "LNAME", "FNAME", "EMAIL_ADDRESS", "MAIL_STOP", "MANAGER", "INTERNAL_TYPE", "JOB_CLASS", "PCDT_ORG", "SECTION") AS
SELECT employee_id_number, u.login_name, u.full_name, u.lname, u.fname, u.email_address, u.mail_stop,
o.manager, internal_type, job_class,
docusp_relative_org(reporting_org) pcdt_org, section_from_org(reporting_org) section
FROM aradmin.user_x@ar_usr u,
aradmin.ORGANIZATION@ar_usr o
WHERE reporting_org LIKE '%/PCDT/%'
AND u.status = 0

AND reporting_org = o.PATH

All

Comments

Work Log

Change History

FishEye

Crucible

Related Builds

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Bob Swift - 28/Apr/07 07:06 AM

I think this is one of those areas where different databases behave differently. I am working on a code change that will solve this problem but will need you to verify it on your system prior to release.

[ Permlink | « Hide ]

Bob Swift - 29/Apr/07 11:33 AM

Please try this and see if it solves your problem.

[ Permlink | « Hide ]

Matt Klein - 30/Apr/07 06:53 AM

Bob,
The new jar file didn't work. The same problem shows up.

[ Permlink | « Hide ]

Bob Swift - 30/Apr/07 07:19 AM

Well this is going to be more difficult then. Here are some things to do.

Please add the following to the .../WEB-INF/classes/log4j.properties file:
```
log4j.logger.org.swift.confluence.table=DEBUG
```
Re-run the scenario and attach the log file to this issue

To see if you can get things back to normal after this error occurs:

Try running a "rollback" sql statement prior to other sql statements.

[ Permlink | « Hide ]

Matt Klein - 30/Apr/07 07:57 AM

Here is the log.

I attempted the rollback, but nothing happened. Did I do it right? Here is my SQL code for the other page that normally works:

{sql:dataSource=DIT1}
SQL ROLLBACK WORK{sql} {sql:dataSource=DIT1}
select news "News", get_proper_date_remedy(date_posted, 'mm/dd/yyyy') "Date Posted" from docusp.PCDT_NEWS where to_date(get_proper_date_remedy(date_posted, 'mm/dd/yyyy'),'mm/dd/yyyy') > sysdate-8{sql}

[ Permlink | « Hide ]

Bob Swift - 30/Apr/07 05:00 PM

Thanks for your patience on this. The log file helped a lot. I am testing a new fix and will post it soon. Unfortunately, I am not able to create the conditions that cause your problem, so will have to ask you to verify the change again!

[ Permlink | « Hide ]

Bob Swift - 30/Apr/07 05:02 PM

Another attempt to fix the problem.

[ Permlink | « Hide ]

Bob Swift - 30/Apr/07 05:50 PM

By the way, I noticed in your log that you are using macro-security and are experiencing the problem noted in a comment on this page: http://confluence.atlassian.com/display/CONFEXT/Macro+security

[ Permlink | « Hide ]

Matt Klein - 01/May/07 06:49 AM

Looks like the latest fix did the trick. Thank you for fixing this so fast. Also, thanks for pointing out the macro-security thing. I am going to take a look at that next.

[ Permlink | « Hide ]

Bob Swift - 01/May/07 04:40 PM

Good. Thanks for testing this out. I will release it soon and let you know. It should be same as what you have. Likely the security thing will be taken care of soon once plugins get upgraded to work on 2.4.x and above only. Don't like breaking compatibility with older releases, but it appears that there is no other choice right now.

[ Permlink | « Hide ]

James Mortimer - 02/May/07 02:32 PM

Hi Bob,
I am having the same error:

"sql: java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction"
your recent patch 3.2.2beta did not fix the error on our system
most of our databases work fine with your macro. This particular database is an oracle 'gateway' into a set of read-only flat text files, and is configured as non-transactional.
- SQLPLUS and other external -tools have no trouble access this gateway without special configuration.
- there are normal read/write tables in the schema aswell (not from the read-only flat files). We get the error no matter which table we query. In fact, we get there error even for an empty query. i.e. {sql:datasource=gateway}
unlike the first bug report, all other SQL queries work fine, just the ones against the gateway datasource fail

suggestions?

[ Permlink | « Hide ]

Bob Swift - 02/May/07 04:40 PM

Double check that you have the beta2 version. See comments above.

[ Permlink | « Hide ]

James Mortimer - 02/May/07 04:52 PM

It was the jar file entitled "sql-plugin-3.2.2-beta2.jar" which was attached to this page as of yesterday, May 1st at noon.

I have read this thread twice but cannot acertain to which comments in particular are you referring.

if it's regarding rollback, we're not having the problem of the non-transactional sql query interfering with other queries
if it's regarding the logs, then we can attempt to get the logs and attach to the page.

[ Permlink | « Hide ]

Bob Swift - 02/May/07 08:14 PM

James, sorry, I wasn't very clear. Yes, I meant the jar you mentioned and make sure it is the only one in ...WEB-INF/lib. And yes, please send me the log so I can see the call stack of the exception to see what is happening. Additional debug information is available if you add

log4j.logger.org.swift.confluence=DEBUG

[ Permlink | « Hide ]

James Mortimer - 03/May/07 11:46 AM

Bob, after server restart and confirming no SQL plugins were installed, a clean install of SQL 3.2.2-beta2 permitted a query of our non-transactional database on our dev server. I think the first beta didn't install correctly despite what the plugin loader replied as a success. (although I did confirm the version was 3.2.2 at the time).

So now we are looking forward to the next release so we can try this out in production mode!

Thanks! And sorry for the red herring.

[ Permlink | « Hide ]

James Mortimer - 04/May/07 09:19 AM

Using this beta2 to query the same non-transactional database, the results return very slowly. So I put it in a {cache} macro.

At first it works.

When I returned the next day, I get:

sql: java.sql.SQLException: OALL8 is in an inconsistent state select count from table@link

refresh IE and I get:

sql: java.sql.SQLException: Closed Connection select count from table@link

when I refresh the cache, I get (very slowly) my expected results.

when I refresh IE, I continue to get my expected results

So this error seems to only be the first time that I view the data. It's happened two days in a row. I'll continue to monitor and report back here if I can reproduce the error consistently.

[ Permlink | « Hide ]

Bob Swift - 06/May/07 12:29 PM

James, sorry to hear this is not working for you yet. Could you please test another change (beta3) that may solve the non-transactional database case? I don't have a test environment for this. You will have to use the transactionisolation parameter (see the notation guide) so the all the transaction code is avoided:
transactionIsolation=none

Note when this is installed, it should show Plugin Version: 3.2.2-beta3.
If this checks out with you, I will release this change.

Matt, this should not affect your positive results!

[ Permlink | « Hide ]

Bob Swift - 06/May/07 12:30 PM

James, forgot to say - if this doesn't work for your problem, please create a new issue and attach logs etc...

[ Permlink | « Hide ]

Bob Swift - 13/May/07 09:24 AM

Release 3.2.2-beta3 as 3.2.2

[ Permlink | « Hide ]

James Mortimer - 16/May/07 02:13 PM

3.2.2. seems to be working. will open new issues if problems return. thanks!