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