2

So I have a CMS that is being upgraded from MySQL 5.5 -> 5.7. One table has a 1000+ columns, but it works fine in 5.5 and 5.6. When reaching 5.7, queries involving this table result in a 1117 error. (all tables in DB are MyISAM).

It works fine in MySQL 5.5 (the related CMS works fine). I upgrade to MySQL 5.6 and everything continues to work fine.

Then when I upgrade to MySQL 5.7(.23), I get an error about too many columns, and even after removing 20+ columns, I still get the error.

What could have changed? I upgraded in order (5.5 -> 5.6 first, tested, then 5.6 -> 5.7)

Is there possibly a setting I'm missing? Does the mysql_upgrade for 5.7 change columns somehow and screws up their limit by adding extra meta data that causes each column to take up more data? I know that column limit is theoretical but I don't know why it works in the previous two versions and not in 5.7.

Any thoughts?

Edit

After removing ~300 columns, the error stops. But this is a show stopper, and I'd really like to know why 5.7 can't handle what 5.5 and 5.6 can, and if it is a configuration issue or something. Thanks for any help.

Improve this question
4
  • Welcome to DBA.SE. We appreciate your participation. How may columns does your table actually hold? And how many did you actually remove in the end? These figure might be relevant. What data types are these columns? All the same type? Varying types?
    – John K. N.
    Oct 26, 2018 at 6:19
  • 1402 columns. About 49.5% text, 49.5% tiny text columns. Maybe 10 int columns, maybe 6 or so varchars. I didn't count exactly as almost all are either text or tiny text (each text column has a corresponding tiny text column). I'm not sure how many I removed, I'd have to re-rehearse removing columns via the CMS, but the table was in the 950~ range of columns when it started working, so more like 400-450 removed.
    – jrothafer
    Oct 26, 2018 at 6:34
  • I checked table data between 5.5 and 5.7, and everything is the same (same row format, column count, row count, avg row length, data length, index length, table size, collation). So 5.7 is treating the same data differently somehow.
    – jrothafer
    Oct 26, 2018 at 7:03
  • Are you doing a JOIN? Is FULLTEXT involved?
    – Rick James
    Oct 27, 2018 at 22:37

2 Answers 2

Reset to default
1

A guess... See what you get for

SHOW VARIABLES LIKE '%engine%';

This may indicate that you are not really using MyISAM everywhere.

In the future, MyISAM is likely to go away. So, start thinking about how to deal with InnoDB.

TINYTEXT is perhaps always worse than a suitable VARCHAR.

More than a hundred columns in a single table is usually a bad schema design. Think about how your table can be logically broken up.

Don't splay an array across columns.

Improve this answer
1

I could not execute queries because of the "too many columns" error since it brought many columns, I changed the variable internal_tmp_disk_storage_engine to MyISAM and it was fixed.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.