4

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!

Improve this question
1
  • I'm not sure there's a tidy solution for using this table structure with Aurora. This table... it's pretty hideous, to be honest, and seems like it's almost begging to be twisted 90°. But, since it appears to be a commercial product, have you checked with the vendor whether Aurora is a supported back-end? Having only tinkered with Aurora in preview, I can't say conclusively, but I'm skeptical whether it can be made to work without some refactoring, unless Aurora supports ROW_FORMAT=COMPRESSED, which I doubt, in spite of the suggestion in the error message. Commented Aug 29, 2015 at 1:25

3 Answers 3

Reset to default
3

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.

Improve this answer
3
  • It has not much to do with field type (all blob/text types behave the same), but with ROW_FORMAT of the table. Aurora probably has different default than current mysql for that. And if Aurora won't let you change that, then only possible solution for it is splitting the table. Commented Sep 1, 2015 at 11:03
  • Take note. Now every MEDIUMTEXT is stored in some other block, not just the 'long' ones. This probably leads to slower response time. Commented Sep 1, 2015 at 14:43
  • the issue that I found out is that they do not allow you to change the innodb_log_file_size parameter, it's essentially hard coded in aurora. there was a google forum post about this and one participant mentioned that this was a big question at one of their mini conferences and the speaker said that they won't change it and there are no plans to change it in aurora. they said they will change it in their MySQL instances, which is true, but it is already pretty large to accommodate even my import which has stupid long fields. btw. aurora also ignores the row format... ;) Commented Sep 1, 2015 at 16:09
1

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.

Improve this answer
2
  • 1
    If I remember correctly from observations during the Aurora preview, MyISAM tables are coerced to InnoDB with a warning when the table is created. Aurora most definitely has no support for MyISAM, which makes perfect sense given that it almost certainly does its scaling magic internal to InnoDB. Commented Aug 29, 2015 at 0:50
  • 1
    Correct, it makes no difference, if you change it to InnoDB you get the same error - Aurora has only one format I think so it ignores the ENGINE statement. Commented Sep 1, 2015 at 10:45
1

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;

Improve this answer
2
  • 1
    well that is something you need to tell to the vbulletin.com guys but based on my experience so far they wouldn't change anything ;) Commented Sep 1, 2015 at 10:46
  • Grrr... inflexible 3rd party software. Commented Sep 1, 2015 at 14:38

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.