Too Many Columns in MySQL - error 1117

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 t开发者_开发问答he 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?

---

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.

---

If it is a warning as you say, then you should remember that 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 probably be punished in one form or another.

However, it's more likely that this is an error in that it's refused to actually let you add more columns. Even if it does let you add more columns, that's unlikely to last for long.

So, regardless of whether it's a warning or error, the right response is to listen to what it's telling you, and fix it.

If you need a quick'n'dirty fix while you're thinking about the best way to fix it properly, you can split the row across two tables with a common identifier.

This will make your queries rather ugly but will at least allow you to add more columns to the "table" (quoted because it's actually two tables with a common key).

Don't use this as a final solution, not least of all because it breaks normalisation rules. But, to be honest, with more than a thousand columns, there's a good chance they're already broken :-)

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.