Mysql – Amazon AWS RDS Aurora Row size too large (VB5)

amazon ec2amazon-rdsMySQLmysql-5.5

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!

Best Answer

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.