Uploaded image for project: 'ModeShape'
  1. ModeShape
  2. MODE-691

SQL Error when deleting a node with the JPA Connector and a MySQL database

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 1.0.0.Final
    • Fix Version/s: 1.1.0.Final
    • Component/s: Storage
    • Labels:
      None
    • Environment:

      ModeShape Version: 1.0.0.Final
      MySQL version: MySQL v5.1.41-community
      MySQL JDBC Driver: MySQL Connector/J 5.1.12
      JVM Version: 1.6.0_17
      Operating System: Windows 7 Professional, 64-bit

      Description

      When using ModeShape 1.0.0.Final with the JPA Connector, MySQL v5.1.41-community, and MySQL Connector/J 5.1.12, a SQL Error is thrown when deleting a node. This error does not occur when using HSQLDB instead of MySQL. The error message is:

      WARN SQL Error: 1093, SQLState: HY000
      ERROR You can't specify target table 'MODE_SIMPLE_LARGE_VALUES' for update in FROM clause

      The SQL statement which triggers the error is:

      delete from MODE_SIMPLE_LARGE_VALUES where SHA1 not in (
      select largevalue3_.SHA1 from MODE_SIMPLE_NODE nodeentity1_
      inner join ModeShape_LARGEVALUE_USAGES largevalue2_
      on nodeentity1_.ID=largevalue2_.ID
      inner join MODE_SIMPLE_LARGE_VALUES largevalue3_
      on largevalue2_.largeValues_SHA1=largevalue3_.SHA1
      )

      Related MySQL documentation:
      "Currently, you cannot delete from a table and select from the same table in a subquery."
      --from the MySQL DELETE Syntax documentation: http://dev.mysql.com/doc/refman/5.1/en/delete.html

      Code block which triggers the SQL error (session is the javax.jcr.Session, pathToNode is relative to the workspace root node):

      Node root = session.getRootNode();
      if(!root.hasNode(pathToNode)) {
      throw new RepositoryException("Cannot delete node at path={} since no node exists at this path", pathToNode);
      }
      Node nodeToDelete = root.getNode(pathToNode);
      nodeToDelete.remove();
      session.save();

      The repository source settings in my configuration XML are like this:
      <mode:source jcr:name="Content"
      mode:classname="org.modeshape.connector.store.jpa.JpaSource"
      mode:model="Simple"
      mode:dialect="org.hibernate.dialect.MySQL5InnoDBDialect"
      mode:driverClassName="com.mysql.jdbc.Driver"
      mode:username="modeshapeuser"
      mode:password="password"
      mode:url="jdbc:mysql://localhost:3306/modeshapetest?autoReconnect=true"
      mode:maximumConnectionsInPool="1"
      mode:referentialIntegrityEnforced="true"
      mode:largeValueSizeInBytes="1024"
      mode:retryLimit="3"
      mode:compressData="false"
      mode:predefinedWorkspaceNames="default"
      mode:showSql="false"
      mode:autoGenerateSchema="create"
      mode:creatingWorkspacesAllowed="true"
      mode:defaultWorkspaceName="default" />

      And here is the relevant part of the exception stack trace:

      Caused by: org.modeshape.graph.connector.RepositorySourceException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute update query
      at org.modeshape.graph.session.GraphSession.save(GraphSession.java:817)
      at org.modeshape.jcr.SessionCache.save(SessionCache.java:374)
      ... 13 more
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute update query
      at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
      at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:60)
      at org.modeshape.connector.store.jpa.model.simple.LargeValueEntity.deleteUnused(LargeValueEntity.java:192)
      at org.modeshape.connector.store.jpa.model.simple.SubgraphQuery.deleteSubgraph(SubgraphQuery.java:220)
      at org.modeshape.connector.store.jpa.model.simple.SimpleJpaRepository$Workspace.removeUuidReference(SimpleJpaRepository.java:402)
      at org.modeshape.graph.connector.map.AbstractMapWorkspace.removeNode(AbstractMapWorkspace.java:268)
      at org.modeshape.graph.connector.map.MapRequestProcessor.process(MapRequestProcessor.java:315)
      at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:229)
      at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:316)
      at org.modeshape.graph.request.processor.RequestProcessor.process(RequestProcessor.java:223)
      at org.modeshape.connector.store.jpa.model.simple.SimpleJpaConnection.execute(SimpleJpaConnection.java:126)
      at org.modeshape.graph.connector.RepositoryConnectionPool$ConnectionWrapper.execute(RepositoryConnectionPool.java:997)
      at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:193)
      at org.modeshape.graph.request.CompositeRequestChannel$2.call(CompositeRequestChannel.java:183)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      at java.util.concurrent.FutureTask.run(FutureTask.java:138)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
      at java.lang.Thread.run(Thread.java:619)
      Caused by: org.hibernate.exception.GenericJDBCException: could not execute update query
      at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
      at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
      at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
      at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:107)
      at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:419)
      at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
      at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1168)
      at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
      at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:51)
      ... 17 more
      Caused by: java.sql.SQLException: You can't specify target table 'MODE_SIMPLE_LARGE_VALUES' for update in FROM clause
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2398)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2316)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2301)
      at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
      at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:98)
      ... 22 more

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  rhauch Randall Hauch
                  Reporter:
                  lookfar1090 Greg Larson
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: