DataObject - MySQL > Row size too large

Silverstripe Version: 3.6.0

Question:

Hi,

I have a custom DataObject with lots of fields. It returns a weird error while trying to save it:

Couldn’t run query: […big query here…] 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 you find a shorted version of my class:

class APIPersonResource extends DataObject {
  private static $db = array(
    'ResourceID' => 'Int',
    'Firstname' => 'Varchar(255)',
    'Lastname' => 'Varchar(255)',
    'Salutation' => 'Varchar(255)',
    'Email' => 'Varchar(255)',
    'HomepageLink' => 'Text',
    'PhoneNo' => 'Varchar(255)',
    'Info' => 'HTMLText',
    'IsResponsible' => 'Boolean',
    'ResponsibleDescription' => 'Varchar(255)',
    'ShortBio' => 'HTMLText',
    'RoomNo' => 'Varchar',
    'IsActive' => 'Boolean',
    'IsAcademic' => 'Boolean',
    'HasImage' => 'Boolean',
    'HasLinksSection' => 'Boolean',
    'HasCv' => 'Boolean',
    'HasPublications' => 'Boolean',
    'HasOfficeHours' => 'Boolean',
    'AreaOfFocus' => 'Varchar(64)',
    'OleLink' => 'Text',
    'ReserveCollectionLink' => 'Text',
    'ResearchAreas' => 'HTMLText',
    'APIDepartmentResourceID' => 'Int',
    'APIAddressResourceID' => 'Int',
    'APIDegreeResourceID' => 'Int'
  );
}

ShortBio, AreaOfFocus, ResearchAreas and Info are user-input fields that can become very long. The weird thing is that those fields are of type HTMLText and in the database table the columns are of type mediumtext:

MariaDB [unibz]> describe APIPersonResource;
+------------------------------+---------------------------------------------------+------+-----+-------------------+----------------+
| Field                        | Type                                              | Null | Key | Default           | Extra          |
+------------------------------+---------------------------------------------------+------+-----+-------------------+----------------+
| ID                           | int(11)                                           | NO   | PRI | NULL              | auto_increment |
| ClassName                    | enum('APIPersonResource','APIPhdStudentResource') | YES  | MUL | APIPersonResource |                |
| LastEdited                   | datetime                                          | YES  |     | NULL              |                |
| Created                      | datetime                                          | YES  |     | NULL              |                |
| Salutation_en_US             | varchar(255)                                      | YES  |     | NULL              |                |
| Salutation_de_DE             | varchar(255)                                      | YES  |     | NULL              |                |
| Salutation_it_IT             | varchar(255)                                      | YES  |     | NULL              |                |
| ResponsibleDescription_en_US | varchar(255)                                      | YES  |     | NULL              |                |
| ResponsibleDescription_de_DE | varchar(255)                                      | YES  |     | NULL              |                |
| ResponsibleDescription_it_IT | varchar(255)                                      | YES  |     | NULL              |                |
| ShortBio_en_US               | mediumtext                                        | YES  |     | NULL              |                |
| ShortBio_de_DE               | mediumtext                                        | YES  |     | NULL              |                |
| ShortBio_it_IT               | mediumtext                                        | YES  |     | NULL              |                |
| ResourceID                   | int(11)                                           | NO   |     | 0                 |                |
| Firstname                    | varchar(255)                                      | YES  |     | NULL              |                |
| Lastname                     | varchar(255)                                      | YES  |     | NULL              |                |
| Salutation                   | varchar(255)                                      | YES  |     | NULL              |                |
| Email                        | varchar(255)                                      | YES  |     | NULL              |                |
| PhoneNo                      | varchar(255)                                      | YES  |     | NULL              |                |
| IsResponsible                | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| ResponsibleDescription       | varchar(255)                                      | YES  |     | NULL              |                |
| ShortBio                     | mediumtext                                        | YES  |     | NULL              |                |
| IsActive                     | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| HasActiveContract            | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| APIDepartmentResourceID      | int(11)                                           | NO   |     | 0                 |                |
| RoomNo                       | varchar(50)                                       | YES  |     | NULL              |                |
| HasImage                     | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| HasCv                        | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| HasPublications              | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| APIAddressResourceID         | int(11)                                           | NO   |     | 0                 |                |
| AreaOfFocus_en_US            | varchar(64)                                       | YES  |     | NULL              |                |
| AreaOfFocus_de_DE            | varchar(64)                                       | YES  |     | NULL              |                |
| AreaOfFocus_it_IT            | varchar(64)                                       | YES  |     | NULL              |                |
| AreaOfFocus                  | varchar(64)                                       | YES  |     | NULL              |                |
| HomepageLink                 | mediumtext                                        | YES  |     | NULL              |                |
| Info_en_US                   | mediumtext                                        | YES  |     | NULL              |                |
| Info_de_DE                   | mediumtext                                        | YES  |     | NULL              |                |
| Info_it_IT                   | mediumtext                                        | YES  |     | NULL              |                |
| ResearchAreas_en_US          | mediumtext                                        | YES  |     | NULL              |                |
| ResearchAreas_de_DE          | mediumtext                                        | YES  |     | NULL              |                |
| ResearchAreas_it_IT          | mediumtext                                        | YES  |     | NULL              |                |
| Info                         | mediumtext                                        | YES  |     | NULL              |                |
| HasLinksSection              | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| HasOfficeHours               | tinyint(1) unsigned                               | NO   |     | 0                 |                |
| OleLink                      | mediumtext                                        | YES  |     | NULL              |                |
| ReserveCollectionLink        | mediumtext                                        | YES  |     | NULL              |                |
| ResearchAreas                | mediumtext                                        | YES  |     | NULL              |                |
| APIDegreeResourceID          | int(11)                                           | NO   |     | 0                 |                |
| IsAcademic                   | tinyint(1) unsigned                               | NO   |     | 0                 |                |
+------------------------------+---------------------------------------------------+------+-----+-------------------+----------------+

How do I solve this problem?

Thanks in advance

Hi,

This is a MySQL issue relating to the maximum size of the table rows. The solution lies either in updating your MySQL configuration (see here for a discussion: Change limit for "Mysql Row size too large" - Stack Overflow)

Alternatively you can break down your object a bit. Maybe move the Salutations and ResponsibleDescriptions (for example) into different dataobjects, and use a relation to join them, possibly split out the language portion of that too.

Hope that helps

1 Like

Hi, thanks for the reply.

Apparently, my table is using InnoDB as engine, and changing it to MyIsam solved the issue (at least on my local dev machine).

Is there a way to set this in the Silverstripe config, or in the DataObject class?

Thanks

On this note, that’s usual. A MediumText column in MySQL can store something like 16MB of data, so it should be plenty :slight_smile:

Yes, it’s possible to do with the following in your dataobject:

use SilverStripe\ORM\DataObject;
use SilverStripe\ORM\Connect\MySQLSchemaManager;

class MyDataObject extends DataObject 
{
    private static $create_table_options = [
        MySQLSchemaManager::ID => 'ENGINE=MyISAM'
    ];
}

The thing to think about though is whether this is actually solving your issue, or just avoiding the symptoms. InnoDB has a number of advantages over MyISAM - this is why it’s the default configuration. Just be sure you’re not creating potential issues and performance problems for the sake of not changing your code a little :wink:

1 Like

It turned out that simply switching the engine to MyISAM solves this issue. I did so by simply adding this line of code to my DataObject class and rebuilding the database: private static $create_table_options = array('MySQLDatabase' => 'ENGINE=MyISAM');