Row size too large + innodb_log_file_size flag

[Mpp Loop]

Are there any plans to allow the innodb_log_file_size  setting to be changed view the allowed flags?

Currently having trouble importing a database export of MyISAM tables into my Cloud SQL server. When I hit a certain table that has many columns, the import fails with the following error:

MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB

Currently it does not seem we can change the innodb_log_file_size flag on Google Cloud SQL and from what I can see that is one of the leading solutions. That is, to update innodb_log_file_size flag according to the MySQL documentation:

As a result of the redo log BLOB write limit introduced for MySQL 5.6, theinnodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR,VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.

Aside from hoping Google Cloud  eventually allows us to update that flag, if anyone has any ideas or suggestions on other ways to remedy this issue it would be greatly appreciated.

Thank you.

-Mike

[Kenworth (Google Cloud Platform)]

The feature to update the flag has already been initially reported on this issue tracker.

As a workaround, setting the 'innodb_strict_mode' to OFF often resolves the issue.  You can try the following instructions / explanation to add the table to your database:

Set innodb_log_file_size to 512MB

Set row_format to default/dynamic

Set innodb_strict_mode to OFF

How to add the table:
1.  Open your create_script.sql file with a text editor, and add one line above the "CREATE TABLE" line, the file should look like this afterwards:  

SET innodb_strict_mode = 0 ;

CREATE TABLE `history_data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `coffee` longtext,
  .....

2.  Add the table to your database instance, replacing HOSTNAME with the hostname of the server, replacing DATABASE with the name of the database, and ensure that the path to the script is correct.

$ mysql -uroot -h HOSTNAME DATABASE < ~/path/to/create_script.sql

3.  Verify that the table has been created

If the above does not mitigate the issue, please open a new issue tracker and provide the redacted result of the following commands:

1. SHOW GLOBAL STATUS\G; 
2. SHOW VARIABLES\G; 
3. SHOW ENGINE INNODB STATUS\G; 

[Mpp Loop]

Kenworth,

Thanks for the reply. Adding the innodb_strict_mode to off before the CREATE statement of the offending table did the trick. Much appreciated!

My prior assumption on GCP was if any particular flags (such as innodb_strict_mode) were not exposed within the allowed set via the GCP cloud sql admin that they couldn't be changed at all. But clearly this isn't the case!

Thank you.

-Mike

[Mpp Loop]

Adding onto this thread after a new development. When creating my production database I enabled the failover replica database option.

While successful in making these "innodb_strict_mode" changes on the Production database (where all is running smoothly) I checked today and noticed that the Failover database is unavailable.

In checking the Failover database logs I saw the "row size too large" issues that took place in production prior to making the changes to innodb_strict_mode.

My assumption was the failover database would mimic the Production database. But do I need to specifically update the innodb_strict_mode on the failover database as well?

Thanks for any feedback...

-Mike

[Kenworth (Google Cloud Platform)]

Yes, when you create a failover replica it will have the same configuration (flags, users, etc) as the master. But if you modify / add a flag on the master after creating the replica, this won't be reflected on the replica automatically.

[Mpp Loop]

Kenworth, 

I figured as much and deleted the failover then created a new one. Hopefully I'll be good to go now.

Thank you!

-Mike