Search code examples
innodbgoogle-cloud-sqlmysql-5.7

How to overcome Row size too large (> 8126) error on Google-Cloud MySQL5.7 Second Generation


Google Cloud MySQL Engine supports the InnoDB storage engine only.

I am getting the following error when creating a table with 300 columns.

[Err] 1118 - Row size too large (> 8126).

Changing some columns to TEXT or BLOB may help. In the current row format, the BLOB prefix of 0 bytes is stored inline.

I tried creating a table with the combination of some columns as text types and some others as blob types as well but it did not work. Even modifying innodb_log_file_size is not possible, as it is not allowed on the Google Cloud-SQL Platform.


Solution

  • "Vertical Partitioning"

    A table with lots of columns is pushing several limits; you hit one of them. There are several reasonable workarounds, Vertical Partitioning may be the best, especially if many are TEXT/BLOB.

    Instead of a single table, have multiple tables with the same PRIMARY KEY, except that one may be AUTO_INCREMENT. JOIN them together as needed to collect the columns. You could even have VIEWs to hide the fact that you split up the table. I recommend grouping the columns by some logical grouping based on the application and which columns are needed 'together'.

    Do not splay an array of things across columns; instead, have another table with multiple rows to handle the repetition. Example: address1, state1, country1, address2, state2, country2.

    Do not use CHAR or BINARY except for truly fixed-length columns. Most of such are very short. Also, most CHAR columns should be CHARACTER SET ascii, not utf8. (Think, country_code, zipcode, md5.)

    innodb_log_file_size is only indirectly related to your Question. What is it's value?

    Directly related is innodb_page_size, which defaults to 16K, and virtually no one ever changes. I would expect Cloud Engines to prohibit changing it.

    (I'm with Bill on desiring more info about your schema -- so we can be more specific about how to help you.)