Thread

We have a large EE installation with MSM and currently 15 sites. I went to create a new site and ran into the following SQL error:

MySQL ERROR:
Error Number: 1117

Description: Too many columns

Query: ALTER TABLE exp_weblog_data ADD COLUMN `field_ft_1479` tinytext NULL

The site appears to be created, but it is likely incomplete and with bad data now.

This KB article seems to tell me that I’m hosed:
http://expressionengine.com/knowledge_base/article/how_many_custom_fields_can_my_installation_support/

It seems my only option might be to move other sites off of this installation of ExpressionEngine and onto another, to get around the custom field limit.

Does EE 2.0 have this same limitation imposed by how one global table is used for storing all weblog data for all sites?

Hi, iso100 - what version and build are you on, and how many custom fields do you have?

1.6.7 Build 20090211.

How can I get a custom field count? We have 15 MSM sites and counting manually could be quite tedious.

Don’t know why I didn’t think of this earlier, but exp_weblog_fields reports 975 fields.

Don’t know why I didn’t think of this earlier, but exp_weblog_fields reports 975 fields.

I am afraid that really is a lot.

It seems my only option might be to move other sites off of this installation of ExpressionEngine and onto another, to get around the custom field limit.

If you can’t reduce the overall number of custom fields, I am afraid that is the one other option you have.

Does EE 2.0 have this same limitation imposed by how one global table is used for storing all weblog data for all sites?

Yes. This is really a MySQL limit, I am afraid, and there’s no workaround that I am aware of. Please feel free to make a Feature Request, but for now 2.0 uses the same database structure here.

Yes. This is really a MySQL limit, I am afraid, and there’s no workaround that I am aware of. Please feel free to make a Feature Request, but for now 2.0 uses the same database structure here.

I consider that a DB Schema limitation and I was really hoping you’d have a different answer. I would think that a table for each site’s custom fields would get past this restriction and even provide a higher level of performance, even. The current table structure is flawed and doesn’t scale up well… something I didn’t know would happen yet we asked this of EllisLab pre-sales before deciding on the platform. Now I’m left to answer to my manager about why I didn’t foresee this happening…

I’m pretty certain even a feature request won’t go anywhere though, as it would be altering the entire DB schema and would likely pull the rug out from under countless extensions and modules.

Damn.

I can’t believe I’m the only one running into this limitation though.

On another related note… is there an “easy” way to split off an MSM site and move it into another installation of EE?

To clarify on the presales comment: We asked about the possibility of EE handling upwards of 15-20 sites on an MSM installation.

I consider that a DB Schema limitation…

I am not going to argue semantics here, but the way things currently work, you are going to run into this limit.

I would think that a table for each site’s custom fields would get past this restriction and even provide a higher level of performance, even. The current table structure is flawed and doesn’t scale up well

I am not a programmer; so all I can suggest, again, is a Feature Request. I am sorry not to have better news.

... something I didn’t know would happen yet we asked this of EllisLab pre-sales before deciding on the platform. ... We asked about the possibility of EE handling upwards of 15-20 sites on an MSM installation.

Yes, EE can handle that. It’s the ~1000 custom fields on top of it that are causing the problem.

... it would be altering the entire DB schema and would likely pull the rug out from under countless extensions and modules.

We have changed the schema before to address this issue (in 1.6.5, I think). Modules etc. should be using EE’s functions anyway instead of relying on existing tables, so I don’t think that would too much of an issue.

I can’t believe I’m the only one running into this limitation though.

I don’t think you are, although it doesn’t come up very often.

On another related note… is there an “easy” way to split off an MSM site and move it into another installation of EE?

It’s not too difficult, using the site_id, but it would still need to be done manually; there is no automated process.

Just to confirm what Ingmar said, EE has handled installations with more more than 15-20 Sites without any scaling problems at all.  The problem here is not the number of Sites, but is instead the number of custom fields.

Can you perhaps tell us why you need so many custom fields?  Perhaps there is a better solution for you. We rarely see installations that actually need such an incredibly high number of custom fields.

As for your current installation, it would be advised to roll back the backup you made prior to duplicating this Site, to ensure that you do not have database corruption.

Long story short:

We had a 3rd party contractor build our first major EE site (not my decision) and essentially they “learned” EE on the job. Many of the things they did I was not happy with, but wasn’t able to fix during development. That site launched and went live.

We decided to launch our first localized version of that site using our internal team. We quickly found out that Gypsy field data does not come across when duplicating an MSM site, and this caused much of our content to have to be reentered. Since we were going to build many future sites off this site’s content and structure (with modifications) we needed to remove Gypsy from the equation. To get things done quickly, I had my developer just create custom fields for each site section unique to that section. This is likely the cause of great number of custom fields.

In reality, some of the fields aren’t in use in various sections… so I’ll have to go through and delete those that aren’t likely to be used. Without Gypsy though, the number of fields is likely to still be very high, as this is a full corporate site with many different content types for each site section. We will likely have to split off these sites onto separate installations of EE, greatly complicating administration, etc

I guess I really should put in a feature request for a better way to handle exp_weblog_data… because this is a limitation of EE that should be mentioned in pre-sales information. Potential customers need to know that there is a fuzzy yet limited number of custom fields that are possible with an EE installation, even if the site performance is fine.

In reality, some of the fields aren’t in use in various sections… so I’ll have to go through and delete those that aren’t likely to be used. Without Gypsy though, the number of fields is likely to still be very high, as this is a full corporate site with many different content types for each site section. We will likely have to split off these sites onto separate installations of EE, greatly complicating administration, etc

I understand your frustration. Is there any way you could have the fields do double duty? In other words, depending on the weblog use a different subset of fields. I’ve done that before.

But that also depends on the members entering content.

I’m going through the site today trying to identify areas of possible overlap. There is at least one area we can do that and I’ll look into creative ways to this in more sections.

It’s a shame we can’t assign multiple field groups to a section. That would resolve most of this.

Sounds good. Let us know if there’s anything we can do for your from a Tech Support point of view. Sorry this is such an issue for you.