error occurred while executing hibernate query

Question

Exception while execution of a fetch HQL query .The query works most of the time but some time its showing this exception

the database is MySQL and the server used is JBoss 5.1.0 GA

The error thats shown is:

org.hibernate.exception.GenericJDBCException: could not execute 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.loader.Loader.doList(Loader.java:2231) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at java.lang.Thread.run(Thread.java:745) Caused by: org.jboss.util.NestedSQLException: Error; - nested throwable: (java.lang.OutOfMemoryError: GC overhead limit exceeded) at org.jboss.resource.adapter.jdbc.WrappedConnection.checkException(WrappedConnection.java:873) at org.jboss.resource.adapter.jdbc.WrappedStatement.checkException(WrappedStatement.java:852) at org.jboss.resource.adapter.jdbc.WrappedResultSet.checkException(WrappedResultSet.java:1947) at org.jboss.resource.adapter.jdbc.WrappedResultSet.getString(WrappedResultSet.java:892) at org.hibernate.type.StringType.get(StringType.java:41) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:173) at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105) at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2124) at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1404) at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1332) at org.hibernate.loader.Loader.getRow(Loader.java:1230) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:603) at org.hibernate.loader.Loader.doQuery(Loader.java:724) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) ... 11 more Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded

Answer 1

Your application seems to have run out of memory. Probably the query might be returning a very large data set, so the memory allocated to the application is not sufficient to handle that. You can either,

• Increase the memory allocated to the Java process, or
• Fetch your data in subsets (pagination) to avoid a large data set getting returned at once.

Answer 2

Based in your stacktrace the query was executed successfully in the database and the error occurs when the result set is being prepared inside the JVM. This exceptions is a memory related issue that occurs when your program is demanding more than available memory.

"Thrown when the Java Virtual Machine cannot allocate an object because it is out of memory, and no more memory could be made available by the garbage collector"

Some possibilities are

• Configuration: You heap configuration (Xmx) is too small for your needs and you should extend it.

• Bug: You have a bug in your query that generates a result set bigger that expected.

• Design: You need to redesign your code in order to load this data without exceed your available memory. You can do pagination and store a small chunk of data on each iteration. In that case it's important to flush each page to the end-user or consumer before load the next one.

GC overhead tutorial

Query Pagination tutorial