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

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 - 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 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( ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery( ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.QueryImpl.getSingleResult( ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult( ~[org.eclipse.persistence.jpa-2.7.9-5-jmix.jar!/:na]
 at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultFromCache( ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getSingleResult( ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JpaDataStore.executeQuery( ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.eclipselink.impl.JpaDataStore.loadOne( ~[jmix-eclipselink-1.1.1.jar!/:na]
 at io.jmix.core.datastore.AbstractDataStore.loadAllAfterSave( ~[jmix-core-1.1.1.jar!/:na]
 at ~[jmix-core-1.1.1.jar!/:na]
 at io.jmix.core.impl.UnconstrainedDataManagerImpl.saveContextToStore( ~[jmix-core-1.1.1.jar!/:na]
 at ~[jmix-core-1.1.1.jar!/:na]
... was called here

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

Thank you for reporting the problem!
We’ll see what we can do:

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