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
Dragomir Zoranovic is currently offline Dragomir ZoranovicFriend
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 13:57 Go to previous messageGo to next message
Dragomir Zoranovic is currently offline Dragomir ZoranovicFriend
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 14:38 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
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 16:32 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
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 21:20 Go to previous messageGo to next message
Dragomir Zoranovic is currently offline Dragomir ZoranovicFriend
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 21: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 16:37 Go to previous message
James Sutherland is currently offline James SutherlandFriend
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: Fri Dec 19 18:24:28 GMT 2014

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

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