MySQL row size error

Question

I can not understand this issue. I have a table that is well under the InnoDB minimum for a row size, and I started getting an error when the last field in the row was being filled. Last field being a 1280 (varchar) field, attempting to place 879 characters of data within it. Returns:

#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.

Ok so I read up and figured I had too much overhead in the row, so I split it in two. I did this by taking a large section out of the front end form that was talking to the table, and storing it in a separate table altogether. I modified my php to handle two tables instead of one. Made no difference - same error trying to fill out the same last field with the same 879 characters in the now severely shrunken table.

So I try making the table ROW_FORMAT=DYNAMIC - still same result. So I try ROW_FORMAT=COMPRESSED - and yes, still the very same problem. This is driving me nuts.

What is left to do? What am I missing?

As requested, here is my table structure:

DROP TABLE IF EXISTS `app_AIGPrReq`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `app_AIGPrReq` (
  `appID` int(11) NOT NULL AUTO_INCREMENT,
  `uID` int(11) NOT NULL,
  `uaID` int(11) NOT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'Data Acquisition',
  `agRef` varchar(32) DEFAULT NULL,
  `groupName` varchar(64) DEFAULT NULL,
  `shortTitle` varchar(64) DEFAULT NULL,
  `recipient` varchar(64) DEFAULT NULL,
  `partOfValCh` varchar(64) DEFAULT NULL,
  `sector` varchar(64) DEFAULT NULL,
  `subSector` varchar(64) DEFAULT NULL,
  `topic` varchar(64) DEFAULT NULL,
  `apFunReqAGMARDT` decimal(12,2) DEFAULT NULL,
  `apFunReqGroup` decimal(12,2) DEFAULT NULL,
  `otherGroupName1` varchar(64) DEFAULT NULL,
  `apFunReqOther1` decimal(12,2) DEFAULT NULL,
  `otherGroupName2` varchar(64) DEFAULT NULL,
  `apFunReqOther2` decimal(12,2) DEFAULT NULL,
  `otherGroupName3` varchar(64) DEFAULT NULL,
  `apFunReqOther3` decimal(12,2) DEFAULT NULL,
  `totalFunding` decimal(12,2) DEFAULT NULL,
  `researchCosts` decimal(12,2) DEFAULT NULL,
  `otherCostsName1` varchar(64) DEFAULT NULL,
  `otherCosts1` decimal(12,2) DEFAULT NULL,
  `otherCostsName2` varchar(64) DEFAULT NULL,
  `otherCosts2` decimal(12,2) DEFAULT NULL,
  `otherCostsName3` varchar(64) DEFAULT NULL,
  `otherCosts3` decimal(12,2) DEFAULT NULL,
  `totalCosts` decimal(12,2) DEFAULT NULL,
  `groupAuthAgent` varchar(64) DEFAULT NULL,
  `groupAuthAgentAddress` varchar(128) DEFAULT NULL,
  `groupAuthAgentPhone` varchar(22) DEFAULT NULL,
  `groupAuthAgentEmail` varchar(64) DEFAULT NULL,
  `groupProjLeader` varchar(64) DEFAULT NULL,
  `groupProjLeaderAddress` varchar(128) DEFAULT NULL,
  `groupProjLeaderPhone` varchar(22) DEFAULT NULL,
  `groupProjLeaderEmail` varchar(64) DEFAULT NULL,
  `otherGroupMembers` varchar(1280) DEFAULT NULL,
  `structure` varchar(1280) DEFAULT NULL,
  `memGovBoard` varchar(1280) DEFAULT NULL,
  `projectGoal` varchar(2560) DEFAULT NULL,
  `ecoBenVal` varchar(2560) DEFAULT NULL,
  `ecoEval` varchar(2560) DEFAULT NULL,
  `projectPlan` varchar(5120) DEFAULT NULL,
  `milestone01` varchar(1024) DEFAULT NULL,
  `dateExp01` varchar(11) DEFAULT NULL,
  `payment01` varchar(11) DEFAULT NULL,
  `milestone02` varchar(1024) DEFAULT NULL,
  `dateExp02` varchar(11) DEFAULT NULL,
  `payment02` varchar(11) DEFAULT NULL,
  `milestone03` varchar(1024) DEFAULT NULL,
  `dateExp03` varchar(11) DEFAULT NULL,
  `payment03` varchar(11) DEFAULT NULL,
  `milestone04` varchar(1024) DEFAULT NULL,
  `dateExp04` varchar(11) DEFAULT NULL,
  `payment04` varchar(11) DEFAULT NULL,
  `milestone05` varchar(1024) DEFAULT NULL,
  `milestone05` varchar(1024) DEFAULT NULL,
  `dateExp05` varchar(11) DEFAULT NULL,
  `payment05` varchar(11) DEFAULT NULL,
  `milestone06` varchar(1024) DEFAULT NULL,
  `dateExp06` varchar(11) DEFAULT NULL,
  `payment06` varchar(11) DEFAULT NULL,
  `milestone07` varchar(1024) DEFAULT NULL,
  `dateExp07` varchar(11) DEFAULT NULL,
  `payment07` varchar(11) DEFAULT NULL,
  `milestone08` varchar(1024) DEFAULT NULL,
  `dateExp08` varchar(11) DEFAULT NULL,
  `payment08` varchar(11) DEFAULT NULL,
  `milestone09` varchar(1024) DEFAULT NULL,
  `dateExp09` varchar(11) DEFAULT NULL,
  `payment09` varchar(11) DEFAULT NULL,
  `milestone10` varchar(1024) DEFAULT NULL,
  `dateExp10` varchar(11) DEFAULT NULL,
  `payment10` varchar(11) DEFAULT NULL,
  `exResName` varchar(64) DEFAULT NULL,
  `exResPosition` varchar(64) DEFAULT NULL,
  `exResOrg` varchar(64) DEFAULT NULL,
  `exResAddress` varchar(128) DEFAULT NULL,
  `exResPhone` varchar(22) DEFAULT NULL,
  `exResEmail` varchar(64) DEFAULT NULL,
  `exResData` varchar(2560) DEFAULT NULL,
  `indRef1` varchar(64) DEFAULT NULL,
  `indRefEmail1` varchar(64) DEFAULT NULL,
  `indRefPhone1` varchar(22) DEFAULT NULL,
  `indRef2` varchar(64) DEFAULT NULL,
  `indRefEmail2` varchar(64) DEFAULT NULL,
  `indRefPhone2` varchar(22) DEFAULT NULL,
  `charRef1` varchar(64) DEFAULT NULL,
  `charRefEmail1` varchar(64) DEFAULT NULL,
  `charRefPhone1` varchar(22) DEFAULT NULL,
  `charRef2` varchar(64) DEFAULT NULL,
  `charRefEmail2` varchar(64) DEFAULT NULL,
  `charRefPhone2` varchar(22) DEFAULT NULL,
  `nonCashContribs` varchar(1280) DEFAULT NULL,
  `othGovTypFun` varchar(1280) DEFAULT NULL,
  `ethicInsBio` enum('yes','no') DEFAULT NULL,
  `nameOfCommit` varchar(64) DEFAULT NULL,
  `nameOfChair` varchar(64) DEFAULT NULL,
  `commitAddress` varchar(128) DEFAULT NULL,
  `webPubContactPerson` varchar(64) DEFAULT NULL,
  `webPubContactAddress` varchar(128) DEFAULT NULL,
  `webPubContactPhone` varchar(22) DEFAULT NULL,
  `webPubContactEmail` varchar(64) DEFAULT NULL,
  `webPubSummary` varchar(1280) DEFAULT NULL,
  `agr1` enum('yes','no') NOT NULL DEFAULT 'no',
  `dateStarted` int(11) NOT NULL,
  `lastModified` int(11) NOT NULL,
  `flulct` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`appID`,`uID`,`uaID`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

Here is what I am trying to do:

UPDATE `agmApps`.`app_AIGPrReq` SET `webPubSummary` = 'Co-innovation is a collaborative process between companies and R&D knowledge organisations that is proving to be a powerful accelerant of innovation.  FoodHQ, a partnership between New Zealand''s leading food innovators and enabling Local Government bodies, is proving to be a powerful force for providing innovative solutions to companies moving further up the value chain.  AGMARDT is supporting the creation of a national cohort of 30-50 of these innovative companies to collaborate with FoodHQ around their key export growth activities including product and process development, cost efficiency, meeting client preferences, shelf-life and food safety, and business model innovation.  Members of the cohort will also benefit from collaborating with each other, and as part of the Programme, FoodHQ will be convening events and provide an interaction platform that provide such opportunities.' WHERE `app_AIGPrReq`.`appID` = 13 AND `app_AIGPrReq`.`uID` = 54 AND `app_AIGPrReq`.`uaID` = 71

UPDATE: I'm temporarily changing this particular table to be MyISAM as opposed to InnoDB. This seems to allow it all to work, however I do want to switch it back to InnoDB like every other table in the database, and have it functioning.

Try two operations. Select your fields without the blob field then try the select with only the key field and the blob field. If this works then you have to ask yourself if this leads to a useful solution in your case. — Sql Surfer, Jul 05 '15 at 16:37
There is no relevant blob field in the picture at all. No col in this table is set to blob. The field where this problem happens in varchar(1280). — Cassandra, Jul 05 '15 at 16:40
can you please post the output of ```SHOW CREATE TABLE yourtable```, and also the ```INSERT``` statement that gives the error? — luksch, Jul 05 '15 at 16:44
My Bad. Select your big varchar field and your other smaller fields in two operations and bring them together using the key value. This may give you the breathing room you need in the row size. — Sql Surfer, Jul 05 '15 at 16:45
It may be an issue with utf-8 and the fact that it needs up to 3 bytes per character. — luksch, Jul 05 '15 at 16:51
@Martin You may want to update on this - https://dev.mysql.com/doc/refman/5.0/en/char.html I am using a newer version of MySQL so varchar does not have a limitation of 255 characters. — Cassandra, Jul 05 '15 at 16:53
@luksch Gave your theory a try, increased the field to varchar(3840) however it has made no difference — Cassandra, Jul 05 '15 at 17:08
Is a solution from http://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large relevant to your case? — zedfoxus, Jul 05 '15 at 17:14
@zedfoxus No. I don't have the ability to modify server config files. — Cassandra, Jul 05 '15 at 17:19
thanks for the update @Cassandra, I learn something new every day :) — Martin, Jul 05 '15 at 17:26
@Martin No worries, sorry if it wounded a bit sarcastic in reply, was not intended as such. Just a bit stressed out because of this really weird issue and needing a solution to it. — Cassandra, Jul 05 '15 at 17:36
that's fine, @Cassandra , it doesn't come across as sarcastic or rude. Sorry I can't provide more helpful advice. — Martin, Jul 05 '15 at 17:49
Honestly, the right solution is to reduce the number of columns. Your table is way too big and is not at all normalized. This will cause pain. If you have the option to split this into multiple tables, please please do so. — siride, Jul 05 '15 at 18:02
@siride Have already done this but it has made no difference at all. It fails in exactly the same way at the same place, even after removing 50% of the cols per row. I will reduce it into other sections later but for bow I need a solution that won't take a lot of time, and actually works. — Cassandra, Jul 05 '15 at 18:38
Are you open to changing the table structure? There are redundant columns that can go into specific table e.g. `webPub`, `charRef`, `indRef` etc. — zedfoxus, Jul 05 '15 at 19:31
@zedfoxus Yes I am open to changing the table structure to use multiple tables for one application form. I've already begun to do this by extracting out the section that holds the most fields in it and having this as a table on it's own. Most of those fields are very tiny, just decimal fields with a maximum of 12 characters. In time I will break down all of the forms into having a table per section. However it's not something I can do quickly as there are 10 forms all up, each with several sections. — Cassandra, Jul 05 '15 at 20:28
Good choice, @Cassandra. You'd be on the right track for long term success by splitting into multiple tables. You are likely to also overcome the current issue in the process. When you create `charRef`, for example, just create `id`, `email`, `phone` columns only. That way you can add as many emails and phones as you desire. — zedfoxus, Jul 05 '15 at 20:34
Thanks @zedfoxus. Seems this is the only realistic answer to the issue. I've never hit this problem before, however I've also never had tables this large either. — Cassandra, Jul 05 '15 at 20:38

score 0 · Accepted Answer · answered Jan 27 '16 at 03:21

zedfoxus suggestion (see comments) is indeed the best solution. The problem was entirely eliminated by splitting each forms data storage up into sections - conveniently done by a per-form section basis. Now each section of the form is stored inside it's own table and this has seriously reduced the DB table structures into a vastly more efficient and tidy format. The irritating overhead limitation is no longer hit, even when maximum input quota per form question is reached.

Thanks to all who answered and offered help. Many useful leads and ideas were posted here!

MySQL row size error

1 Answers1