Syncope
  1. Syncope
  2. SYNCOPE-59

ERROR org.syncope.core.init.JobInstanceLoader - While loading NotificationJob instance - ORA-01002: fetch out of sequence

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 1.0.0-incubating
    • Fix Version/s: None
    • Component/s: core
    • Labels:
    • Environment:
      Oracle 10g, Linux x86_64, OCI

      Description

      Stracktrace Error on startup:

      15:51:05.908 ERROR org.syncope.core.init.JobInstanceLoader - While loading NotificationJob instance
      org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ORA-01002: fetch out of sequence

      {prepstmnt 155276228 SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = ? FOR UPDATE [params=?]}

      [code=1002, state=72000]
      at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:109) ~[quartz-1.8.6.jar:na]
      at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112) ~[quartz-1.8.6.jar:na]
      at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:235) ~[quartz-1.8.6.jar:na]
      at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInLock(JobStoreSupport.java:3712) ~[quartz-1.8.6.jar:na]
      at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJobAndTrigger(JobStoreSupport.java:1059) ~[quartz-1.8.6.jar:na]
      at org.quartz.core.QuartzScheduler.scheduleJob(QuartzScheduler.java:822) ~[quartz-1.8.6.jar:na]
      at org.quartz.impl.StdScheduler.scheduleJob(StdScheduler.java:243) ~[quartz-1.8.6.jar:na]
      at org.syncope.core.init.JobInstanceLoader.registerJob(JobInstanceLoader.java:134) ~[JobInstanceLoader.class:na]
      at org.syncope.core.init.JobInstanceLoader.registerJob(JobInstanceLoader.java:162) ~[JobInstanceLoader.class:na]
      at org.syncope.core.init.JobInstanceLoader.load(JobInstanceLoader.java:210) ~[JobInstanceLoader.class:na]
      at org.syncope.core.init.JobInstanceLoader$$FastClassByCGLIB$$edd08387.invoke(<generated>) [cglib-2.2.2.jar:na]
      at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [cglib-2.2.2.jar:na]

        Issue Links

          Activity

          Hide
          Denis Signoretto added a comment -

          Searching on Oracle Metalink, the issues it's related to a know Oracle JDBC and OCI Bug.

          As reported in the Oracle Note 1160014.1:

          • Applies to: JDBC - Version: 10.1.0.3.0 and later [Release: and later ] When executing a SELECT FOR UPDATE through the OCI JDBC driver with the 'Auto Commit' enabled, following error is reported: java.sql.SQLException: ORA-01002: fetch out of sequence. The same problem is not seen using Oracle Thin drivers.
          • Solution

          An internal bug exists for this issue and it has been fixed in JDBC 11g.

          There are multiple solutions to this issue:

          a) Use the 11g version of JDBC-OCI.
          Install an Oracle 11g client. Either 11.1.0.7.0 or above should be fine. Make sure to install this into a seperate ORACLE_HOME and not in the same home as the 10g client.

          According to the below note:
          Note 207303.1 - Client / Server / Interoperability Support Between Different Oracle Versions
          11g clients supported/certified against 10g database if database has a minimum version of 10.2.0.2.

          b) Use Oracle JDBC Thin Driver 10.2.0.1 or above.

          c) If you do not wish to upgrade to the 11g drivers or if you do not want to change to thin drivers, raise a SR with Oracle Software Support and request for a one-off patch for the version of JDBC OCI driver that you are using.

          Show
          Denis Signoretto added a comment - Searching on Oracle Metalink, the issues it's related to a know Oracle JDBC and OCI Bug. As reported in the Oracle Note 1160014.1: Applies to: JDBC - Version: 10.1.0.3.0 and later [Release: and later ] When executing a SELECT FOR UPDATE through the OCI JDBC driver with the 'Auto Commit' enabled, following error is reported: java.sql.SQLException: ORA-01002: fetch out of sequence. The same problem is not seen using Oracle Thin drivers. Solution An internal bug exists for this issue and it has been fixed in JDBC 11g. There are multiple solutions to this issue: a) Use the 11g version of JDBC-OCI. Install an Oracle 11g client. Either 11.1.0.7.0 or above should be fine. Make sure to install this into a seperate ORACLE_HOME and not in the same home as the 10g client. According to the below note: Note 207303.1 - Client / Server / Interoperability Support Between Different Oracle Versions 11g clients supported/certified against 10g database if database has a minimum version of 10.2.0.2. b) Use Oracle JDBC Thin Driver 10.2.0.1 or above. c) If you do not wish to upgrade to the 11g drivers or if you do not want to change to thin drivers, raise a SR with Oracle Software Support and request for a one-off patch for the version of JDBC OCI driver that you are using.
          Hide
          Francesco Chicchiriccò added a comment -

          Bulk closing for 1.0.0-RC1-incubating

          Show
          Francesco Chicchiriccò added a comment - Bulk closing for 1.0.0-RC1-incubating
          Hide
          Francesco Chicchiriccò added a comment -

          Syncope has been verified to be working with OCI driver 11 on Oracle 11g.

          Show
          Francesco Chicchiriccò added a comment - Syncope has been verified to be working with OCI driver 11 on Oracle 11g.
          Hide
          Denis Signoretto added a comment -

          Hi Fabio,

          the error disappear using Thin Client. I tried with different jdbc connection url but every time we use OCI the problem occurs.

          So at the moment, the a possible workaround is avoid using OCI driver.

          Show
          Denis Signoretto added a comment - Hi Fabio, the error disappear using Thin Client. I tried with different jdbc connection url but every time we use OCI the problem occurs. So at the moment, the a possible workaround is avoid using OCI driver.
          Hide
          fabio martelli added a comment -

          Hi Denis, any progress about this issue?
          I cannot reproduce the problem by using oracle 11g as the backend.

          Please, let me have a feedback asap.

          Regartds,
          F.

          Show
          fabio martelli added a comment - Hi Denis, any progress about this issue? I cannot reproduce the problem by using oracle 11g as the backend. Please, let me have a feedback asap. Regartds, F.
          Hide
          fabio martelli added a comment -

          Hi Denis,
          I cannot reproduce the problem.

          I made my check by using oracle 11 and the relative instant client.

          My datasource configuration is the following

          <Resource name="jdbc/syncopeDataSource" auth="Container" type="javax.sql.DataSource"^M
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true"^M
          testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" validationInterval="30000"^M
          maxActive="150" minIdle="2" maxWait="10000" initialSize="2" removeAbandonedTimeout="10"^M
          removeAbandoned="true" logAbandoned="true" suspectTimeout="10"^M
          timeBetweenEvictionRunsMillis="5000" minEvictableIdleTimeMillis="5000"^M
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"^M
          username="syncope" password="syncope" driverClassName="oracle.jdbc.OracleDriver"^M
          url="jdbc:oracle:oci:@pioppo:1521:orcl" />

          Probably, apart the oracle version, the difference between my configuration and yours in into the jdbc url. Did you use a tnsnames.ora file for your connection?
          Could you try with a "legacy" jdbc url?

          Cheers,
          F.

          Show
          fabio martelli added a comment - Hi Denis, I cannot reproduce the problem. I made my check by using oracle 11 and the relative instant client. My datasource configuration is the following <Resource name="jdbc/syncopeDataSource" auth="Container" type="javax.sql.DataSource"^M factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true"^M testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" validationInterval="30000"^M maxActive="150" minIdle="2" maxWait="10000" initialSize="2" removeAbandonedTimeout="10"^M removeAbandoned="true" logAbandoned="true" suspectTimeout="10"^M timeBetweenEvictionRunsMillis="5000" minEvictableIdleTimeMillis="5000"^M jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"^M username="syncope" password="syncope" driverClassName="oracle.jdbc.OracleDriver"^M url="jdbc:oracle:oci:@pioppo:1521:orcl" /> Probably, apart the oracle version, the difference between my configuration and yours in into the jdbc url. Did you use a tnsnames.ora file for your connection? Could you try with a "legacy" jdbc url? Cheers, F.
          Hide
          Denis Signoretto added a comment -

          Trying more, the problem only occurs using OCI driver:

          Our current release is: Oracle JDBC Driver version - "10.2.0.5.0"

          Bye,
          Denis.

          ssian.jira.plugin.system.issuetabpanels:comment-> tabpanel&focusedCommentId=13256389#comment-13256389 ]
          fault.jspa
          For more information on JIRA, see: http://www.atlassian.com/software/jira

          Show
          Denis Signoretto added a comment - Trying more, the problem only occurs using OCI driver: Our current release is: Oracle JDBC Driver version - "10.2.0.5.0" Bye, Denis. ssian.jira.plugin.system.issuetabpanels:comment-> tabpanel&focusedCommentId=13256389#comment-13256389 ] fault.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
          Hide
          Denis Signoretto added a comment -

          Hi Fabio,

          I tryed various combinations and I found that using thin client and excluding
          usage of jdbc/syncopeDataSource connection pool the error disappear.

          Are you able to reproduce the error using OCI and jndi Connection Pool?

          Thanks,
          Denis.

          va.lang.Exception

          Show
          Denis Signoretto added a comment - Hi Fabio, I tryed various combinations and I found that using thin client and excluding usage of jdbc/syncopeDataSource connection pool the error disappear. Are you able to reproduce the error using OCI and jndi Connection Pool? Thanks, Denis. va.lang.Exception
          Hide
          Denis Signoretto added a comment -

          Hi Fabio,

          for completeness, we are using:

          as Database: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production,
          and we are accessing to DB with this settings (by OCI Driver).

          <Resource name="jdbc/syncopeDataSource" auth="Container" type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true"
          testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" validationInterval="30000"
          maxActive="150" minIdle="2" maxWait="10000" initialSize="2" removeAbandonedTimeout="10"
          removeAbandoned="true" logAbandoned="true" suspectTimeout="10"
          timeBetweenEvictionRunsMillis="5000" minEvictableIdleTimeMillis="5000"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
          username="xxxxx" password="xxxxx" driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:oci:@ORACLE" />

          Do you think, we should try excluding connection pool and switching to thin client (without Oracle OCI driver)?
          What Oracle configuration are you using?

          Thanks,
          Denis.

          P.S. Further more at startup we have a warning on connection pool abandoned (probably it doesn't matter)

          Apr 18, 2012 10:55:26 AM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
          WARNING: Connection has been abandoned PooledConnection[oracle.jdbc.driver.T2CConnection@305bf465]:java.lang.Exception
          at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1006)
          at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:735)
          at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:591)
          at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:179)
          at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:124)
          at ch.qos.logback.core.db.JNDIConnectionSource.getConnection(JNDIConnectionSource.java:69)

          fault.jspa

          Show
          Denis Signoretto added a comment - Hi Fabio, for completeness, we are using: as Database: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production, and we are accessing to DB with this settings (by OCI Driver). <Resource name="jdbc/syncopeDataSource" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true" testOnBorrow="true" testOnReturn="true" validationQuery="SELECT 1 FROM DUAL" validationInterval="30000" maxActive="150" minIdle="2" maxWait="10000" initialSize="2" removeAbandonedTimeout="10" removeAbandoned="true" logAbandoned="true" suspectTimeout="10" timeBetweenEvictionRunsMillis="5000" minEvictableIdleTimeMillis="5000" jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" username="xxxxx" password="xxxxx" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:oci:@ORACLE" /> Do you think, we should try excluding connection pool and switching to thin client (without Oracle OCI driver)? What Oracle configuration are you using? Thanks, Denis. P.S. Further more at startup we have a warning on connection pool abandoned (probably it doesn't matter) Apr 18, 2012 10:55:26 AM org.apache.tomcat.jdbc.pool.ConnectionPool abandon WARNING: Connection has been abandoned PooledConnection [oracle.jdbc.driver.T2CConnection@305bf465] :java.lang.Exception at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1006) at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:735) at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:591) at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:179) at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:124) at ch.qos.logback.core.db.JNDIConnectionSource.getConnection(JNDIConnectionSource.java:69) fault.jspa
          Hide
          fabio martelli added a comment -

          Hi Denis,
          I've just tried to reproduce the problem.

          Following the steps performed:
          1. create a new project [1]
          2. change to use oracle ad backend [2]
          3. deploy syncope and syncope-console
          4. start container
          5. shutdown and startup again

          The problem has not been reproduced.
          Please, let me know if I missed some steps.

          Regards,
          F.

          [1] https://cwiki.apache.org/confluence/display/SYNCOPE/Create+a+new+Syncope+project
          [2] https://cwiki.apache.org/confluence/display/SYNCOPE/Run+Syncope+in+real+environments#RunSyncopeinrealenvironments-Oracle

          Show
          fabio martelli added a comment - Hi Denis, I've just tried to reproduce the problem. Following the steps performed: 1. create a new project [1] 2. change to use oracle ad backend [2] 3. deploy syncope and syncope-console 4. start container 5. shutdown and startup again The problem has not been reproduced. Please, let me know if I missed some steps. Regards, F. [1] https://cwiki.apache.org/confluence/display/SYNCOPE/Create+a+new+Syncope+project [2] https://cwiki.apache.org/confluence/display/SYNCOPE/Run+Syncope+in+real+environments#RunSyncopeinrealenvironments-Oracle

            People

            • Assignee:
              fabio martelli
              Reporter:
              Denis Signoretto
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development