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

Database persistence problems with Oracle and MySQL

zcc39r Apr 8, 2016 3:30 PM

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.

1. Re: Database persistence problems with Oracle and MySQL

hchiorean Apr 8, 2016 7:57 AM (in response to zcc39r)

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?

We could, but that requires some code changes to our subsystem. You can log an enhancement request if you like, but this is more of a cosmetic issue IMO, not a functional one.

The first exception is a logging bug which affects only Oracle, so feel free to log a JIRA. You should be able to work around it by setting create-on-start=false after the first time the table has been created.

The second one is caused by the fact that any datasources configured in Wildfly and used by ModeShape should not be JTA enabled because ModeShape has to be able to dictate how transactions take place. So you have to add the jta=false attribute to the <datasource>. I'll update our documentation to reflect this.
Actions
2. Re: Database persistence problems with Oracle and MySQL

zcc39r Apr 8, 2016 4:09 PM (in response to hchiorean)

Thank you, Horia! Your advices were helpful. I filed two issues: https://issues.jboss.org/browse/MODE-2591 and https://issues.jboss.org/browse/MODE-2592.
Actions

Go to original post