Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 13:48 Go to next message
Eclipse UserFriend
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 13:57 Go to previous messageGo to next message
Eclipse UserFriend
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 14:38 Go to previous messageGo to next message
Eclipse UserFriend
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 16:32 Go to previous messageGo to next message
Eclipse UserFriend
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 21:20 Go to previous messageGo to next message
Eclipse UserFriend
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 21:25] by Moderator

Report message to a 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 16:37 Go to previous message
Eclipse UserFriend
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:
  


Current Time: Sun Feb 09 06:01:56 GMT 2025

Powered by FUDForum. Page generated in 0.04727 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top