8 Replies Latest reply: May 22, 2012 12:27 PM by Randall Hauch RSS

SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3

Leon Mika Newbie

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:

 

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

 

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.

 

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 , 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:

 

<?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>

 

Data Store Configuration:

<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>

 

  • 1. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
    Randall Hauch Master

    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
    Leon Mika Newbie

    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:

     

    <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>
    

     

    I get the JCR repository using the RepositoryFactory scan technique:

     

    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);
            }
        }
    }
    

     

    The code I am trying to execute is given below:

     

    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);
         }
    }
    

     

    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:

     

    <?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>
    

     

    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
    Randall Hauch Master

    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
    Leon Mika Newbie

    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:

     

    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
    

     

    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:

     

    // 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;
    

     

    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

     

     

     

  • 5. Re: SQLGrammaException using Modeshape 2.8.0.Final with PostgreSQL 9.0.3
    Randall Hauch Master

    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
    Leon Mika Newbie

    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
    Randall Hauch Master

    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
    Randall Hauch Master

    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.