DataObject - MySQL > Row size too large


#1

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


#2

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


#4

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


#3

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:


#6

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');


#5

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: