Eclipse Community Forums: EclipseLink » Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables

Home » Eclipse Projects » EclipseLink » Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables(java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join)

Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #810736]

Thu, 01 March 2012 08:48

Eclipse User

Hi,

I have more than 61 subclasses of one root class. Making query on a root class gives: java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join.
I have tried with InheritancePolicy.setShouldOuterJoinSubclasses(false), but still got the same exception. The only way to get query working is to remove ordering. It seems that using ordering always triggers outer-joining.

Am I missing something in descriptor configuration for a root descriptor?

Re: Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #810744 is a reply to message #810736]

Thu, 01 March 2012 08:57

Eclipse User

Here is a stacktrace:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): 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
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:646)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:3097)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:3049)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:420)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:2831)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
at org.eclipse.persistence.internal.sessions.remote.RemoteSessionController.executeQuery(RemoteSessionController.java:329)
at org.eclipse.persistence.sessions.remote.rmi.RMIRemoteSessionControllerDispatcher.executeQuery(RMIRemoteSessionControllerDispatcher.java:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:305)
at sun.rmi.transport.Transport$1.run(Transport.java:159)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:155)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:931)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:607)
... 34 more

Re: Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #810770 is a reply to message #810744]

Thu, 01 March 2012 09:38

Eclipse User

Using the database for ordering is not possible over multiple queries, so joining is used when the object has inheritance. You will not be able to use odering on the query used on the root, and instead order the returned results inmemory if required.

Best Regards,
Chris

Re: Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #810875 is a reply to message #810770]

Thu, 01 March 2012 11:32

Eclipse User

You can set the query hint, "eclipselink.inheritance.outer-join"="false" on the query.

Because multiple queries are executed, you will not get a proper order, but the result will still be ordered within each subclass.

Re: Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #811037 is a reply to message #810875]

Thu, 01 March 2012 16:20

Eclipse User

How can I apply hint to query by using native Java code? I can see there is a method DatabaseQuery.setHintString(String newHintString), but what is a value for parameter newHintString.

[Updated on: Thu, 01 March 2012 16:25] by Moderator

Re: Outer-Joining Subclasses on Queries gives java.sql.SQLException: Too many tables [message #813720 is a reply to message #811037]

Mon, 05 March 2012 11:37

Eclipse User

If the query is a JPA Query or @NamedQuery you can set the hint using setHint() or @QueryHint.

If you are using a EclipseLink native query (ReadAllQuery) just call,
query.setShouldOuterJoinSubclasses(false);

Previous Topic:	Absence of @DiscriminatorColumn causes exception: Unknown column DTYPE
Next Topic:	Relation between Mulitple instance of EntityManagerFactory

Goto Forum:

-=] Back to Top [=-

Current Time: Thu Jul 03 20:51:45 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter