1

I have this error:

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

To solve this can i just change InnoDB to MyISAM?

asontu
  • 4,548
  • 1
  • 21
  • 29
Marcin Jaworski
  • 330
  • 2
  • 9
  • 1
    Sounds like a schema issue. Large rows usually mean you're storing some data in a row with names like column1, column2... columnN that should be in a separate table. – Joel Coehoorn Apr 09 '15 at 15:42
  • You are right, I made mistakes but now when I change table I need change many functions and probably making new errors. – Marcin Jaworski Apr 13 '15 at 13:04
  • 1
    possible duplicate of [Change limit for "Mysql Row size too large"](http://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large) – GolezTrol Apr 16 '15 at 15:10
  • I'm not DB owner and i don't have permission to changing settings. I can set tables options. When i select InnoDB i can choosing beetwen compact or redundant in ROW_FORMAT. Dynamic or compressed i have when i choosing MyISAM. – Marcin Jaworski Apr 17 '15 at 06:41

1 Answers1

5

Yes, you could switch to MyISAM. But that is not necessarily a good idea:

  • MyISAM does not support transactions
  • MyISAM tables often need REPAIR after a crash

An InnoDB table can handle more than 8KB per row. Apparently you ran into the problem by having a dozen or more TEXT/BLOB columns? At most 767 bytes of a column is stored in the main part of the row; the rest is put in a separate block.

I think one ROW_FORMAT will put all of a big columns in a separate block, leaving behind only 20 bytes to point at it.

Another approach to wide rows is to do "vertical partitioning". That is, build another table (or tables) with a matching PRIMARY KEY and some of the big columns. It is especially handy to move sparsely populated column(s) to such a table, then have fewer rows in that table, and use LEFT JOIN to fetch the data. Also, if you have some column(s) that you rarely need to SELECT, then those are good candidates to move -- no JOIN needed when you don't need those columns.

Rick James
  • 135,179
  • 13
  • 127
  • 222