Too Many Columns in MySQL - error 1117

Question

I just added a new field to my table in mysql and it came back with a warning of "1117: too many columns"

The table has (gasp) 1449 columns. I know, I know it's a ridiculous number of columns and we are in the process of refactoring the schema but I need to extend this architecture just a bit more. That said, this doesn't seem to be reaching the theoretical limit of 3398 as per the mysql documentation. We are also not close to the 64K limit per row as we are in the 50K range right now.

The warning does not prevent me from adding fields to the schema so not sure how it fails if at all. How do I interpret this error given that it does not seem to cause any issues?

Answer 1

Perhaps some of these factors are adding to the total byte count:

http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

e.g., if a column allows nulls, that adds to the total or if unicode is used, that more than triples the space required by character columns, etc...

for MyISAM:

row length =
  1 +
  (sum of column lengths) +
  (number of NULL columns + delete_flag + 7)/8 +
  (number of variable-length columns)

you could check if it's indeed a row size issue or a column count issue, by adding just a tinyint not null column, then dropping that and adding a char(x) column until you get the error.

Answer 2

Warnings are exactly that: warnings. It means you're okay for now but, if you continue with the behaviour that elicited the warning, you will be punished in one form or another (I'm just trying to get this concept through to my three-year-old daughter at the moment so forgive me if it seems a little simplistic).

That's MySQL being polite. It s next step, if you continue down this particular path is to refuse to co-operate (probable) or flat-out lose your data (less likely but karmic in a dark-humour sort of way), neither of which you want.

The right response is to listen to what it's telling you. If you want a quick'n'dirty fix while you're thinking about the best way to refactor, you can split the row across two tables with a common identifier.

This will make your queries (temporarily) ugly but will at least stop the warnings. But don't use this as a final solution. I'm finding it very hard to imagine an item that would have thousands of attributes that couldn't be organised into a better hierarchy.