Level 2

Exception executing report: javax.el.ELException: .Using ACS Commons report

Forum|Forum|1 year ago
June 4, 2024
3 replies
1568 views

Hi,

I am using ACS commons reports to pull the pages report that are deactivated and modified long back, offtime & redirected pages.

While I am trying to run the report based on the above conditions I am facing this error.

Error: Exception executing report: javax.el.ELException: Error reading 'resultsList' on type com.adobe.acs.commons.reports.api.ResultsPage Error reading 'resultsList' on type com.adobe.acs.commons.reports.api.ResultsPage

Query: SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') AND (s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE) OR (s.[cq:lastReplicationAction] = "Deactivate" AND s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE)) OR offTime < CAST("2023-12-01T00:00:00.000Z" AS DATE) OR s.[redirectTarget] IS NOT NULL )

This is working for small content locale but not for huge no of pages. Can someone help me here why am i facing this error.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

EstebanBustamante

Community Advisor and Adobe Champion

Hi,

It looks like the query is the issue, can you try to run the query directly in CRX/DE or in the Query Performance Tool? If you get an issue it is most likely that you will require an index. Please check this: https://experienceleague.adobe.com/en/docs/experience-manager-cloud-service/content/operations/query-and-indexing-best-practices#query-performance-tool

Hope this helps

Esteban Bustamante

HrishikeshKagne

Community Advisor

Hi @nimma05 ,

The issue you're encountering with the ACS Commons report appears to be related to the size and complexity of the query being executed, especially when working with a large number of pages. The javax.el.ELException indicates an error in evaluating the expression language in Java, often due to issues like null values, large result sets, or performance bottlenecks.

Here's a step-by-step approach to troubleshooting and potentially resolving the issue:

1. Check Query Complexity

Ensure that your query is optimized and not overly complex. Complex queries with many conditions can be slow and resource-intensive, especially on large data sets.

2. Incremental Query Execution

Break down the query into smaller parts and execute them incrementally to identify which part is causing the problem. This helps in pinpointing the exact issue.

3. Paging and Limits

Use paging and limits to handle large result sets efficiently. This can prevent memory overload and improve performance.

4. Optimizing Query Conditions

Simplify and optimize the query conditions where possible. For example, instead of multiple OR conditions, try to structure the query to minimize the number of conditions checked.

5. Check for Null Values

Ensure that none of the fields being queried have unexpected null values that could cause the expression language evaluation to fail.

6. Review ACS Commons Configuration

Ensure that the ACS Commons configuration is correctly set up and optimized for performance. You might need to adjust settings related to query execution or result handling.

7. Use Logs for Debugging

Enable detailed logging to capture more information about the error. This can provide insights into what is causing the ELException.

Example Query Breakdown

Here’s an example of breaking down the query and handling potential issues incrementally:

Step 1: Basic Query for cq:PageContent

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}')

Step 2: Add Last Modified Condition

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') AND s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE)

Step 3: Add Deactivated Pages Condition

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') AND (s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE) OR s.[cq:lastReplicationAction] = "Deactivate")

Step 4: Add Off Time Condition

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') AND (s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE) OR s.[cq:lastReplicationAction] = "Deactivate" OR offTime < CAST("2023-12-01T00:00:00.000Z" AS DATE))

Step 5: Add Redirected Pages Condition

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') AND (s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE) OR s.[cq:lastReplicationAction] = "Deactivate" OR offTime < CAST("2023-12-01T00:00:00.000Z" AS DATE) OR s.[redirectTarget] IS NOT NULL)

Example with Paging and Limits

Using paging and limits can help manage large result sets:

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}') 
AND (s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE) 
OR (s.[cq:lastReplicationAction] = "Deactivate" AND s.[cq:lastModified] < CAST("2022-01-01T00:00:00.000Z" AS DATE)) 
OR offTime < CAST("2023-12-01T00:00:00.000Z" AS DATE) 
OR s.[redirectTarget] IS NOT NULL) 
ORDER BY s.[jcr:created] 
LIMIT 1000 OFFSET 0

Debugging and Logging

Enable detailed logging for the ACS Commons and JCR queries. In AEM, you can increase the log level for specific classes to gather more information:

Go to AEM Web Console: http://localhost:4502/system/console/configMgr
Search for "Apache Sling Commons Log File".
Add or modify the logger for com.adobe.acs.commons.reports and org.apache.jackrabbit.oak.query.
Set the log level to DEBUG or TRACE.

This will help you capture detailed logs and identify where the issue might be occurring.

By following these steps, you should be able to isolate the cause of the ELException and optimize your query for better performance and reliability

Hrishikesh Kagane

N

Nimma05Author

Level 2

Hi @hrishikeshkagne,

Thank you! for your detailed explanation.

Even with the basic query I am getting error.

SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s, '{{path}}')

In the logs i could see below error

org.apache.jackrabbit.oak.query.RuntimeNodeTraversalException: The query read or traversed more than 500000 nodes. To avoid affecting other tasks, processing was stopped.

So, I increased the limit to 7 lac in osgi configuration for time being to get the report after changing the issue got resolved. Is that fine if I change the limit?

I am running this query on 19k data

Thanks

HrishikeshKagne

Community Advisor

Hi @nimma05 ,

Increasing the limit to 700,000 nodes may solve the issue for now, but it is not a recommended solution. Querying a large number of nodes can have a significant impact on the performance of the system.

You may want to consider optimizing your query to reduce the number of nodes being queried. You can try adding additional filters to your query to narrow down the results. For example, you can filter by node type or property values.

If optimizing the query is not possible, you may want to consider using pagination to limit the number of nodes returned in each query. This can help reduce the impact on system performance.

It is also important to note that increasing the limit may not be a permanent solution. As the amount of data in the system grows, you may need to increase the limit again in the future.

Hrishikesh Kagane

kautuk_sahni

Community Manager

@nimma05 Did you find the suggestions from users helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!

Kautuk Sahni