Page 1 of 1

Too many tables; MySQL can only use 61 tables in a join

Posted: Fri May 22, 2015 3:35 am
by ig0r
on org.broadleafcommerce.core.order.domain.OrderImpl.updatePrices(OrderImpl.java:593) i get a nasty exception

analyzing the query it really fetches the whole relationship graph. and since we extended lot of entities we reach the limit.
any ideas why its fetching the whole graph, and how to resolve this? we are running on blc 3.0.5-GA

[ERROR] [site] 22-05-2015 08:40:11 org.broadleafcommerce.core.workflow.DefaultErrorHandler 54 - An error occurred during the workflow
org.hibernate.exception.GenericJDBCException: Too many tables; MySQL can only use 61 tables in a join
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy193.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
at org.hibernate.loader.Loader.doQuery(Loader.java:899)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2228)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:61)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:678)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:80)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1804)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:549)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:234)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:545)
at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:124)
at org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:266)
at org.broadleafcommerce.core.order.domain.OrderImpl.updatePrices(OrderImpl.java:593)
[08:41:45] Igor Fodor: at com.sun.proxy.$Proxy193.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
at org.hibernate.loader.Loader.doQuery(Loader.java:899)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2228)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:61)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:678)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:80)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1804)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:549)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:234)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:545)
at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:124)
at org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:266)
at org.broadleafcommerce.core.order.domain.OrderImpl.updatePrices(OrderImpl.java:593)

Re: Too many tables; MySQL can only use 61 tables in a join

Posted: Fri May 22, 2015 8:43 am
by ig0r
is it possible mixing inheritance strategies in a entity hierarchy ?

Category (joined inheritance) <- ExtendedCategory (Single Table) <- CatA extends ExtendedCategory, CatB extends ExtendedCategory

Re: Too many tables; MySQL can only use 61 tables in a join

Posted: Wed May 27, 2015 12:11 am
by phillipuniverse
You're probably running into the fix that we had here: https://github.com/BroadleafCommerce/Br ... ssues/1209

The only way around it in the 3.0 line is to use single table inheritance.