Hi,

I am trying to load a list with ScrollableResult and that works fine if there are not a lot of results. But now i have like 1.5milj titles and i get this exception while calling query.scroll

I appreciate any kind of help

thx in advance





Hibernate version: hibernate-annotations

Code between sessionFactory.openSession() and session.close():

@Transactional(readOnly = true)
public SrollableResults load(Distributor dis){
Session session = sessionFactory.getCurrentSession();
Query query = session.createQuery("FROM Title title WHERE title.archived = false AND title.isbn.fund.distributor = ? and title.isbn.fund.active = true and title.isbn.fund.marketType=?");
int i = 0;
query.setParameter(i++, dist);
query.setParameter(i++, MarketType.OPEN_MARKET);

retrun query.scroll(ScrollMode.FORWARD_ONLY);

}


Full stack trace of any exception that occurs:
Caused by: java.sql.SQLException: Incorrect key file for table '#sql_7cc_0.MYI'; try to repair it
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1250)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:763)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.scroll(Loader.java:2301)


Caused by: org.hibernate.exception.GenericJDBCException: could not execute query using scroll
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.scroll(Loader.java:2336)
at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:464)
at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:413)
at org.hibernate.engine.query.HQLQueryPlan.performScroll(HQLQueryPlan.java:269)
at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1233)
at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:90)


Caused by: java.sql.SQLException: Incorrect key file for table '#sql_7cc_0.MYI'; try to repair it
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1250)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:763)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.scroll(Loader.java:2301)

Name and version of the database you are using:
Mysql-5


<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show.sql}</prop>
<prop key="hibernate.cache.use_query_cache">${hibernate.cache.query}</prop>
<prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.secondlevel}</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.validator.autoregister_listeners">${hibernate.validator.autoregister_listeners}</prop>
<prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
<prop key="hibernate.jdbc.batch_size">100</prop>
<prop key="hibernate.order_inserts">true</prop>
<prop key="hibernate.order_updates">true</prop>
<prop key="hibernate.jdbc.use_scrollable_resultset">true</prop>
<prop key="hibernate.jdbc.fetch_size">30</prop>


</props>
</property>


<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${hibernate.connection.driver_class}" />
<property name="jdbcUrl" value="${hibernate.connection.url}" />
<property name="user" value="${hibernate.connection.username}" />
<property name="password" value="${hibernate.connection.password}" />
<property name="maxPoolSize" value="${hibernate.connection.max.poolsize}" />
<property name="idleConnectionTestPeriod" value="60" />
<property name="preferredTestQuery" value="select 1" />
</bean>


${hibernate.connection.url} :



hibernate.connection.url=jdbc:mysql://localhost:3306/test?useCursorFetch=true&amp;useServerPrepStmts=true&amp;

I have seen this before and in our case it was caused by running out of space on the temp drive. You'll probably have to configure MySQL to use a different directory on another disk/partition for temporary files.

Ok nordborg thx for your support

I'll try that and let you know if that works