silverstripe数据对象太大,无法使用mysql/innodb保存

c8ib6hqw  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(344)

关于使用php7.1、silverstripe3.6.0和mysql的web应用程序的问题。该应用程序由paas提供商托管。因此,我不能直接访问数据库,也不能像错误消息所建议的那样调整innodb。
我有一个自定义数据对象,有很多字段。它在试图保存时返回一个奇怪的错误:
无法运行查询:[…此处的大更新查询…]行大小太大(>8126)。将某些列更改为文本或blob或使用row\u format=dynamic或row\u format=compressed可能会有所帮助。在当前行格式中,768字节的blob前缀是内联存储的。
在这里您可以找到我的课程的简短版本:

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` 以及 `Info` 所有的用户输入字段都可能变得很长。奇怪的是那些字段是 `HTMLText` 在数据库表中,列的类型为 `mediumtext` :

MariaDB [leonixyz]> 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 | |
+------------------------------+---------------------------------------------------+------+-----+-------------------+----------------+

有没有办法解决这个问题而不把我的对象分成几部分?
提前谢谢
fwzugrvs

fwzugrvs1#

您应该重构您的模型(例如,使用与 has_one 以及 belongs_to ),或将rowformat设置为compressed。可以使用以下代码对每个数据对象执行此操作(来自ss3,但也应适用于ss4):

private static $create_table_options = array(
    'MySQLDatabase' => 'ENGINE=InnoDB ROW_FORMAT=COMPRESSED'
);

相关问题