MySQL Forums
Forum List  »  InnoDB

"Row size too large" error is triggering - when I don't think it should be
Posted by: Jack W-H
Date: August 22, 2016 07:08AM

I've got a bizarre issue with MySQL (specifically MySQL 5.5.5-10.0.26-MariaDB) where it is throwing the "Row size too large" error when (by my logic!) it shouldn't be.

I have an ExpressionEngine installation that was recently migrated to InnoDB from MyISAM for performance reasons. The main entry table is throwing errors when trying to update large entries:

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

The `UPDATE` query ExpressionEngine is trying to run is huge (updating around 50 columns, with a lot of large content). But this error is only ever thrown when updating one specific `TEXT` column (`field_id_13`), with data only 1500 bytes in length.

- If I comment out the setting of `field_id_13`, the query works every single time
- If I comment out `field_id_13` and add extra fields totalling more data being inserted, the query still works!
- It's only this one column that is causing the error - commenting or adding any other columns gives no problems at all

The column is being set with the following data:

`field_id_13` = 'YTo3OntzOjk6InJvd19vcmRlciI7YTo1OntpOjA7czoxMToicm93X2lkXzg1NzAiO2k6MTtzOjExOiJyb3dfaWRfODU2OCI7aToyO3M6MTE6InJvd19pZF84NTY5IjtpOjM7czoxMToicm93X2lkXzg1NjciO2k6NDtzOjExOiJyb3dfaWRfODU3MSI7fXM6MTE6InJvd19pZF84NTcwIjthOjI6e3M6ODoiY29sX2lkXzEiO3M6MTc6IlRoZSBHcmFuZCBDYW55b24gIjtzOjg6ImNvbF9pZF8yIjthOjM6e3M6NzoiZmlsZWRpciI7czoxOiIyIjtzOjg6ImZpbGVuYW1lIjtzOjE2OiJHcmFuZC1DYW55b24uanBnIjtzOjg6ImV4aXN0aW5nIjtzOjE4OiJHcmFuZC1DYW55b24uanBnfDIiO319czoxMToicm93X2lkXzg1NjgiO2E6Mjp7czo4OiJjb2xfaWRfMSI7czozMDoiQ2VudHJhbCBQYXJrIGluIE5ldyBZb3JrIENpdHkgIjtzOjg6ImNvbF9pZF8yIjthOjM6e3M6NzoiZmlsZWRpciI7czoxOiIyIjtzOjg6ImZpbGVuYW1lIjtzOjE2OiJjZW50cmFsLXBhcmsuanBnIjtzOjg6ImV4aXN0aW5nIjtzOjE4OiJjZW50cmFsLXBhcmsuanBnfDIiO319czoxMToicm93X2lkXzg1NjkiO2E6Mjp7czo4OiJjb2xfaWRfMSI7czoxMDoiQ2hpY2FnbydzICI7czo4OiJjb2xfaWRfMiI7YTozOntzOjc6ImZpbGVkaXIiO3M6MToiMiI7czo4OiJmaWxlbmFtZSI7czoxNDoiY2hpY2Fnb2MyYy5qcGciO3M6ODoiZXhpc3RpbmciO3M6MTY6ImNoaWNhZ29jMmMuanBnfDIiO319czoxMToicm93X2lkXzg1NjciO2E6Mjp7czo4OiJjb2xfaWRfMSI7czozNToiV2FzaGluZ3RvbiBELkMuJ3MgQ2FwaXRvbCBCdWlsZGluZyAiO3M6ODoiY29sX2lkXzIiO2E6Mzp7czo3OiJmaWxlZGlyIjtzOjE6IjIiO3M6ODoiZmlsZW5hbWUiO3M6MTg6Indhc2hpbmd0b24tZGM0LmpwZyI7czo4OiJleGlzdGluZyI7czoyMDoid2FzaGluZ3Rvbi1kYzQuanBnfDIiO319czoxMToicm93X2lkXzg1NzEiO2E6Mjp7czo4OiJjb2xfaWRfMSI7czoyMToiVGhlIExpbmNvbG4gTWVtb3JpYWwgIjtzOjg6ImNvbF9pZF8yIjthOjM6e3M6NzoiZmlsZWRpciI7czoxOiIyIjtzOjg6ImZpbGVuYW1lIjtzOjIxOiJsaW5jb2xuLW1lbW9yaWFsMi5qcGciO3M6ODoiZXhpc3RpbmciO3M6MjM6ImxpbmNvbG4tbWVtb3JpYWwyLmpwZ3wyIjt9fXM6MTc6InRyaWdnZXJfcmV2aXNpb25zIjtzOjE6IjEiO30=',

That's 1540 characters of data. The data for `field_id_13` is base64-encoded data of a serialised PHP object (not my design decision unfortunately - this is done by an EE field type). When decoded, the string length is only 1154 characters.

The column `field_id_13` is set as `TEXT`, though I have also tried setting it as `VARCHAR(10000)`, `MEDIUMTEXT`, `LONGTEXT`, and `BLOB` - neither of which make any difference as the error is always thrown.

The MySQL installation is using the Antelope file system. I am aware a move can be made to Barracuda and I can use `ROW_FORMAT=compressed` but the client is very performance-conscious and I am reluctant to bring the increase in disk usage overhead for compressing/decompressing on the fly.

Any ideas what's going on?

For reference, here is my `CREATE TABLE` result:

CREATE TABLE `exp_channel_data` (
`entry_id` int(10) unsigned NOT NULL,
`site_id` int(4) unsigned NOT NULL DEFAULT '1',
`channel_id` int(4) unsigned NOT NULL,
`field_id_2` text,
`field_ft_2` tinytext,
`field_id_3` text,
`field_ft_3` tinytext,
`field_id_4` text,
`field_ft_4` tinytext,
`field_id_5` text,
`field_ft_5` tinytext,
`field_id_12` mediumtext,
`field_ft_12` tinytext,
`field_id_13` text,
`field_ft_13` tinytext,
`field_id_18` mediumtext,
`field_ft_18` tinytext,
`field_id_19` mediumtext,
`field_ft_19` tinytext,
`field_id_20` text,
`field_ft_20` tinytext,
`field_id_21` mediumtext,
`field_ft_21` tinytext,
`field_id_22` mediumtext,
`field_ft_22` tinytext,
`field_id_23` text,
`field_ft_23` tinytext,
`field_id_24` int(11) DEFAULT '0',
`field_ft_24` tinytext,
`field_id_25` mediumtext,
`field_ft_25` tinytext,
`field_id_26` text,
`field_ft_26` tinytext,
`field_id_46` float DEFAULT '0',
`field_ft_46` tinytext,
`field_id_47` mediumtext,
`field_ft_47` tinytext,
`field_id_49` text,
`field_ft_49` tinytext,
`field_id_50` text,
`field_ft_50` tinytext,
`field_id_51` text,
`field_ft_51` tinytext,
`field_id_52` text,
`field_ft_52` tinytext,
`field_id_53` mediumtext,
`field_ft_53` tinytext,
`field_id_54` text,
`field_ft_54` tinytext,
`field_id_55` text,
`field_ft_55` tinytext,
`field_id_56` text,
`field_ft_56` tinytext,
`field_id_91` text,
`field_ft_91` tinytext,
`field_id_92` text,
`field_ft_92` tinytext,
`field_id_93` text,
`field_ft_93` tinytext,
`field_id_94` text,
`field_ft_94` tinytext,
`field_id_95` text,
`field_ft_95` tinytext,
`field_id_96` text,
`field_ft_96` tinytext,
`field_id_97` text,
`field_ft_97` tinytext,
`field_id_98` text,
`field_ft_98` tinytext,
`field_id_99` text,
`field_ft_99` tinytext,
`field_id_100` text,
`field_ft_100` tinytext,
`field_id_101` text,
`field_ft_101` tinytext,
`field_id_102` text,
`field_ft_102` tinytext,
`field_id_103` text,
`field_ft_103` tinytext,
`field_id_104` text,
`field_ft_104` tinytext,
`field_id_105` text,
`field_ft_105` tinytext,
`field_id_106` text,
`field_ft_106` tinytext,
`field_id_107` mediumtext,
`field_ft_107` tinytext,
`field_id_109` text,
`field_ft_109` tinytext,
`field_id_110` text,
`field_ft_110` tinytext,
`field_id_111` text,
`field_ft_111` tinytext,
`field_id_112` text,
`field_ft_112` tinytext,
`field_id_115` text,
`field_ft_115` tinytext,
`field_id_116` text,
`field_ft_116` tinytext,
`field_id_119` text,
`field_ft_119` tinytext,
`field_id_120` text,
`field_ft_120` tinytext,
`field_id_121` text,
`field_ft_121` tinytext,
`field_id_122` text,
`field_ft_122` tinytext,
`field_id_123` text,
`field_ft_123` tinytext,
`field_id_124` text,
`field_ft_124` tinytext,
`field_id_125` text,
`field_ft_125` tinytext,
`field_id_126` text,
`field_ft_126` tinytext,
`field_id_146` text,
`field_ft_146` tinytext,
`field_id_147` decimal(10,4) DEFAULT '0.0000',
`field_ft_147` tinytext,
`field_id_148` text,
`field_ft_148` tinytext,
`field_id_149` text,
`field_ft_149` tinytext,
`field_id_150` text,
`field_ft_150` tinytext,
`field_id_151` text,
`field_ft_151` tinytext,
`field_id_152` text,
`field_ft_152` tinytext,
`field_id_153` text,
`field_ft_153` tinytext,
`field_id_154` text,
`field_ft_154` tinytext,
`field_id_155` text,
`field_ft_155` tinytext,
`field_id_158` text,
`field_ft_158` tinytext,
`field_id_159` text,
`field_ft_159` tinytext,
`field_id_160` text,
`field_ft_160` tinytext,
`field_id_161` text,
`field_ft_161` tinytext,
PRIMARY KEY (`entry_id`),
KEY `channel_id` (`channel_id`),
KEY `site_id` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Options: ReplyQuote


Subject
Views
Written By
Posted
"Row size too large" error is triggering - when I don't think it should be
1103
August 22, 2016 07:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.