search

rbeckman-nextgen / test-mc3

0 stars 0 forks source link

Timezone region not found when using Oracle #3376

Open rbeckman-nextgen opened 4 years ago

rbeckman-nextgen commented 4 years ago

The following exception occurs

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1ORA-01882: timezone region  not found

Possibly due to us updating the JDBC jar in MIRTH-3444

A workaround seems to be adding a vmoption http://stackoverflow.com/questions/9156379/ora-01882-timezone-region-not-found

Seems to occur when connecting to older Oracle instances. We might do nothing or we might downgrade the jar, but we should investigate the issue a little more.

Imported Issue. Original Details: Jira Issue Key: MIRTH-3499 Reporter: wayneh Created: 2014-10-22T16:45:02.000-0700

rbeckman-nextgen commented 4 years ago

I tested this with a few different versions, including 10gR2 (the earliest version we officially support), and didn't get any errors. It's likely dependent on the underlying system time zone and the specific version of Java used. The reason this happens is because the JDBC driver reports the time zone supplied by the JVM, which may be the long-description version (like "America/Los_Angeles"). If the database doesn't recognize the time zone (like if it only accepts GMT offset notations), then it will throw that error.

The workaround is to add the following to mcservice.vmoptions (or mcserver.vmoptions):

-Doracle.jdbc.timezoneAsRegion=false

Imported Comment. Original Details: Author: narupley Created: 2014-10-22T17:40:23.000-0700

rbeckman-nextgen commented 4 years ago

Reported versions where this has occurred so far

9i (not officially supported) 11.2.0.3.0 (not yet reproduced)

Imported Comment. Original Details: Author: wayneh Created: 2014-10-22T17:52:34.000-0700

rbeckman-nextgen commented 4 years ago

Although adding the line to the vmoptions file corrected the original error, I have been unable to pull any data from the Oracle 9i database that I was connecting to previously. Looks like the only option here is to downgrade the jar file.

Imported Comment. Original Details: Author: seaston Created: 2014-10-23T04:25:34.000-0700

rbeckman-nextgen commented 4 years ago

The old driver can be found on Oracle's site, or on our SVN repository: [https://svn.mirthcorp.com/connect/tags/3.0.3/server/lib/database/ojdbc6.jar]

Imported Comment. Original Details: Author: narupley Created: 2014-10-23T05:02:42.000-0700

rbeckman-nextgen commented 4 years ago

Is upgrading the database an option? Officially we don't support 9i anymore.

Imported Comment. Original Details: Author: wayneh Created: 2014-10-23T11:01:00.000-0700

rbeckman-nextgen commented 4 years ago

Upgradng the database is an option, but not one that I am able to control easily. The system I am connecting to is not mine - it is not mirthdb that is on Oracle but a Radiology sytem running on Oracle 9i. I believe a new server has been ordered for this system, but in the short term I need to be able to continue to run queries on the 9i database so have replaced the jar file in the server-lib/database folder.

Imported Comment. Original Details: Author: seaston Created: 2014-10-23T11:06:56.000-0700

rbeckman-nextgen commented 4 years ago

Please note now that 3.1.1 is out. If you upgrade from 3.1.0 to 3.1.1, you will need to replace the jar again.

Rather than just reverting the jar ourselves, we'd like to provide a better mechanism for allowing channels to use older jars while still allowing us to update the ones we use internally. We hope that we will be seeing this functionality materialize sooner rather than later.

Imported Comment. Original Details: Author: wayneh Created: 2014-11-12T11:36:20.000-0800

rbeckman-nextgen commented 4 years ago

How to replace the jar file in Mirth Appliance?

Imported Comment. Original Details: Author: vchacon Created: 2015-04-28T10:03:08.000-0700