Cannot convert exp_channel_data to INNODB

Question

I’m currently coming against an issue with a large EE2 site that I’ve recently moved to EC2/RDS. To take advantage of the RDS snapshots and not risk data-integrity issues (as per Amazon Recommendation) with the DB Snapshot/Backups, we need to convert all tables to INNODB.

The problem is that my exp_channel_data table is baulking with an error Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline..

All other tables converted fine. We have around 215+ custom fields across the entire site, so that means we've got around 430(ish) columns in the exp_channel_data table.

Anyone come across this or does anyone have advice on how to fix? Google suggests doing some my.cnf changes etc - but RDS doesn’t give me this (or console access), so I'm at a bit of a loss as to how I can sort this.

Looking at the datatypes of all the columns, none of them are particularly large - EE mostly creates fields with datatype of TEXT and TINYTEXT - but I have some in there with Decimals and/or INT.

Answer 1

After spending some months on this exact problem (we have 360 or so custom fields), here's what I've learned:

• RDS snapshots are only consistent for InnoDB tables.
• Converting exp_channel_data to InnoDB is possible, but only if innodb_page_size is large enough (in my case 32k or 64k), but this parameter is not available in RDS because it needs to be set before the database is created, not after. (I tested this using Docker.) A feature request has been made to AWS.
• You cannot split the table into smaller chunks (a vertical partition) and combine them with a view because you cannot add columns or rows to such a view.
• Designing the table as row based, rather than column based is in my opinion the best option (minimal space, simple to add a row, no schema changes anymore), but the source-code impact inside EE is prohibitive since every developer accesses this table differently. Ellislab are aware of the issue and there appears to be some interest in solving this since it was first raised in March 2011.
• At present my work-around^, subject to issues^^ outside the scope of this question, is to create a mysqldump of exp_channel_data every hour and if the need arises, restore the RDS snapshot and then the mysqldump.

^ I'm also looking at FLUSH TABLES WITH READ LOCK and then doing an RDS snapshot manually instead of using mysqldump, but this depends on Meta Data - see below. That way I'll get the whole database in one go and I'll know what it's called.

^^ The issues relate to Elastic Beanstalk deploying RDS and being able to determine which snapshots belong to the RDS instance that is currently running. In essence getting Meta Data from Elastic Beanstalk.