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

Support
#1

One of my entity has a quite high complexity with associations to itself and I run into this problem quite frequently now:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.5-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join
Error Code: 1116

This happens at a dataManager.save(...) call though, so I can’t optimize the fetch plan.

I already did some simplifications of the entity and reduced associations, but I would like to know if there are other ways of dealing with this problem?

EDIT: added part of interesting stacktrace:

 at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391) ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264) ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult(QueryImpl.java:530) ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:406) ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultFromCache(JmixEclipseLinkQuery.java:793) ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getSingleResult(JmixEclipseLinkQuery.java:155) ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JpaDataStore.executeQuery(JpaDataStore.java:494) ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JpaDataStore.loadOne(JpaDataStore.java:143) ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.core.datastore.AbstractDataStore.loadAllAfterSave(AbstractDataStore.java:432) ~[jmix-core-1.1.1.jar!/:na]
 at io.jmix.core.datastore.AbstractDataStore.save(AbstractDataStore.java:236) ~[jmix-core-1.1.1.jar!/:na]
 at io.jmix.core.impl.UnconstrainedDataManagerImpl.saveContextToStore(UnconstrainedDataManagerImpl.java:257) ~[jmix-core-1.1.1.jar!/:na]
 at io.jmix.core.impl.UnconstrainedDataManagerImpl.save(UnconstrainedDataManagerImpl.java:216) ~[jmix-core-1.1.1.jar!/:na]
... 
dataManager.save(commitContext.setJoinTransaction(false)) was called here
#2

This particular problem I solved by adding .setDiscardSaved(true) to the SaveContext.

#3

Thank you for reporting the problem!
We’ll see what we can do: https://github.com/Haulmont/jmix-data/issues/109

Currently you can either use setDiscardSaved(true) or make some of your referenced entities cacheable, for example:

eclipselink.cache.shared.Foo = true
eclipselink.cache.size.Foo = 500

If a referenced entity is cacheable, it is always loaded from cache (or by a separate select if cache misses) instead of SQL join, regardless of the fetch mode set in the fetch plan.

1 Like