Mysql 5.7 too many columns

#1 / Mar 22, 2011 6:33pm

We just started getting this error when trying to duplicate a MSM site in Site Management:

A Database Error Occurred
Error Number: 1117

Too many columns

ALTER TABLE exp_channel_data ADD COLUMN field_ft_1083 tinytext NULL

Filename: controllers/cp/sites.php

Line Number: 1204

Looking at exp_channel_data, it does indeed have a ton of columns. (The last column is field_ft_1088.)

We have 25 sites on this EE server. Each has 31 fields across 8 field groups. So, we’re not talking crazy numbers here.

From the looks of things, you’re creating a two new columns for each unique field across every site. Is that expected?

Are we just missing a MySQL config setting to allow more columns? Or have we hit some kind of undocumented max?

We had planned on rolling out another 10-20 sites on this server. If we’ve hit a wall, we’ll need to change course right away.

EE 2.1.1 Build 20101020

#2 / Mar 22, 2011 6:43pm

You’re hitting MySQLs limit. You can change the column types to more accurately reflect the type of data they contain, e.g. a column that stores a date or boolean doesn’t need to be a “text” that is taking up unnecessary space in the table.

I’m not 100% familiar with the differences in MyISAM and InnoDB types but switching the types may help? http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

#3 / Mar 23, 2011 12:01pm

Thanks for that suggestion.

I’m a little hesitant to change the db schema. If we run an EE update in the future is there a chance that changing a column type would break the EE update?

I’d like to hear from EllisLab before I do anything drastic that might cause us problems later. Mostly, I’d like to know if this is expected behavior and we just ran into some kind of undocumented limitation of MSM.

#4 / Mar 24, 2011 4:43am

To begin with the MySQL limit is the source of the error.
What causes it would be the amount of custom fields spread through your MSM sites.

I found 2 other threads on this topic going back though the forums.
One here and the other here

Now looking at your info you don’t seem to have an awful lot of custom fields. Are any of these fields third-party?

I am going start a conversation on this again internally and will come back to you with hopefully more detail

#5 / Mar 24, 2011 9:23am

Thanks for those other threads John. I searched for that error, but didn’t find those for some reason.

This is how our field types break down per site. (Each site has the same fields. So multiply each by 25.)

Matrix: 14
Playa: 6
Text: 5
Text Area: 5
Wygwam: 5
File: 1
P&T Switch: 1
P&T Dropdown: 1

Do Matrix or Playa fields store their data in such a way that chews through the MySQL byte limit faster?

#6 / Mar 24, 2011 4:50pm

Hi, siffring. John just escalated this particular thread, so the dev team has all the information in this thread. I have no idea how Matrix or Playa store their data, you’d have to ask Brandon. Thanks in advance for your patience. Hopefully we’ll hear back from the dev team soon.

#7 / Mar 24, 2011 4:56pm

Thanks Sue. Appreciate you escalating it. I’m happy to buy the dev team beers at EECI if bribery helps grease the wheels. 😊

#8 / Mar 25, 2011 5:10am

siffring,

Below is Derek’s response in this thread as to the why part.

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

Matrix I know now has its own tables for columns and data but it would be worth hearing Brandon’s take on this as I am not overly intimate his add-ons inner workings. The relationship table which Playa uses can be resource intensive too in terms of its size. If you clear your cache before duplicating a site does the error appear?

There is one suggestion that after backing up your database that you try is flipping from MyISAM to InnoDB

Other than that unfortunately you may need a rethink on some of your fields and how you approached the content.