CODEWITHSUNDEEP
sqlmysqljoingrailsgrails-orm
JiniKJohny
JiniKJohny

Reputation: 1182

Grails Lazy load to avoid Too many table issue after 61 tables to Join

I have a set of tables inherited from the parent table and finally got stuck with the SQL limitation of joining too many tables.

Too many tables; MySQL can only use 61 tables in a join. Stacktrace follows:
java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1915)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2023)

How can I avoid joining the tables while selecting from the parent table? Similar to set lazy: true for a property, is there any option to load all the associations LAZY by a single configuration?

Class Parent {
  long id,
  String name
  DisplayMode layoutMode
   ......
  static mappings = {
     cache: true,
     version: true
  } 
}

Class Child1 extends Parent{
  long id,
  String prop1
  String prop2
   ......
  static mappings = {
     cache: true,
     version: true
  } 
}

Class Child2 extends Parent{
  long id,
  String prop1
  String prop2
   ......
  static mappings = {
     cache: true,
     version: true
  } 
}

Class Child3 extends Parent{
  long id,
  String prop1
  String prop2
   ......
  static mappings = {
     cache: true,
     version: true
  } 
}

There is almost 60+ child tables defined already. This parent class is a Dashboard parent and child are each dashlets and there is more dashlets yet to come.

Any select happening on Parent table joins all the child tables and I need to avoid that.

Grails version: 2.4.3

MySQL : 8

Upvotes: 5

Views: 133

Answers (2)

devbd
devbd

Reputation: 431

In your classes, you didn't define any association so Grails don't know how to deal with data fetching.

Try after defining an association between parent-child classes... hasOne or hasMany and then belongsTo

Class Parent {
  long id,
  String name
  DisplayMode layoutMode
   ......
  static mappings = {
     cache: true,
     version: true
  } 
  static hasMany = [child1 : Child1, child2 : Child2, child3 : Child3]
}

Please note

Class Parent {
   ......
  static mappings = {
     ....
     child1  lazy: true // it's not needed
  } 

}

Usage: association_name(lazy: boolean)

By default GORM single-ended associations are lazy in that when you load a domain instance, an associated domain is not loaded until accessed.

Grails Doc

Upvotes: 0

d r
d r

Reputation: 7846

NOTE:
I know nothing about grails, but since noone is answering this - maybe I could show you how to workaround the mysql 61+ joins limitation.

If your question (or part of it) is how to make mysql query work if there are more than 61 table joins involved then the answer could be grouping sets of tables together using UNION ALL and then join grouped datasets to the parent table. This should give you the data from all (62) tables with number of joins reduced to the number of groupings.
Please see the mysql fiddle here.
I created 62 tables and joined them all individualy getting the error from your question (61+ joins limit). I made just one group of 10 tables unioned all and joined to the parent table as a group with the rest 51 table left joined individualy and there were no error any more since there are now 52 joins.
As I could see you have a lot of tables that are basicaly of the same description that could be unioned together so you should be able to reduce the number of joins simply using UNION ALL as in the fiddle.

Upvotes: 1

Related Questions