Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions /

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

Too many tables; MySQL can only use 61 tables in a join


Reply


5 Posts   2579 Views

Avatar
njprrogers

Community Member, 23 Posts

16 September 2011 at 5:47am

Edited: 16/09/2011 5:49am

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

Avatar
swaiba

Forum Moderator, 1805 Posts

16 September 2011 at 8:07pm

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

Avatar
njprrogers

Community Member, 23 Posts

16 September 2011 at 9:04pm

Edited: 16/09/2011 9:05pm

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.

Avatar
swaiba

Forum Moderator, 1805 Posts

16 September 2011 at 9:18pm

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.

Avatar
njprrogers

Community Member, 23 Posts

16 September 2011 at 9:23pm

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