Jump to:

23483 Posts in 18976 Topics by 2878 members

General Questions

SilverStripe Forums » General Questions » Too many tables; MySQL can only use 61 tables in a join

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 2345 Views
  • njprrogers
    Avatar
    Community Member
    23 Posts

    Too many tables; MySQL can only use 61 tables in a join Link to this post

    Hi,

    I'm working on a mature SS installation here. I'm trying to add a new page type but unfortunately I can't because MySQL returns the error message :
    Line 536 in C:\xampp\htdocs\airosu\sapphire\core\model\MySQLDatabase.php "Too many tables; MySQL can only use 61 tables in a join" (preceded by the SQL select statement including every table in the database!).

    Is this an upper limit for SS? Is there any way around this using MySQL? I've come across this before and trimmed one or two tables away but I need to add about 10. The join does not include obsolete tables so deleting them will make no difference.

    Has anybody any ideas?

    Thanks,

    Nick

  • swaiba
    Avatar
    Forum Moderator
    1792 Posts

    Re: Too many tables; MySQL can only use 61 tables in a join Link to this post

    That is a MySQL limit, not silverstripe.

    I've come accross something similar when I tried to have about, well, over 61 has_one for a dataobject. This was not a great solution and I did a very nasty hack to get ti working - the "has_many" end of the relationship I'd add the hasone complex table field just after doing a set_stat('has_one', array( 'obj', 'obj')) to fool ss intot hinking there was a connection and it sorted out my issue (in a terribly hacky way).

  • njprrogers
    Avatar
    Community Member
    23 Posts

    Re: Too many tables; MySQL can only use 61 tables in a join Link to this post

    Unfortunately, it's a Silverstripe limitation as well.

    This issue isn't because of code I have added... it's an issue of scalability with Silverstripe and MySQL itself.

    I'm just adding another simple page type. And because there are already 60 other page types and Silverstripe is doing a join in it's core of all tables when you attempt to access the admin panel.

    I'm not sure why you would need to do this, or how this can be efficient. Joining practically all tables which have been added to the database?

    So, the only solution would be hacking the core which I'm reluctant to do as the site owner wants to stay on the Silverstripe upgrade path.

  • swaiba
    Avatar
    Forum Moderator
    1792 Posts

    Re: Too many tables; MySQL can only use 61 tables in a join Link to this post

    well a bit of both then...
    I've just had a look at the number of pages in my systems and I've got one nearing the 61 limit!

    I've always been quite lazy with pages - creating new ones instead of using the allowed operations and rendering with a different type.
    But I use a bunch of pages that are hidden - so I hope they are not going to be included in the sitetree rendering.

    All of this is of course down to the ORM.

    I'd advise you raise you issue in open.silverstripe.org to get progress from the ss team.

  • njprrogers
    Avatar
    Community Member
    23 Posts

    Re: Too many tables; MySQL can only use 61 tables in a join Link to this post

    I know, this is a problem that has been skirted around for a while here. We've trimmed a page type or two when we've needed to add one but now we have a big project and I need about 10 new page types. It's a multi-country site as well which has blown things out a bit more than normal.

    Yikes!

    Thanks for your feedback. I'll further this with the SS team... We've a couple of options, a refactoring project to cut down on page types or maybe a switch to another database. Or lastly another CMS... should really be using some enterprise software here but the flexibility of SS is very attractive.

    Cheers

    2345 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.