GateIn 3.6 with Joss AS 7 Oracle Integration problem

[anishantony]

Hi

I created my custom portal in GateIn 3.6 and it run perfectly when i use hsql db as database of jcr and idm tables. Then i changed my database from hsqldb to oracle. Here follows the steps of my configuration

1. Created a module in jboss for oracle driver (ojdbc6.jar)

2. Done the following lines of code in standalone.xml file (for adding oracle driver)

<datasource jndi-name="java:/jdbcidm_AnishImpl" pool-name="IDMAnishImplPortalDS" enabled="true" use-java-context="true">

                    <connection-url>jdbc:oracle:thin:localhost:1521:xe</connection-url>

                    <driver>oracle</driver>

                    <security>

                        <user-name>system</user-name>

                        <password>manager/password>

                    </security>

                </datasource>

                <datasource jndi-name="java:/jdbcjcr_AnishImpl" pool-name="JCRAnishImplPortalDS" enabled="true" use-java-context="true">

                    <connection-url>dbc:oracle:thin:localhost:1521:xe</connection-url>

                    <driver>oracle</driver>

                    <security>

                        <user-name>system</user-name>

                        <password>manager</password>

                    </security>

                </datasource>

                <drivers>

                <driver name="oracle" module="com.oracle"/>

                    <driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver>

                </drivers>

3. Cleared the work,tmp,log

But when i restart  my GateIn server it will create JCR-IDM tables. But shows some exception on the insertion of data into corresponding tables. Here follows the exception

17:00:38,790 ERROR [exo.jcr.component.core.JDBCWorkspaceDataContainer] (MSC service thread 1-7) Error of init db org.exoplatform.services.jcr.impl.util.jdbc.DBInitializerException: Could not create the DB schema of the workspace 'anishimpl-ws'. Reason: ORA-00972: identifier is too long

; ORA-00972: identifier is too long

; ORA-00972: identifier is too long

. Last command: CREATE UNIQUE INDEX JCR_IDX_IANISHIMPLWS_PARENT_NAME ON JCR_IANISHIMPLWS(I_CLASS, PARENT_ID, NAME, I_INDEX, VERSION DESC): org.exoplatform.services.jcr.impl.util.jdbc.DBInitializerException: Could not create the DB schema of the workspace 'anishimpl-ws'. Reason: ORA-00972: identifier is too long

; ORA-00972: identifier is too long

; ORA-00972: identifier is too long

. Last command: CREATE UNIQUE INDEX JCR_IDX_IANISHIMPLWS_PARENT_NAME ON JCR_IANISHIMPLWS(I_CLASS, PARENT_ID, NAME, I_INDEX, VERSION DESC)

  at org.exoplatform.services.jcr.impl.util.jdbc.DBInitializer.init(DBInitializer.java:319) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer.initDatabase(CQJDBCWorkspaceDataContainer.java:190) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.storage.jdbc.JDBCWorkspaceDataContainer.<init>(JDBCWorkspaceDataContainer.java:326) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.storage.jdbc.optimisation.CQJDBCWorkspaceDataContainer.<init>(CQJDBCWorkspaceDataContainer.java:84) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.6.0_30]

  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) [rt.jar:1.6.0_30]

  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) [rt.jar:1.6.0_30]

  at java.lang.reflect.Constructor.newInstance(Constructor.java:513) [rt.jar:1.6.0_30]

  at org.exoplatform.container.ExoContainer.createComponent(ExoContainer.java:422) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.jmx.MX4JComponentAdapter.getComponentInstance(MX4JComponentAdapter.java:97) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.management.ManageableComponentAdapter.getComponentInstance(ManageableComponentAdapter.java:68) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getInstance(ConcurrentPicoContainer.java:468) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getComponentInstanceOfType(ConcurrentPicoContainer.java:422) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.CachingContainer.getComponentInstanceOfType(CachingContainer.java:139) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ExoContainer.createComponent(ExoContainer.java:412) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.jmx.MX4JComponentAdapter.getComponentInstance(MX4JComponentAdapter.java:97) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.management.ManageableComponentAdapter.getComponentInstance(ManageableComponentAdapter.java:68) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getInstance(ConcurrentPicoContainer.java:468) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getComponentInstanceOfType(ConcurrentPicoContainer.java:422) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.CachingContainer.getComponentInstanceOfType(CachingContainer.java:139) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ExoContainer.createComponent(ExoContainer.java:412) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.jmx.MX4JComponentAdapter.getComponentInstance(MX4JComponentAdapter.java:97) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.management.ManageableComponentAdapter.getComponentInstance(ManageableComponentAdapter.java:68) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getInstance(ConcurrentPicoContainer.java:468) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.getComponentInstanceOfType(ConcurrentPicoContainer.java:422) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.CachingContainer.getComponentInstanceOfType(CachingContainer.java:139) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer$2.run(RepositoryContainer.java:488) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer$2.run(RepositoryContainer.java:356) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.commons.utils.SecurityHelper.doPrivilegedExceptionAction(SecurityHelper.java:310) [exo.kernel.commons-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer.registerWorkspace(RepositoryContainer.java:355) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer.registerWorkspacesComponents(RepositoryContainer.java:817) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer.registerComponents(RepositoryContainer.java:719) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer.access$300(RepositoryContainer.java:98) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer$1.run(RepositoryContainer.java:166) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer$1.run(RepositoryContainer.java:158) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.commons.utils.SecurityHelper.doPrivilegedExceptionAction(SecurityHelper.java:310) [exo.kernel.commons-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryContainer.<init>(RepositoryContainer.java:157) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryServiceImpl.createRepository(RepositoryServiceImpl.java:138) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryServiceImpl.init(RepositoryServiceImpl.java:323) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.services.jcr.impl.RepositoryServiceImpl.start(RepositoryServiceImpl.java:279) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.6.0_30]

  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [rt.jar:1.6.0_30]

  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_30]

  at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_30]

  at org.exoplatform.container.LifecycleVisitor.traverse(LifecycleVisitor.java:100) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.LifecycleVisitor.start(LifecycleVisitor.java:170) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ConcurrentPicoContainer.start(ConcurrentPicoContainer.java:554) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ExoContainer.start(ExoContainer.java:270) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.PortalContainer.start(PortalContainer.java:679) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.ExoContainer.start(ExoContainer.java:258) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.RootContainer.createPortalContainer(RootContainer.java:678) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.container.RootContainer.createPortalContainers(RootContainer.java:343) [exo.kernel.container-2.4.3-GA.jar:2.4.3-GA]

  at org.gatein.integration.jboss.as7.web.StartupService.start(StartupService.java:45)

  at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1811)

  at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1746)

  at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [rt.jar:1.6.0_30]

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [rt.jar:1.6.0_30]

  at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_30]

Caused by: java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)

  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)

  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)

  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)

  at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)

  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)

  at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1838)

  at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1803)

  at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:294)

  at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:371)

  at org.exoplatform.services.jcr.impl.util.jdbc.DBInitializer$2.run(DBInitializer.java:279) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  at org.exoplatform.commons.utils.SecurityHelper.doPrivilegedExceptionAction(SecurityHelper.java:310) [exo.kernel.commons-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.commons.utils.SecurityHelper.doPrivilegedSQLExceptionAction(SecurityHelper.java:121) [exo.kernel.commons-2.4.3-GA.jar:2.4.3-GA]

  at org.exoplatform.services.jcr.impl.util.jdbc.DBInitializer.init(DBInitializer.java:275) [exo.jcr.component.core-1.15.3-GA.jar:1.15.3-GA]

  ... 57 more

17:00:38,840 INFO  [exo.jcr.component.core.ExoJBossCacheFactory] (MSC service thread 1-7) JBoss Cache configuration used: war:/conf/jcr/jbosscache/local/config.xml

17:00:38,862 INFO  [exo.jcr.component.core.ExoJBossCacheFactory] (MSC service thread 1-7) Custom JGroups configuration set:vfs:/F:/Gatein3.6/GateIn-3.6.0.Final-jbossas7/gatein/gatein.ear/portal.war/WEB-INF/classes/jgroups/gatein-udp.xml

17:00:39,732 ERROR [exo.jcr.component.core.RepositoryContainer] (MSC service thread 1-7) Repository error: org.exoplatform.services.jcr.impl.storage.JCRInvalidItemStateException: [anishimpl-ws] ADD NODE. Parent not found. Item []:1, ID: 00exo0jcr0root0uuid0000000000000, ParentID: null. Cause >>>> ORA-02291: integrity constraint (BMITTHRESHOLD.JCR_FK_IANISHIMPLWS_PARENT) violated - parent key not found

: ORA-02291: integrity constraint (BMITTHRESHOLD.JCR_FK_IANISHIMPLWS_PARENT) violated - parent key not found

: ORA-02291: integrity constraint (BMITTHRESHOLD.JCR_FK_IANISHIMPLWS_PARENT) violated - parent key not found.....etc

I am using Oracle 10g

[nfilotto]

Hi,

This is due to the fact that eXo JCR in isolated mode (which means that each workspace will have its own tables within the same db schema) relies by default on the name of the workspace to define the name of the tables and indexes. To workaround that you need to define a new property called db-tablename-suffix at container level in your workspace configuration, to provide a shorter name that will be supported by Oracle, If I'm not mistaken the limit size of name in Oracle is 30 characters and in hsqldb there is no limit that's why you don't meet this issue.

In this example (https://github.com/gatein/gatein-portal/blob/3.6.0.Final/web/portal/src/main/webapp/WEB-INF/conf/jcr/repository-configuration.xml#L117), eXo JCR will use "PSYSTEM" instead of the name of the workspace.