Thread

EE Version 1.6.8 Build:  20091201
MySQL Version 5.0.51a-3ubuntu5.4
MSM Installed

I create a new field group, and then try to create a new text area field in that group. 

On trying to save the new field I get:

MySQL ERROR:

Error Number: 1005

Description: Can’t create table ‘./ee/#sql-d5b_45917.frm’ (errno: 139)

Query: ALTER TABLE exp_weblog_data ADD COLUMN field_ft_517 tinytext NULL

I suspect we are running into MySQL row length limits (as we do have just over 500 fields and some of those are FFM fields) - but am frustrated trying to find tools to debug and confirm that.

I found a “SHOW TABLE STATUS” command to return information about the tables in question - but it’s returning 0 for “max_data_length”.

I’m admittedly a bit out of my league here.  Are there better tools to use to :

A. Determine if I am indeed running into a MySQL limitation.
B. Know what our maximum row length is
C. Be able to determine the effects of any field/IA restructuring.

Have you tried doing a repair or optimize on the table in question?

Beyond that, those are good questions but not ones we can answer directly, as it can vary from host to host. Contact them and hopefully they can help diagnose the issue further.

Keep us posted!

No - I haven’t done anything yet as I wanted to leave it in error state until further notice.

While I understand the answers may be different host to host (but would they be if this was a MySQL limit?), I was hoping for some direction on if there were better ways to return the current and maximum sizes from MySQL.

Mike,

There is actually a limit of 4096 columns per table but every table has a maximum row size of 65,535 bytes. So basically the total width of all these columns cannot exceed this size.

This info is from this MySQL article but I am unsure if your host may have further restricted this.

If you look in a tool like phpMyAdmin what is the table size for exp_weblog_data at the moment? Would it be close to 65,535 bytes?

Thanks John Henry - I don’t have access to the db at that level so am asking the people who do…;)

Let us know what you find out, We’ll be here.

64796 exp_weblog_data.frm
589824 exp_weblog_data.ibd

I take it the “frm” is the row size and the limitation we’re running into?

Error Number: 1005
Description: Can’t create table ‘./ee/#sql-d5b_45917.frm’ (errno: 139)

I can confirm that errno: 139 refers to “Too big row”.

Apart from that you’d probably need somebody well versed in the ways of MySQL…

I’d perhaps suggest getting in touch with Mitchell at Solspace as I know that they offer an evaluation service for servers and although I’ve never used them for that I can definitely attest to them knowing their stuff 😉

Best wishes,

Mark

Let us know how this turns out, Boyink!

Help me understand here…I came across this post from DJones indicating that EE should be good for ~ 2000 custom fields, yet we’re bumping into this issue at around 500 (like the original poster in that thread).

We’ve not added date fields.

if you look at the exp_weblog_data table schema, are there any fields other than entry_id, site_id, and weblog_id that aren’t of type text or tinytext?  char/varchar/date-type fields contribute more to the row length limit than text and blob fields do.

if you’re on 1.6.8, you might try to reuse some custom fields via the gypsy extension

I just had them run me that schema - all fields are text or tinytext.

I’m reluctant to add Gypsy to the mix quite yet - this is already a pretty complex EE MSM install and I noted that Brandon isn’t going to continue to develop Gypsy.

another way you might consolidate is by moving groups of fields into a FF matrix field (limited to a single row).  you probably won’t want to move any fields to the matrix that you need to use the weblog:entries search parameter on though, for performance reasons.  don’t want to have to deserialize the field to find matching rows because the deserialization can only happen in PHP.  even a query on a non-indexed field that forces a full table scan will outperform PHP.

We’re using FFM already - and actually it’s partially to blame for the number of fields that we have.

FFM isn’t fully MSM compatible. I can’t re-use fields across MSM sites and then pull site-specific content - so even though the site would allow shared field groups I have to create site-specific ones.