2 Replies Latest reply on Apr 8, 2016 4:09 PM by Rustam Usmanov

    Database persistence problems with Oracle and MySQL

    Rustam Usmanov Apprentice

      Having WildFly 10.0.0, ModeShape 5.0.0, Oracle 11g, MySQL 5.0.95 I tried to configure database persistent repositories and encountered the following problems:

       

      1. Oracle

      I defined repository as:

      <repository name="ora">
        <db-persistence datasource-jndi="java:jboss/datasources/ora"/>
        <db-binary-storage data-source-jndi-name="java:jboss/datasources/ora"/>
      </repository>
      
      
      
      

       

      Note that one concept of datasource JNDI name has different names in defferent contexts. Was it made intentionally? May be it's better to have one attribute name for one concept?

       

      and datasource as:

      <datasource jta="false" jndi-name="java:jboss/datasources/ora" pool-name="mdsp" enabled="true" use-ccm="false">
        <connection-url>jdbc:oracle:thin:@host:1521/ORA</connection-url>
        <driver-class>oracle.jdbc.OracleDriver</driver-class>
        <driver>ojdbc6.jar</driver>
        <security>
          <user-name>user</user-name>
          <password>password</password>
        </security>
        <validation>
          <validate-on-match>false</validate-on-match>
          <background-validation>false</background-validation>
        </validation>
        <statement>
          <share-prepared-statements>false</share-prepared-statements>
        </statement>
      </datasource>
      
      
      
      

       

      After WildFly start tables MODESHAPE_REPOSITORY and CONTENT_STORE were created and I can use repository as usual without problems till WildFly restart.

      After restart It's impossible to obtain a JCR session:

      12:47:46,871 ERROR [stderr] (ServerService Thread Pool -- 75) javax.jcr.RepositoryException: Error while starting 'ora' repository: 1 parameter supplied, but 2 parameters required: "Ignoring Oracle SQL exception for database {0} with error code {1}" => "Ignoring Oracle SQL exception for database 955 with error code {1}"

      12:47:46,872 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:634)

      12:47:46,872 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:598)

      12:47:46,872 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:135)

      ...

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75) Caused by: java.lang.IllegalArgumentException: 1 parameter supplied, but 2 parameters required: "Ignoring Oracle SQL exception for database {0} with error code {1}" => "Ignoring Oracle SQL exception for database 955 with error code {1}"

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.common.util.StringUtil.createString(StringUtil.java:188)

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.common.logging.slf4j.SLF4JLoggerImpl.debug(SLF4JLoggerImpl.java:126)

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.persistence.relational.OracleStatements.createTable(OracleStatements.java:45)

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.persistence.relational.RelationalDb.runWithConnection(RelationalDb.java:358)

      12:47:46,886 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.persistence.relational.RelationalDb.start(RelationalDb.java:85)

      12:47:46,887 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository$RunningState.<init>(JcrRepository.java:1064)

      12:47:46,887 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository$RunningState.<init>(JcrRepository.java:936)

      12:47:46,887 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository.doStart(JcrRepository.java:367)

      12:47:46,887 ERROR [stderr] (ServerService Thread Pool -- 75)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:632)

      12:47:46,887 ERROR [stderr] (ServerService Thread Pool -- 75)  ... 66 more

      Looking at SQL statements passed to the Oracle shows that there is an attempt to create tables mentioned above with ORA-00955 name is already used by an existing object Oracle response.

       

       

      2. MySQL

      I defined repository as:

      <repository name="mysql">
        <db-persistence datasource-jndi="java:jboss/datasources/mysql"/>
        <db-binary-storage data-source-jndi-name="java:jboss/datasources/mysql"/>
      </repository>
      
      
      
      

       

      and datasource as:

      <datasource jta="true" jndi-name="java:jboss/datasources/mysql" pool-name="ill" enabled="true" use-ccm="true">
        <connection-url>jdbc:mysql://host/db</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <driver>mysql-connector-java-5.1.34-bin.jar_com.mysql.jdbc.Driver_5_1</driver>
        <new-connection-sql>set autocommit=0</new-connection-sql>
        <security>
          <user-name>user</user-name>
          <password>password</password>
        </security>
        <validation>
          <validate-on-match>false</validate-on-match>
          <background-validation>false</background-validation>
        </validation>
        <timeout>
          <set-tx-query-timeout>false</set-tx-query-timeout>
          <blocking-timeout-millis>0</blocking-timeout-millis>
          <idle-timeout-minutes>0</idle-timeout-minutes>
          <query-timeout>0</query-timeout>
          <use-try-lock>0</use-try-lock>
          <allocation-retry>0</allocation-retry>
          <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
        </timeout>
        <statement>
          <share-prepared-statements>false</share-prepared-statements>
        </statement>
      </datasource>
      
      
      

      After WildFly start it's impossible to obtain a JCR session:

      13:12:36,690 ERROR [stderr] (ServerService Thread Pool -- 66) javax.jcr.RepositoryException: Error while starting 'mysql' repository: java.sql.SQLException: IJ031017: You cannot set autocommit during a managed transaction

      13:12:36,693 ERROR [stderr] (ServerService Thread Pool -- 66)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:634)

      13:12:36,712 ERROR [stderr] (ServerService Thread Pool -- 66)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:598)

      13:12:36,713 ERROR [stderr] (ServerService Thread Pool -- 66)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:135)

      ...

      13:15:41,653 ERROR [stderr] (ServerService Thread Pool -- 67) Caused by: org.modeshape.persistence.relational.RelationalProviderException: java.sql.SQLException: IJ031017: You cannot set autocommit during a managed transaction

      13:15:41,653 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.DataSourceManager.newConnection(DataSourceManager.java:123)

      13:15:41,653 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.DataSourceManager.<init>(DataSourceManager.java:58)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.RelationalDb.<init>(RelationalDb.java:62)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.RelationalProvider.getDB(RelationalProvider.java:34)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.RelationalProvider.getDB(RelationalProvider.java:27)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.schematic.Schematic.getDbFromProvider(Schematic.java:91)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.schematic.Schematic.lambda$getDb$9(Schematic.java:74)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.Spliterators$IteratorSpliterator.tryAdvance(Spliterators.java:1812)

      13:15:41,654 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.ReferencePipeline.forEachWithCancel(ReferencePipeline.java:126)

      13:15:41,655 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:498)

      13:15:41,655 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:485)

      13:15:41,655 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)

      13:15:41,655 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.FindOps$FindOp.evaluateSequential(FindOps.java:152)

      13:15:41,660 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)

      13:15:41,663 ERROR [stderr] (ServerService Thread Pool -- 67)  at java.util.stream.ReferencePipeline.findFirst(ReferencePipeline.java:464)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.schematic.Schematic.getDb(Schematic.java:76)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.jcr.Environment.getDb(Environment.java:59)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.jcr.JcrRepository$RunningState.<init>(JcrRepository.java:1034)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.jcr.JcrRepository$RunningState.<init>(JcrRepository.java:936)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.jcr.JcrRepository.doStart(JcrRepository.java:367)

      13:15:41,664 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.jcr.JcrRepository.login(JcrRepository.java:632)

      ...

      13:15:41,665 ERROR [stderr] (ServerService Thread Pool -- 67) Caused by: java.sql.SQLException: IJ031017: You cannot set autocommit during a managed transaction

      13:15:41,665 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.setJdbcAutoCommit(BaseWrapperManagedConnection.java:994)

      13:15:41,665 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.jboss.jca.adapters.jdbc.WrappedConnection.setAutoCommit(WrappedConnection.java:787)

      13:15:41,665 ERROR [stderr] (ServerService Thread Pool -- 67)  at org.modeshape.persistence.relational.DataSourceManager.newConnection(DataSourceManager.java:118)

      ...

      Did I miss something in my configuration?

      I run my code as EJB with CMT. But same results could be obtained with ModeShape Explorer. The only difference is  that in MySQL case tables were created.