Google cloudsql issue when update TEXT field

[channing dev]

Hello,

I have a GAE instance on Google cloudsql and created one database on it, in the database there is a table which have 40 fields, and have 5 text fields.

The issue is when I run update sql to update one of the text field, cloudsql alert me with below error:

[Err] 1118 - 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.

I can make sure the field is text and the update value is less than 64 kb, and in my another table which have 10 fields we can update successfully use the same sql.

Does cloudsql have this issue when a table have more than some fields? e.g. more than 30? or 35? 

Thanks.

[Nick (Cloud Platform Support)]

Hey Channing,

This isn't a Cloud SQL restriction but rather an InnoDB restriction. You can read about it in the links given in this Stack Overflow Q&A from a user who saw the exact same error message.

Cheers,

Nick
Cloud Platform Community Support

[channing dev]

Hi Nick,

OK. I found some documents about this, found one way to solve it. That is I want to change InnoDB to Barracuda format. How to config this on Google Cloud SQL? There is no this tag on Cound SQL console. Or you have some other advice to me?

Thanks.

[Nick (Cloud Platform Support)]

Cloud SQL only supports a subset of MySQL flags, although you could file a Feature Request in the Public Issue Tracker for Cloud SQL to see innodb_file_format supported. Are any of the other options from my last post possible to use?

[channing dev]

Nick,

I see. Now I will firstly change my table structure, it seems no other way as I know.

Thank you.

[Nick (Cloud Platform Support)]

Let me know how it goes, whatever you decide to do, and feel free to post back here if you run into any troubles. I'm sure this thread could be useful to future users.

Cheers,

Nick
Cloud Platform Community Support

[channing dev]

Nick,

At before I have 5 TEXT and so many char(255) and varchar(2000). Now I have changed to 2 TEXT and 3 MEDIUMTEXT, and change char(255) to varchar(255), change varchar(2000) to varchar(500), so it works fine. 

But the issue in InnoDB I think is exist, Google cloudsql should support innodb_file_format flag. 

OK, I will try to request a Public Issue Tracker to Google to make GAE more powerful.

Thank you.

[Nick (Cloud Platform Support)]

I'm glad to hear you were able to run the update query once the fields had been modified. I was worried you'd be unable to shrink the fields. I expect you'll see huge savings of efficiency and storage space by changing CHAR to VARCHAR and generally shrinking the size of various fields.

You might also want to consider further normalization of the table, linking the primary key ID to rows in another table which stores some of the columns which might not be necessary to retrieve on each query, allowing JOIN queries to be run in the case that those columns are needed as well.

We look forward to logging the Feature Request you make - we're always looking for suggestions from users as to what they believe would be the most useful features to work on.

Cheers,

Nick

[Nick (Cloud Platform Support)]

* ALTER TABLE query, not UPDATE query! Anyways, I hope it would be clear from the context.

Feel free to update the thread if you find any other useful information that could help other users.

Cheers!

Nick
Cloud Platform Community Support

[channing dev]

Nick,

Thank you very much.