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.
"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.)