Thread

The title says it all: when creating a new site in the Multiple Site Manager I get the following error:

MySQL ERROR:

Error Number: 1117

Description: Too many columns

Query: ALTER TABLE exp_weblog_data ADD COLUMN `field_ft_1016` varchar(40) NOT NULL default 'none'

Something tells me this isn’t going to have a quick fix, but any suggestions?

[Moderator Edit: Moved to Technical Support forum]

Wow. That’s quite a few fields. Is there any particular reason you’re using that many?

Which build of 1.6 are you running?

Sue,

I’m working with nomolos on this site. We have 23 MSM that we are building, and it needs to be released this week. We have been working with Derek and have a pre-release of the next MSM build.

The reason for so many fields is that we have a ton of custom fields because each section of the site is highly custom.

Any thoughts,
bjs

I don’t know if this has anything to do with it:

http://forums.mysql.com/read.php?21,39908,161217

Also I ran some numbers, we currently only have 8 sites using MSM with each site having 10 weblogs and 65 custom fields, so that would be a total of 520 custom fields between 8 sites.

We have to have a total of 23 sites.

bjs

You mentioned working with Derek with a pre-release of MSM.

I pointed out this thread to him, I’ll let you know what he has to say about it.

The maximum number of columns per table is likely being reached, or more specifically, the maximum row width.  There’s a hard limit of 4096 columns per table, but as the MySQL documentation puts it: “the exact limit depends on several interacting factors.”  Primarily, the length of a given row cannot exceed 64k.  For utf8, for instance, VARCHAR fields contribute their length * 3 bytes to allocate enough space for the row.

Each ExpressionEngine custom field has at least two columns, typically 1 TEXT field to hold the contents, and 1 VARCHAR field to hold the field’s formatting type.  The content column contributes only 9-12 bytes to the row length, because MySQL stores TEXT fields elsewhere.  The formatting field column contributes up to 122 bytes to the row width (length of 40 times 3 in the case of a utf8 database, plus two bytes to store the length).  Date fields and relationship fields are stored a bit differently, but the point is: you’re never likely to hit MySQL’s limit on the number of columns per table because the columns each contribute to the maximum row width.

So let’s say you have 520 custom fields, and assume that your database is utf8:

bytes        from
 6240          520  content columns
63400       520  field formatting columns
————————————————————
69640 bytes

Unfortunately, these sites will need a major re-think in how weblogs are being used to store and display content.  Not being familiar with your site’s needs, I cannot recommend anything specific.  But knowing that so far the sites have been duplicating weblogs and have identical custom fields, I’m wondering if you would be able to just have one weblog, and perhaps use categories to determine whether an entry was intended for one site or another.  The site= parameter of the weblog tag would allow the templates of each site to display information from this one master weblog.  That way, you have a total of 65 custom fields, period.

Of course, looking at why you have so many custom fields would be good too.  What are each of the custom fields being used for?  It’s likely that many could be repurposed as a single custom field, or as statuses, categories, etc.  That might not be the case, but again, without more detailed information, it’s hard for us to make firm recommendations that you can immediately put into practice.

Hope you find a resolution in time!

Edited to add: the version of MSM bjs is using is the same that is in the latest build update of 20070918

I’m wondering if you would be able to just have one weblog, and perhaps use categories to determine whether an entry was intended for one site or another.  The site= parameter of the weblog tag would allow the templates of each site to display information from this one master weblog.

I am dealing with 108 sites in the MSM. Right now I am still determining why duplicating a weblog is relevant. I posted a question on this very subject. http://ellislab.com/forums/viewthread/125361/

Category groups do not work cross-site, you need to create and assign the category group on its respective site.

So, how would I use categories? This project is very complex and I am still looking for answers before I leap in and start construction.

If you duplicate a weblog when creating a new Site, then its custom fields are also duplicated.  If the content is truly shared with no unique needs, then you can just use one weblog, and reference it from the front end of the other sites using the site= parameter.  In all but the most unique cases, there’s no need to duplicate weblogs, fields, and data if the sites are sharing information.

Categories are not shared in the database, but if named identically on each site, can be used to your advantage to present it as a single category from the visitor’s perspective.

Hopefully you appreciate that I cannot give you a one-shot answer based on what little information about your site and data structure you’ve put in front of me.  Your particular solution might entail something different than the general ideas mentioned above.  Can you elaborate?

I have not run into this problem yet, but as I looked over the structure of how the ee database stores weblog fields, and I am anticipating running into this problem, and I am kind of worried about it…

Here is the situation, I am developing a site with the MSM that will run many identical sites (potentially 20-50+) using the same domain name, but will change the site_id= parameter dynamically in the site (using a cookie retrieved by php and parsed on input). The MSM mostly comes into play because for the CP user permissions, as there will be many different user groups in different locations editing the site and they should only have access to the particular site they are assigned to. I don’t see how it would be possible to apply the same user permissions which are so handy with the MSM without setting up similar weblogs under each individual site.

The setup of the columns in the exp_weblog_data seems very counter intuitive, especially given the fact that repetition of weblogs might be something common (well documented in the feature request here http://ellislab.com/forums/viewthread/110247/ which is also something I have had a terrible time with), and the ability to have separate MSM sites is sort of mission critical in this project. Do you have any recommendations of how I should continue, or are there any plans of restructuring the exp_weblog_data table in EE 2.0 or in any upcoming 1.6.x release?

Also, this seems to be something that should be addressed not just for field repetition, but for those looking to build large sites using EE and the MSM module (such as this one http://ellislab.com/forums/viewthread/126556/)

Thanks for your help

-Tim

No, no restructuring is planned in the immediate future.  We’ve looked into this a number of times, and there are a few key features of ExpressionEngine that are either impossible or extremely resource intensive with a differing schema.  There are also some unique advantages to having discrete field groups per site.

We will continue to look at ways to make improvements and broaden the market for MSM, but the short answer is, we never built MSM with the intention that people would be managing hundreds of sites - the licensing would exclude that for all but the most extreme cases (the organization having to fully own the property of each site), so that’s not the audience we are targeting with the MSM expansion.

Now for your sites in particular, I can’t give you any sound advice without having a greater understanding of the content needs of the site.  Would you mind dropping me an email (MSG button at left) with more concrete details about your project?

email sent, thanks

This thread has raised some questions on our approach and MSM.

Currently if I run this…

SELECT * FROM `exp_weblog_data`

I get 816 columns.

And we have quite a bit more expansion to do on our sites.

Should we be thinking about separate EE installs at this point?

Also, what other tell tale signs can one look for prior to hitting the wall with mysql.

I want to head this off at the pass if our approaches are leading us to a dead end with db limitations.

Thoughts?

This thread has raised some questions on our approach and MSM.

Please note that the numbers from the 2007 posts in this thread are no longer relevant.  Since version 1.6.5, the schema was altered to increase the maximum theoretical limit.  Unless you use tons and tons of date and relationship fields, you aren’t going to hit that ceiling.  Doesn’t mean you don’t still need to give careful thought to your data structure, of course.

Currently if I run this…

SELECT * FROM `exp_weblog_data`

I get 816 columns.

Do you mean 816 rows?  That query would show you all columns, but wouldn’t count them for you.

Yeah, I guess the query is irrelevant. I did mean 816 columns though in the weblog data table. Is this an area that should concern us… that many columns in the table and growing?

Yes, I’d be concerned.  It’s not necessarily a cause for alarm, but I’d definitely examine the site’s data structure for possible improvements.  With a site that large, I’d even recommend getting some outside consulting, such as a performance evaluation by Solspace.