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 08:48 Go to next message
Dragomir Zoranovic is currently offline Dragomir Zoranovic
Messages: 7
Registered: February 2012
Junior Member
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 Go to previous messageGo to next message
Dragomir Zoranovic is currently offline Dragomir Zoranovic
Messages: 7
Registered: February 2012
Junior Member
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 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
Registered: July 2009
Senior Member
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 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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.


James : Wiki : Book : Blog : Twitter
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 Go to previous messageGo to next message
Dragomir Zoranovic is currently offline Dragomir Zoranovic
Messages: 7
Registered: February 2012
Junior Member
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]

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 11:37 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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);


James : Wiki : Book : Blog : Twitter
Previous Topic:QueuableWeakCacheKey growing endlessly
Next Topic:Relation between Mulitple instance of EntityManagerFactory
Goto Forum:
  


Current Time: Tue Jul 29 08:48:29 EDT 2014

Powered by FUDForum. Page generated in 0.03829 seconds