Amazon AWS RDS Aurora Row size too large (VB5)

Question

We're trying to import our vbulletin 5 database into RDS/Aurora and getting this:

ERROR 1118 (42000) at line 5733: 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.

Here is the table structure for the insert that is failing:

DROP TABLE IF EXISTS `language`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `language` (
  `languageid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  `userselect` smallint(5) unsigned NOT NULL DEFAULT '1',
  `options` smallint(5) unsigned NOT NULL DEFAULT '1',
  `languagecode` varchar(12) NOT NULL DEFAULT '',
  `charset` varchar(15) NOT NULL DEFAULT '',
  `imagesoverride` varchar(150) NOT NULL DEFAULT '',
  `dateoverride` varchar(50) NOT NULL DEFAULT '',
  `timeoverride` varchar(50) NOT NULL DEFAULT '',
  `registereddateoverride` varchar(50) NOT NULL DEFAULT '',
  `calformat1override` varchar(50) NOT NULL DEFAULT '',
  `calformat2override` varchar(50) NOT NULL DEFAULT '',
  `logdateoverride` varchar(50) NOT NULL DEFAULT '',
  `locale` varchar(20) NOT NULL DEFAULT '',
  `decimalsep` char(1) NOT NULL DEFAULT '.',
  `thousandsep` char(1) NOT NULL DEFAULT ',',
  `phrasegroup_global` mediumtext,
  `phrasegroup_cpglobal` mediumtext,
  `phrasegroup_cppermission` mediumtext,
  `phrasegroup_forum` mediumtext,
  `phrasegroup_calendar` mediumtext,
  `phrasegroup_attachment_image` mediumtext,
  `phrasegroup_style` mediumtext,
  `phrasegroup_logging` mediumtext,
  `phrasegroup_cphome` mediumtext,
  `phrasegroup_promotion` mediumtext,
  `phrasegroup_user` mediumtext,
  `phrasegroup_help_faq` mediumtext,
  `phrasegroup_sql` mediumtext,
  `phrasegroup_subscription` mediumtext,
  `phrasegroup_language` mediumtext,
  `phrasegroup_bbcode` mediumtext,
  `phrasegroup_stats` mediumtext,
  `phrasegroup_diagnostic` mediumtext,
  `phrasegroup_maintenance` mediumtext,
  `phrasegroup_profilefield` mediumtext,
  `phrasegroup_thread` mediumtext,
  `phrasegroup_timezone` mediumtext,
  `phrasegroup_banning` mediumtext,
  `phrasegroup_reputation` mediumtext,
  `phrasegroup_wol` mediumtext,
  `phrasegroup_threadmanage` mediumtext,
  `phrasegroup_pm` mediumtext,
  `phrasegroup_cpuser` mediumtext,
  `phrasegroup_accessmask` mediumtext,
  `phrasegroup_cron` mediumtext,
  `phrasegroup_moderator` mediumtext,
  `phrasegroup_cpoption` mediumtext,
  `phrasegroup_cprank` mediumtext,
  `phrasegroup_cpusergroup` mediumtext,
  `phrasegroup_holiday` mediumtext,
  `phrasegroup_posting` mediumtext,
  `phrasegroup_poll` mediumtext,
  `phrasegroup_fronthelp` mediumtext,
  `phrasegroup_register` mediumtext,
  `phrasegroup_search` mediumtext,
  `phrasegroup_showthread` mediumtext,
  `phrasegroup_postbit` mediumtext,
  `phrasegroup_forumdisplay` mediumtext,
  `phrasegroup_messaging` mediumtext,
  `phrasegroup_inlinemod` mediumtext,
  `phrasegroup_hooks` mediumtext,
  `phrasegroup_cprofilefield` mediumtext,
  `phrasegroup_reputationlevel` mediumtext,
  `phrasegroup_infraction` mediumtext,
  `phrasegroup_infractionlevel` mediumtext,
  `phrasegroup_notice` mediumtext,
  `phrasegroup_prefix` mediumtext,
  `phrasegroup_prefixadmin` mediumtext,
  `phrasegroup_album` mediumtext,
  `phrasegroup_socialgroups` mediumtext,
  `phrasegroup_advertising` mediumtext,
  `phrasegroup_tagscategories` mediumtext,
  `phrasegroup_contenttypes` mediumtext,
  `phrasegroup_vbblock` mediumtext,
  `phrasegroup_vbblocksettings` mediumtext,
  `phrasegroup_vb5blog` mediumtext,
  `vblangcode` varchar(12) NOT NULL DEFAULT '',
  `revision` smallint(5) unsigned NOT NULL DEFAULT '0',
  `phrasegroup_ckeditor` mediumtext NOT NULL,
  `phrasegroup_cpcms` mediumtext NOT NULL,
  `phrasegroup_navbarlinks` mediumtext NOT NULL,
  PRIMARY KEY (`languageid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Here is the INSERT that is failing: https://gist.githubusercontent.com/tobsn/a7e573f0df69f483023b/raw/7ae033b921474a2081f5f97a5c348bb08b02d56c/gistfile1.txt

I already tried to set the table to InnoDB and raise the amount in innodb_log_file_size but it seems like this config variable is not accessible on RDS. I tried to convert the fields into text, longtext, and blob and none of that solved the issue.

THANK YOU!

Answer 1

Well, after researching into this and trying out around 20-30 possibly solutions I could not make it work and as far as I can see the limits are set in Aurora and can NOT be changed. Hence the import fails in every single possible solution.

I killed the Aurora instance and created a MySQL one, imported without any hiccups right away. The import or log size limit is set to a much higher default and doesn't choke on the data. Amazon was quoted to say that in Aurora they will not, so far, ever change the limits on this in Aurora. On the other side in MySQL they made it a editable parameter some time ago with, as I said above, already much higher limit.

TLDR: Aurora will always crap out on you with too large fields no matter what field type you set - use RDS MySQL.

Answer 2

You show "create table" with MyISAM engine but the error comes from InnoDB so either you changed it or RDS did.

From manual:

"Tables created in older versions of MySQL use the Antelope file format, which supports only ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, MySQL stores the first 768 bytes of BLOB, VARCHAR, and TEXT columns in the clustered index record along with the primary key. The 768-byte prefix is followed by a 20-byte pointer to the overflow pages that contain the rest of the column value.

It means that your row size may grow 768 bytes per any variant of TEXT column if you use those formats. If your data in any actual row get over the 8k limit in total, you get the error. That happens if you have many not so short texts in the fields, one long text won't trigger.

You can try to change the ROW_FORMAT to one which does not keep the prefix inline or you can split the table into two or more where each one will have some reasonable number of blob/text columns.

You might try to use MyISAM instead but I am not sure RDS supports that very well.

Answer 3

Wrong design. When you need one more phrase, you have to ALTER TABLE, which is a costly operation.

Instead...

1. Build a new, 3-column, table PhraseGroups with PRIMARY KEY(languageid, phrasegroup). It will have L*P rows, where L = number of languages and P = number of phrasegroups. phrasegroup would be VARCHAR(44) and contain 'global' or 'forum' or 'style' or 'user', etc. Plus a languageid (not AUTO_INCREMENT and a MEDIUMTEXT.
2. Copy the phrasegroups into the new table.
3. DROP all the phrasegroup columns from that table. This leaves table language with meta info about the language, such as thousand_sep.

This will be a much cleaner schema, and totally avoid the 8126 limitation.

(and it works for MyISAM or InnoDB; any ROW_FORMAT)

But, since you have a file ready to LOAD, here's how to get there: Build the table as you have it, specifically with ENGINE=MyISAM, but name it something like 'raw'. Then perform my 3 steps. Step 3 might be better done as CREATE TABLE languages... SELECT ... FROM raw;