cancel
Showing results for 
Search instead for 
Did you mean: 

MSSQL Fatel error: The query processor ran out of internal resources and could not produce a query plan

Former Member
0 Kudos

Hi, I am using hybris-commerce-suite-5.4.0.3 + Microsoft SQL server 2014 database. Getting the fatal error during content catalog synchronization/system update where as it is working fine when connected to HSQL DB. Even I have tried to remove all the relations defined in my core-items.xml file but still getting errors. =========== Pl refer below error description =============

[DefaultSetupSyncJobService] Created cronjob [000000VW] to synchronize catalog [myprojectContentCatalog] staged to online version. INFO | jvm 1 | main | 2016/02/10 20:22:06.301 | INFO [hybrisHTTP20] [DefaultSetupSyncJobService] Configuring full version sync INFO | jvm 1 | main | 2016/02/10 20:22:22.805 | ERROR [Thread-141] [SyncSchedulerCallableBase] Exception The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. occurred during executing query INFO | jvm 1 | main | 2016/02/10 20:22:24.607 | (SELECT {p1:pk},{p2:pk} FROM {CMSItem AS p1 JOIN CMSItem AS p2 ON {p1:catalogVersion}=?srcVer AND {p2:catalogVersion}=?tgtVer AND {p1:uid}={p2:uid} } WHERE NOT EXISTS ({{SELECT {pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND ({ts:sourceItem}={p1:pk} )}}) AND NOT EXISTS ({{ SELECT {pk} FROM {ItemSyncTimestamp AS ts} WHERE {ts:syncJob}=0 AND {ts:targetVersion}=?tgtVer AND {ts:sourceVersion}=?srcVer AND ({ts:targetItem}={p2:pk} )}})ORDER BY {p1:creationtime} ASC, {p1:pk} ASC) with arguments (tgtVer,srcVer), INFO | jvm 1 | main | 2016/02/10 20:22:24.609 | values(myprojectContentCatalog/Online(8796093252185),myprojectContentCatalog/Staged(8796093219417)) INFO | jvm 1 | main | 2016/02/10 20:22:24.620 | INFO | jvm 1 | main | 2016/02/10 20:22:24.620 | de.hybris.platform.jalo.JaloSystemException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.[HY--1] INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.handleError(JDBCQuery.java:126) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:74) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.callImpl(SyncSchedulerCallableBase.java:120) INFO | jvm 1 | main | 2016/02/10 20:22:24.621 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:96) INFO | wrapper | main | 2016/02/10 20:22:24.621 | Wrapper Process has not received any CPU time for 1 seconds. Extending timeouts. INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at de.hybris.platform.catalog.jalo.synchronization.SyncSchedulerCallableBase.call(SyncSchedulerCallableBase.java:1) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.FutureTask.run(FutureTask.java:262) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | at de.hybris.platform.core.TenantAwareThreadFactory$1.run(TenantAwareThreadFactory.java:160) INFO | jvm 1 | main | 2016/02/10 20:22:24.622 | Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) INFO | jvm 1 | main | 2016/02/10 20:22:24.623 | at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) INFO | jvm 1 | main | 2016/02/10 20:22:24.628 | at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) INFO | jvm 1 | main | 2016/02/10 20:22:24.628 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:194) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | at de.hybris.platform.catalog.jalo.synchronization.JDBCQuery.execute(JDBCQuery.java:70) INFO | jvm 1 | main | 2016/02/10 20:22:24.629 | ... 7 more INFO | jvm 1 | main | 2016/02/10 20:22:24.630 | INFO [hybrisHTTP20] [Initialization] Error creating sample data for myprojectinitialdata. See console output.

Former Member
0 Kudos

It is a development server and hybris and mssql db are running on the same machine with 16 GB RAM. Pl give some more inputs and solution as this is a stopper for our project now.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

"The query processor ran out of internal resources " is a good hint, dare I ask, what sort of equipment are you running the database server on? is this a dev environment? are you running hybris and DB server on same machine? is it a dedicated DB box? is it fine tuned? removing relations ain't the answer for sure. Your database is either struggling to cope with very limited memory / CPU, or it is corrupt.

Former Member
0 Kudos

It is a development server and hybris and mssql db are running on the same machine with 16 GB RAM. Pl give some more inputs and solution as this is a stopper for our project now.