Details
-
Bug
-
Resolution: Done
-
Major
-
5.1.0.Final
-
None
Description
Repository Layout
The layout of the test repository is presented below. Note, that appRoot and all its child nodes are versionable. By the way, I do understand that this kind of topology is far less than desirable, but unfortunately we have to maintain a repository (formerly running on Jackrabbit 1.x) which has some nodes with a ton of direct children.
- jcrRoot -- appRoot --- node1 ---- child1 ---- child2 ---- ... ---- childN --- node2 --- ... --- nodeN
Problem Creating a Node
Every time a new node, i.e. direct child, gets added to the appRoot, a certain SQL query receives more input parameters. Eventually, the number of parameters exceeds the limit allowed by the Oracle 11g DBMS and the query becomes syntactically incorrect. The table below represents the aforementioned query, where "Node Index" indicates the order in which nodes are added.
| Node Index | SQL Query |
|---|---|
| 1 | SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?) |
| 2 | SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?,?,?) |
| 3 | SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?,?,?,?,?) |
As you can see, the SELECT query that is issued to create the first node has 6 parameters in the IN clause. The second query adds 2 more parameters. The third one adds yet another 2 and so on. It appears that we have an arithmetic progression here. In Oracle 11g, the IN clause cannot have more than 1000 arguments. So, to count how many nodes we need to add to reach the limit, we can do the following:
a1 + (n - 1) * d = 6 + (498 - 1) * 2 = 1000
As a result, if we have more than 498 direct child nodes, the Oracle 11g refuses to invoke the constructed query. Here is a stack trace:
org.modeshape.persistence.relational.RelationalProviderException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 at org.modeshape.persistence.relational.RelationalDb.runWithConnection(RelationalDb.java:370) at org.modeshape.persistence.relational.RelationalDb.load(RelationalDb.java:187) at org.modeshape.jcr.cache.document.LocalDocumentStore.load(LocalDocumentStore.java:83) at org.modeshape.jcr.cache.document.WorkspaceCache.loadFromDocumentStore(WorkspaceCache.java:335) at org.modeshape.jcr.cache.document.WritableSessionCache.lockNodes(WritableSessionCache.java:1613) at org.modeshape.jcr.cache.document.WritableSessionCache.save(WritableSessionCache.java:712) at org.modeshape.jcr.cache.SessionCacheWrapper.save(SessionCacheWrapper.java:96) at org.modeshape.jcr.JcrSession$SystemSessionCache.save(JcrSession.java:2482) at org.modeshape.jcr.JcrVersionManager.checkin(JcrVersionManager.java:373) at org.modeshape.jcr.JcrVersionManager.checkin(JcrVersionManager.java:295) ... Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.modeshape.persistence.relational.DefaultStatements.loadIDs(DefaultStatements.java:138) at org.modeshape.persistence.relational.DefaultStatements.lambda$load$0(DefaultStatements.java:123) at org.modeshape.persistence.relational.DefaultStatements.runBatchOperation(DefaultStatements.java:261) at org.modeshape.persistence.relational.DefaultStatements.load(DefaultStatements.java:121) at org.modeshape.persistence.relational.RelationalDb.lambda$load$12(RelationalDb.java:187) at org.modeshape.persistence.relational.RelationalDb.runWithConnection(RelationalDb.java:362) ...
Possible Solution
Before sending the query for execution, it may be possible to format it in a way that Oracle 11g will understand, e.g.:
SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN ('entry-0', 'entry-1', ... 'entry-999') OR ID IN ('entry-1000', 'entry-1001', ... 'entry-1999') OR ID IN (...)
Problem Deleting a Node
Similarly to the problem related to node creation, we can get an invalid query when removing a node with a lot of children. The table below represents some of the queries that get sent to the DBMS upon request to delete a node, where "Node Count" represents the overall number of direct child nodes for a node to be deleted.
| Node Count | SQL Query |
|---|---|
| 1 | DELETE FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?) |
SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?,?) |
|
| 2 | DELETE FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?) |
SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?,?,?,?,?) |
|
| 3 | DELETE FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?) |
SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID IN (?,?,?,?,?,?,?,?,?,?,?,?,?) |
Based on the results above (considering arithmetic progression a1 + (n - 1) * d):
- The SELECT query will fail on an attempt to delete a node with 332 child nodes, i.e. 7 + (332 - 1) * 3 = 1000
- The DELETE query will fail on an attempt to delete a node with 999 child nodes, i.e. 2 + (999 - 1) * 1 = 1000
Questions
If an issue described here is a known problem, is there a possibility to release a fix for it in 5.2 version of the ModeShape? And if so, is 5.2 still targeted for this month, i.e. September, 2016? Thank you.
Notes
- The queries provided here were obtained with YourKit profiling tool.
- A sample project that allows to demonstrate a problem is located here. By the way, it takes a while to create a lot of direct child nodes (~ 2h to get ~ 490 nodes).
