8 Replies Latest reply on May 22, 2012 12:27 PM by rhauch

    SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3

    lmika

      Hi,

       

      I am using Modeshape 2.8.0.Final within a WAR file which is being deployed to JBoss AS 7.1.1 Final.  Modeshape is configured to use a JDBC connection retrieved by JNDI.  On startup, the container does not log any errors or stacktraces but once I try to add new nodes to the repository, I get the following exception:

       

      {noformat}

      11:33:53,171 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] (JCR testSource-3-thread-2) Could not synchronize database state with session: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1175) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1251) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:269) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:461) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:265) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.map.MapRequestProcessor.getTargetNode(MapRequestProcessor.java:616) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.map.MapRequestProcessor.process(MapRequestProcessor.java:382) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:315) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:363) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:235) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaConnection.execute(SimpleJpaConnection.java:133) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.RepositoryConnectionPool$ConnectionWrapper.execute(RepositoryConnectionPool.java:1124) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:213) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:202) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_29]

                at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_29]

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

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

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

      Caused by: java.sql.BatchUpdateException: Batch entry 0 /* insert org.modeshape.connector.store.jpa.model.simple.NodeEntity */ insert into MODE_SIMPLE_NODE (ALLOWS_SNS, CHILD_NAME_LOCAL, CHILD_NAME_NS_ID, COMPRESSED, DATA, CHILD_INDEX, NODE_UUID, PARENT_ID, NUM_PROPS, ENFORCEREFINTEG, SNS_INDEX, WORKSPACE_ID, ID) values ('0', 'patient', '5', NULL, '<stream of 260 bytes>', '0', '34aafcb4-d020-4923-9ffd-a3598bd86c7f', '2', '3', '0', '1', '1', '6') was aborted.  Call getNextException to see the cause.

                at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2619)

                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)

                at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2752)

                at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:708)

                at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1072)

                at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                ... 24 more

       

       

      11:33:53,191 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/JcrServer].[au.com.infomedix.jcrloadtest.jcrserver.restapi.JcrLoadTestApplication]] (http--127.0.0.1-8080-1) Servlet.service() for servlet au.com.infomedix.jcrloadtest.jcrserver.restapi.JcrLoadTestApplication threw exception: org.jboss.resteasy.spi.UnhandledException: java.lang.RuntimeException: Could not perform operation over the repository

                at org.jboss.resteasy.core.SynchronousDispatcher.handleApplicationException(SynchronousDispatcher.java:340) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.handleException(SynchronousDispatcher.java:214) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.handleInvokerException(SynchronousDispatcher.java:190) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:540) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:502) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:119) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:208) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:50) [resteasy-jaxrs-2.3.2.Final.jar:]

                at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]

                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]

                at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]

                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]

                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]

                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]

                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]

                at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]

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

      Caused by: java.lang.RuntimeException: Could not perform operation over the repository

                at au.com.infomedix.jcrloadtest.jcrserver.repo.JcrService.executeCommand(JcrService.java:92) [classes:]

                at au.com.infomedix.jcrloadtest.jcrserver.restapi.NodeCrudMethods.create(NodeCrudMethods.java:53) [classes:]

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

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

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

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

                at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:155) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:257) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:222) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:211) [resteasy-jaxrs-2.3.2.Final.jar:]

                at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:525) [resteasy-jaxrs-2.3.2.Final.jar:]

                ... 19 more

      Caused by: javax.jcr.RepositoryException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

                at org.modeshape.jcr.SessionCache.save(SessionCache.java:482) [modeshape-jcr-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.jcr.JcrSession.save(JcrSession.java:1313) [modeshape-jcr-2.8.0.Final.jar:2.8.0.Final]

                at au.com.infomedix.jcrloadtest.jcrserver.operations.CreateNodeOperation.performOperation(CreateNodeOperation.java:55) [classes:]

                at au.com.infomedix.jcrloadtest.jcrserver.operations.CreateNodeOperation.performOperation(CreateNodeOperation.java:1) [classes:]

                at au.com.infomedix.jcrloadtest.jcrserver.repo.AbstractJcrOperation.execute(AbstractJcrOperation.java:44) [classes:]

                at au.com.infomedix.jcrloadtest.jcrserver.repo.JcrService.executeCommand(JcrService.java:84) [classes:]

                ... 29 more

      Caused by: org.modeshape.graph.connector.RepositorySourceException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

                at org.modeshape.graph.session.GraphSession.save(GraphSession.java:1052) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.jcr.SessionCache.save(SessionCache.java:476) [modeshape-jcr-2.8.0.Final.jar:2.8.0.Final]

                ... 34 more

      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

                at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:302) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:461) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:265) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.map.MapRequestProcessor.getTargetNode(MapRequestProcessor.java:616) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.map.MapRequestProcessor.process(MapRequestProcessor.java:382) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:315) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:363) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:235) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.connector.store.jpa.model.simple.SimpleJpaConnection.execute(SimpleJpaConnection.java:133) [modeshape-connector-store-jpa-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.connector.RepositoryConnectionPool$ConnectionWrapper.execute(RepositoryConnectionPool.java:1124) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:213) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:202) [modeshape-graph-2.8.0.Final.jar:2.8.0.Final]

                at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_29]

                at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_29]

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

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

                ... 1 more

      Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1175) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1251) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:269) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                ... 16 more

      Caused by: java.sql.BatchUpdateException: Batch entry 0 /* insert org.modeshape.connector.store.jpa.model.simple.NodeEntity */ insert into MODE_SIMPLE_NODE (ALLOWS_SNS, CHILD_NAME_LOCAL, CHILD_NAME_NS_ID, COMPRESSED, DATA, CHILD_INDEX, NODE_UUID, PARENT_ID, NUM_PROPS, ENFORCEREFINTEG, SNS_INDEX, WORKSPACE_ID, ID) values ('0', 'patient', '5', NULL, '<stream of 260 bytes>', '0', '34aafcb4-d020-4923-9ffd-a3598bd86c7f', '2', '3', '0', '1', '1', '6') was aborted.  Call getNextException to see the cause.

                at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2619)

                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)

                at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2752)

                at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeBatch(CachedPreparedStatement.java:708)

                at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1072)

                at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                ... 24 more

      {noformat}

       

      I have logged into Postgres from the console (using the same user JDBC is configured to use) and I have observerd that Modeshape was able to create the tables so it looks like it could connect

      to the database (and modify the schema).

       

      Has anyone else experienced this?  Is there are way I can turn off batch updates?

       

      Any assistance will be greatly appreciated.

       

      h4. Application Configuration

       

      | *Container* | JBoss AS 7.1.1. Final (standalone) |

      | *JDBC Driver* | postgresql-9.1-902.jdbc4.jar (included as a module) |

      | *Database Version* | PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.5.1 20101208 [gcc-4_5-branch revision 167585], 64-bit |

      | *Java* | java version "1.6.0_29" Java(TM) SE Runtime Environment (build 1.6.0_29-b11) Java HotSpot(TM) 64-Bit Server VM (build 20.4-b02, mixed mode) |

      | *OS* | openSUSE 11.4 "Celadon" - Linux linux-l00m.site 2.6.37.6-0.11-desktop #1 SMP PREEMPT 2011-12-19 23:39:38 +0100 x86_64 x86_64 x86_64 GNU/Linux |

       

      *Modeshape Configuration:*

       

      {code:xml}

      <?xml version="1.0" encoding="UTF-8"?>

      <configuration xmlns:mode="http://www.modeshape.org/1.0" xmlns:jcr="http://www.jcp.org/jcr/1.0">

        <!--

        Define the JCR repositories

        -->

        <mode:repositories>

            <!--

            Define a JCR repository that accesses the 'Cars' source directly.

            This of course is optional, since we could access the same content through 'vehicles'.

            -->

            <mode:repository jcr:name="testRepository" mode:source="testSource">

                <mode:descriptors>

                  <!--

                      This adds a JCR Repository descriptor named "myDescriptor" with a value of "foo".

                      So this code:

                      Repository repo = ...;

                      System.out.println(repo.getDescriptor("myDescriptor");

       

                      Will now print out "foo".

                  -->

                  <myDescriptor mode:value="foo" />

                </mode:descriptors>

                <!--

                      Import the custom node types defined in the named files. The values

                      can be an absolute path to a classpath resource, an absolute file system

                      path, a relative path on the file system (relative to where the process was

                      started from), or a resolvable URL. If more than one node type definition

                      file is needed, the files can be listed as a single comma-delimited string

                      in the 'mode:resource' attribute of the 'jcr:nodeTypes' element, or listed

                      individually using multiple mode:resource child elements (as shown below).

                  -->

                <jcr:nodeTypes>

                     <mode:resource>/jcr/schema.cnd</mode:resource>

                </jcr:nodeTypes>

            </mode:repository>

        </mode:repositories>

         <!--

         Define the sources for the content. These sources are directly accessible using the

         ModeShape-specific Graph API.

         -->

         <mode:sources jcr:primaryType="nt:unstructured">

         <!--

              In-memory source:

       

             <mode:source jcr:name="testSource"

                  mode:classname="org.modeshape.graph.connector.inmemory.InMemoryRepositorySource"

                  mode:defaultWorkspaceName="default"

                  mode:autoGenerateSchema="validate"/>

           -->

              <mode:source

                  jcr:name="testSource"

                  mode:dialect="org.hibernate.dialect.PostgreSQLDialect"

                  mode:classname="org.modeshape.connector.store.jpa.JpaSource"

                  mode:description="Database store"

                  mode:showSql="true"

                  mode:dataSourceJndiName="java:jboss/datasources/JcrDataDS"

                  mode:defaultWorkspaceName="default"

                  mode:autoGenerateSchema="create"/>

         </mode:sources>

         <!--

         Define the sequencers. This is an optional section. For this example, we're not using any sequencers.

         -->

         <mode:sequencers>

             <!--mode:sequencer jcr:name="Image Sequencer">

                 <mode:classname>

                  org.modeshape.sequencer.image.ImageMetadataSequencer

                 </mode:classname>

                 <mode:description>Image metadata sequencer</mode:description>

                 <mode:pathExpression>/foo/source => /foo/target</mode:pathExpression>

                 <mode:pathExpression>/bar/source => /bar/target</mode:pathExpression>

             </mode:sequencer-->

         </mode:sequencers>

         <!--

         <mode:mimeTypeDetectors>

             <mode:mimeTypeDetector jcr:name="Detector"

                                   mode:description="Standard extension-based MIME type detector"/>

         </mode:mimeTypeDetectors>

          -->

      </configuration>

      {code}

       

      *Data Store Configuration:*

      {code:xml}

      <subsystem xmlns="urn:jboss:domain:datasources:1.0">

                  <datasources>

                      <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                          <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>

                          <driver>h2</driver>

                          <security>

                              <user-name>sa</user-name>

                              <password>sa</password>

                          </security>

                      </datasource>

                      <datasource jndi-name="java:jboss/datasources/JcrDataDS" pool-name="JcrDataDS" enabled="true">

                          <connection-url>jdbc:postgresql:jcr_data</connection-url>

                          <driver>org.postgresql</driver>

                          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                          <pool>

                              <min-pool-size>10</min-pool-size>

                              <max-pool-size>100</max-pool-size>

                              <prefill>true</prefill>

                          </pool>

                          <security>

                              <user-name>jcr</user-name>

                              <!-- No password used -->

                          </security>

                          <statement>

                              <prepared-statement-cache-size>32</prepared-statement-cache-size>

                              <share-prepared-statements>true</share-prepared-statements>

                          </statement>

                      </datasource>

                      <drivers>

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

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

                          </driver>

                          <driver name="com.mysql" module="com.mysql">

                              <xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>

                          </driver>

                          <driver name="org.postgresql" module="org.postgresql">

                              <xa-datasource-class>org.postgresql.Driver</xa-datasource-class>

                          </driver>

                      </drivers>

                  </datasources>

              </subsystem>

      {code}

        • 1. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
          rhauch

          Leon:

           

          Are you using the Hibernate version that ships with AS7.1.1.Final, which ships with Hibernate Core 4.0.1.Final (and similar version of Entity Manager, etc.). ModeShape 2.8.0.Final was built to use Hibernate 3.5.2.Final (see our parent POM file). Obviously these are very different versions, and I suspect they're not playing well together.

           

          The good news is that AS7 allows you the ability to also add Hibernate 3.5.2 to the installation for use by ModeShape without affecting the rest of the AS7 functionality (assuming your application doesn't need both Hibernate 4 and ModeShape 2.8). Unfortunately, how you do this greatly depends on how you've packaged/installed ModeShape into AS7. If you'd like to explain more about how you did this, I'd be happy to try to help out.

          • 2. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
            lmika

            Hi Randall,

             

            Thanks for your prompt reply.

             

            The application is a WAR file which is using Modeshape 2.8.0 as a library dependency (as oppose to retrieving it from the container using JNDI), however Modeshape is configured to get the JDBC connection from the container.

             

            The project is built with Maven 3.0.4 using the following POM file:

             

            {code:xml}

            <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

              xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

              <modelVersion>4.0.0</modelVersion>

              <groupId>au.com.infomedix.jcrloadtest</groupId>

              <artifactId>JcrServer</artifactId>

              <packaging>war</packaging>

              <version>1.0-SNAPSHOT</version>

              <name>JcrServer Maven Webapp</name>

              <url>http://maven.apache.org</url>

              <parent>

                <groupId>au.com.infomedix.jcrloadtest</groupId>

                <artifactId>jcrloadtest-all</artifactId>     

                <version>1.0-SNAPSHOT</version>

                <relativePath>../pom.xml</relativePath>

              </parent>

              <dependencies>

                <!-- This dependency contains JaxB annotated POJO's used by this project and the LoadTestAgent -->  

                <dependency>

                  <groupId>au.com.infomedix.jcrloadtest</groupId>

                  <artifactId>JcrLoadTestCommon</artifactId>

                  <version>1.0-SNAPSHOT</version>

                </dependency>

                <dependency>

                  <groupId>commons-io</groupId>

                  <artifactId>commons-io</artifactId>

                  <version>2.1</version>

                </dependency>

                <dependency>

                  <groupId>org.apache.commons</groupId>

                  <artifactId>commons-lang3</artifactId>

                  <version>3.0.1</version>

                </dependency>  

                <dependency>

                  <groupId>commons-collections</groupId>

                  <artifactId>commons-collections</artifactId>

                  <version>3.2.1</version>

                </dependency>  

                      <dependency>

                          <groupId>commons-codec</groupId>

                          <artifactId>commons-codec</artifactId>

                          <version>1.4</version>

                      </dependency>  

             

                <dependency>

                  <groupId>org.modeshape</groupId>

                  <artifactId>modeshape-jcr</artifactId>

                  <version>2.8.1.Final</version>

                </dependency>

                <dependency>

                  <groupId>org.modeshape</groupId>

                  <artifactId>modeshape-connector-store-jpa</artifactId>

                  <version>2.8.1.Final</version>

                </dependency>

             

                <dependency>

                  <groupId>javax.servlet</groupId>

                  <artifactId>servlet-api</artifactId>

                  <version>2.5</version>

                  <scope>provided</scope>

                </dependency>

                <dependency>

                  <groupId>org.jboss.spec</groupId>

                  <artifactId>jboss-javaee-6.0</artifactId>

                  <version>2.0.0.CR1</version>

                  <type>pom</type>

                  <scope>provided</scope>

                </dependency>

                <dependency>

                  <groupId>org.jboss.resteasy</groupId>

                  <artifactId>resteasy-jaxrs</artifactId>

                  <version>2.2.1.GA</version>

                  <scope>provided</scope>

                </dependency>

             

                <dependency>

                  <groupId>org.slf4j</groupId>

                  <artifactId>slf4j-api</artifactId>

                  <version>1.6.4</version>

                </dependency>

             

                <dependency>

                  <groupId>junit</groupId>

                  <artifactId>junit</artifactId>

                  <version>3.8.1</version>

                  <scope>test</scope>

                </dependency>  

              </dependencies>

              <build>

                <finalName>JcrServer</finalName>

              </build>

              <!--

              <properties>

                        <hibernate.version>3.4</hibernate.version>

              </properties>

               -->

            </project>

            {code}

             

            I get the JCR repository using the RepositoryFactory scan technique:

             

            {code:java}

            public class JcrService

            {

                private static Logger log = LoggerFactory.getLogger(JcrService.class);

                private static JcrService instance = null;

             

             

                private Repository repository;

             

             

                public static synchronized JcrService getInstance()

                {

                    if (instance == null)

                    {

                        instance = new JcrService("http://localhost:8080/JcrServer/config/modeshape.xml?repositoryName=testRepository");

                    }

             

                    return instance;

                }

             

                private JcrService(String configUrl)

                {

                    this.repository = searchForJcrRepository(configUrl);

                }

             

                private Repository searchForJcrRepository(String configUrl)

                {

                    Repository repository;

                    Map<String, String> parameters = Collections.singletonMap("org.modeshape.jcr.URL", configUrl);

             

                    try

                    {

                        repository = null;

                        for (RepositoryFactory factory : ServiceLoader.load(RepositoryFactory.class))

                        {

                            repository = factory.getRepository(parameters);

                            if (repository != null)

                            {

                                return repository;

                            }

                        }

             

                        log.error("Could not locate RepositoryFactory");

                        throw new RuntimeException("Could not get repository.");

                    }

                    catch (RepositoryException e)

                    {

                        log.error("Could not get repository", e);

                        throw new RuntimeException("RepositoryException thrown while trying to get repository.", e);

                    }

                }

            }

            {code}

             

            The code I am trying to execute is given below:

             

            {code:java}

            public class CreateNodeOperation extends AbstractJcrOperation<CreateNodeRequest, CreateNodeResponse>

            {

                 public CreateNodeOperation(CreateNodeRequest request)

                 {

                     super(request);

                 }

             

                 @Override

                 protected CreateNodeResponse performOperation(Session session,

                           CreateNodeRequest operationRequest) throws Exception

                 {

                     Node parentNode;

                     List<String> nodeIds = new ArrayList<String>();

             

                     // Determine the parent node.

                     for (au.com.infomedix.jcrloadtest.common.nodemodel.Node nodeDef : operationRequest.getNodes())

                     {

                           if (StringUtils.isEmpty(operationRequest.getParentId()))

                           {

                               parentNode = session.getRootNode();

                           }

                           else

                           {

                               parentNode = session.getNodeByIdentifier(operationRequest.getParentId());

                           }

             

                           /*

                            The method toJcrNode() basically does the following:

             

                                Node node = parentNode.addNode(name in nodeDef);

             

                                for (each mixin within nodeDef)

                                    node.addMixin(mixin)             

             

                                for (each property within nodeDef)

                                    node.setProperty(propertyName, propertyValue)

             

                                for (each subnode within nodeDef)

                                    recursively call toJcrNode using "node" as the parent node.

                            */             

                           Node newNode = new JaxbNodeTransformer().includeProperties(true).includeSubnodes(true).includeMixins(true).toJcrNode(parentNode, nodeDef);

                           nodeIds.add(newNode.getIdentifier());

                     }

             

                     session.save();      // This is where the exception is being thrown.

             

                     return CreateNodeResponse.newInstance(true, nodeIds);

                 }

            }

            {code}

             

            The application does not make use of JPA or Hibernate directly (so I haven't added a {{persistence.xml}} file) but uses JCR for all forms of persistence.  I have tried to prevent the Hiberate 4.0 module from loading by adding a {{WEB-INF/jboss-deployment-structure.xml}} file:

             

            {code:xml}

            <?xml version="1.0"?>

            <jboss-deployment-structure>

                <deployment>

                    <exclusions>

                        <module name="org.hibernate"/>

                        <module name="org.jboss.as.jpa.hibernate"/>          

                    </exclusions>

                </deployment>

            </jboss-deployment-structure>

            {code}

             

            I have also tried downgrading the JDBC driver to "postgresql-9.0-802.jdbc4.jar".  However, I still get {{BatchUpdateExcptions}} when I try to write to the repository.

             

            Please let me know if I have missed anything.

             

            Thanks,

             

            Leon

             

            • 3. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
              rhauch

              Are you including the Hibernate 3.5.x libraries in your WAR file?

              • 4. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
                lmika

                Hi Randall,

                 

                Yes, I'm using the version of Hibernate which Modeshape is dependant on (Hibernate 3.5.2-Final).

                 

                However, I am starting to suspect that this might actually be caused by Hibernate and the way it writes LOB data to the PostgreSQL database I'm using. I've checkout the source of Modeshape 2.8.1.FINAL and turned off bulk updates from within the code.  In doing so, I got a different exception when I try to create nodes:

                 

                {noformat}

                14:51:01,008 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/JcrServer].[au.com.infomedix.jcrloadtest.jcrserver.restapi.JcrLoadTestApplication]] (http--127.0.0.1-8080-1) Servlet.service() for servlet au.com.infomedix.jcrloadtest.jcrserver.restapi.JcrLoadTestApplication threw exception: org.jboss.resteasy.spi.UnhandledException: java.lang.RuntimeException: Could not perform operation over the repository

                          at org.jboss.resteasy.core.SynchronousDispatcher.handleApplicationException(SynchronousDispatcher.java:340) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.handleException(SynchronousDispatcher.java:214) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.handleInvokerException(SynchronousDispatcher.java:190) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:540) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:502) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:119) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:208) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:55) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:50) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]

                          at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]

                          at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]

                          at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]

                          at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]

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

                Caused by: java.lang.RuntimeException: Could not perform operation over the repository

                          at au.com.infomedix.jcrloadtest.jcrserver.repo.JcrService.executeCommand(JcrService.java:92) [classes:]

                          at au.com.infomedix.jcrloadtest.jcrserver.restapi.NodeCrudMethods.create(NodeCrudMethods.java:53) [classes:]

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

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

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

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

                          at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:155) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:257) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:222) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:211) [resteasy-jaxrs-2.3.2.Final.jar:]

                          at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:525) [resteasy-jaxrs-2.3.2.Final.jar:]

                          ... 19 more

                Caused by: javax.jcr.RepositoryException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [org.modeshape.connector.store.jpa.model.simple.NodeEntity]

                          at org.modeshape.jcr.SessionCache.save(SessionCache.java:482) [modeshape-jcr-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.jcr.JcrSession.save(JcrSession.java:1312) [modeshape-jcr-2.8.1.Final.jar:2.8.1.Final]

                          at au.com.infomedix.jcrloadtest.jcrserver.operations.CreateNodeOperation.performOperation(CreateNodeOperation.java:55) [classes:]

                          at au.com.infomedix.jcrloadtest.jcrserver.operations.CreateNodeOperation.performOperation(CreateNodeOperation.java:23) [classes:]

                          at au.com.infomedix.jcrloadtest.jcrserver.repo.AbstractJcrOperation.execute(AbstractJcrOperation.java:44) [classes:]

                          at au.com.infomedix.jcrloadtest.jcrserver.repo.JcrService.executeCommand(JcrService.java:84) [classes:]

                          ... 29 more

                Caused by: org.modeshape.graph.connector.RepositorySourceException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [org.modeshape.connector.store.jpa.model.simple.NodeEntity]

                          at org.modeshape.graph.session.GraphSession.save(GraphSession.java:1052) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.jcr.SessionCache.save(SessionCache.java:476) [modeshape-jcr-2.8.1.Final.jar:2.8.1.Final]

                          ... 34 more

                Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [org.modeshape.connector.store.jpa.model.simple.NodeEntity]

                          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:302) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                          at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:461) [modeshape-connector-store-jpa-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.getNode(SimpleJpaRepository.java:265) [modeshape-connector-store-jpa-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.connector.map.MapRequestProcessor.getTargetNode(MapRequestProcessor.java:616) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.connector.map.MapRequestProcessor.process(MapRequestProcessor.java:382) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:315) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:363) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:235) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.connector.store.jpa.model.simple.SimpleJpaConnection.execute(SimpleJpaConnection.java:133) [modeshape-connector-store-jpa-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.connector.RepositoryConnectionPool$ConnectionWrapper.execute(RepositoryConnectionPool.java:1124) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:222) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:211) [modeshape-graph-2.8.1.Final.jar:2.8.1.Final]

                          at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_29]

                          at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_29]

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

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

                          ... 1 more

                Caused by: org.hibernate.exception.SQLGrammarException: could not insert: [org.modeshape.connector.store.jpa.model.simple.NodeEntity]

                          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2438) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2858) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:260) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:1175) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1251) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:269) [hibernate-entitymanager-3.5.2-Final.jar:3.5.2-Final]

                          ... 16 more

                Caused by: org.postgresql.util.PSQLException: ERROR: column "data" is of type oid but expression is of type bytea

                  Hint: You will need to rewrite or cast the expression.

                  Position: 213

                          at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)

                          at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

                          at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

                          at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

                          at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)

                          at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)

                          at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:115)

                          at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)

                          at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2418) [hibernate-core-3.5.2-Final.jar:3.5.2-Final]

                          ... 27 more

                {noformat}

                 

                Doing a few Google searches suggested to me that this has been seen before: [https://hibernate.onjira.com/browse/HHH-4876],

                [http://in.relation.to/15492.lace] and [http://stackoverflow.com/questions/4488693/hibernate-postgresql-column-x-is-of-type-oid-but-expression-is-of-type-byte].

                 

                I have managed to temporarily resolved the problem by adding the {{@Type}} annotation to the {{data}} field of {{NodeEntity.java}} within +modeshape-connector-store-jpa+:

                 

                {code:java}

                // NodeEntry.java:128

                @Lob

                @Column( name = "DATA", nullable = true, unique = false, length = 1048576 )

                @Type( type="org.hibernate.type.PrimitiveByteArrayBlobType" )  // new annotation added.

                private byte[] data;

                {code}

                 

                I am not sure if this is a viable long-term solution (I have no knowledge on how this attribute will affect the storing of data), so I will continue looking for a more appropiate fix.  However, this has resolved the problem for my purposes.

                 

                Thanks for your help,

                 

                Leon

                 

                 

                1 of 1 people found this helpful
                • 5. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
                  rhauch

                  Hi, Leon.

                   

                  I'm glad that you were able to resolve the issue. It does appear to be a bug in Hibernate itself. I also looked for solutions to that error, and came across this StackOverflow question which has the same workaround. I also found HHH-4876 issue in the Hibernate's JIRA, which has the same solution but is marked "WONTFIX" (unfortuntately). It's not clear whether that new annotations will affect working with other DBMSes. Would you mind logging the issue (including the workaround) so that a) we can track it and work with the HIbernate folks to identify a solution, and b) other people might benefit from the workaround.

                   

                  Thanks!

                   

                  Randall

                  • 6. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
                    lmika

                    Hi Randall,

                     

                    I've raised a JIRA ticket for this issue: MODE-1486.  I'll mark this discussion as "answered" now.

                     

                    Thanks for all your help,

                     

                    Leon

                    • 7. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
                      rhauch

                      Thank you for logging that issue, Leon. We'll try fixing it and testing it against several other databases, and if all goes well we'll include the fix in 2.8.2.Final.

                      • 8. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
                        rhauch

                        Leon:

                         

                        Have you tried manually creating the database schema using DDL that explicitly creates the column to be of the correct type? You can generate the (perhaps almost correct) DDL using our generation tool. I'm not sure that will help at all, given the problem happens during reading/writing data.