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)

Show: Today's Messages :: Show Polls :: Message Navigator

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

Thu, 01 March 2012 08:48

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?

Report message to a moderator

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

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

Report message to a moderator

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

Chris Delahunt
Messages: 863
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

Report message to a moderator

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

James Sutherland
Messages: 1844
Registered: July 2009

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

Report message to a moderator

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

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

James Sutherland
Messages: 1844
Registered: July 2009

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

Report message to a moderator

Previous Topic:	QueuableWeakCacheKey growing endlessly
Next Topic:	Relation between Mulitple instance of EntityManagerFactory

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 19:31:21 EDT 2013