恢复Django-mailer数据库时，MySQL错误1118（行大小太大） [英] MySQL Error 1118 (Row size too large) when restoring Django-mailer database
问题描述
当迁移django-mailer的messagelog表时，我遇到了可怕的错误1118： / p>
行2226处的错误1118（42000）：行大小太大（> 8126）。将某些列更改为TEXT或BLOB可能有所帮助。在当前行格式中，0字节的BLOB前缀被内联存储。
我已经在线阅读了很多关于这个错误的东西，但是没有它解决了我的问题。
NB这个错误是不是来自创建表，而是插入一个数据量很大的行。
注意： p>
- innodb_file_format和innodb_file_format_max变量设置为Barracuda。
- 在创建表时将ROW_FORMAT设置为DYNAMIC 。
-
该表没有很多列。模式如下：
+ ---------------- + -------- ---- + ------ + ----- + --------- + ---------------- +
| Field |类型|空|关键|默认|额外|
+ ---------------- + ------------ + ------ + ----- + - ------- + ---------------- +
| id | int（11）| NO | PRI | NULL | auto_increment |
| message_data | longtext | NO | | NULL | |
| when_added | datetime | NO | | NULL | |
|优先| varchar（1）| NO | | NULL | |
| when_attempt | datetime | NO | | NULL | |
|结果| varchar（1）| NO | | NULL | |
| log_message | longtext | NO | | NULL | |
+ ---------------- + ------------ + ------ + ----- + - ------- + ---------------- +
再次，只有当我尝试插入一个相当大的（message_data约5兆字节）行时，才会发生错误;创建表格工作正常，在失败之前添加的行数约为50万行。
我没有想法;我已经尝试过DYANMIC和COMPRESSED行格式，我已经检查了相关innodb变量的值：
mysql>显示变量，如％innodb_file％;
+ -------------------------- + ----------- +
| Variable_name |价值|
+ -------------------------- + ----------- +
| innodb_file_format |梭子鱼|
| innodb_file_format_check | ON |
| innodb_file_format_max |梭子鱼|
| innodb_file_per_table | ON |
+ -------------------------- + ----------- +
创建代码（来自SHOW CREATE TABLE）如下所示：
CREATE TABLE`mailer_messagelog`（
`id` int（11）NOT NULL AUTO_INCREMENT，
`message_data` longtext NOT NULL，
`when_added` datetime NOT NULL，
`priority` varchar （1）NOT NULL，
`when_attempted` datetime NOT NULL，
`result` varchar（1）NOT NULL，
`log_message` longtext NOT NULL，
PRIMARY KEY（` id`）
）ENGINE = InnoDB AUTO_INCREMENT = 869906 DEFAULT CHARSET = latin1 ROW_FORMAT = DYNAMIC
根据此问题的其中一个答案，您的问题可能是由于MySQL 5.6中的更改引起的（请参阅InnoDB关于 http：// dev的注释.mysql.com / DOC / relnotes的/ MySQL的/ 5.6 / EN /新闻5-6-20.html ）：
InnoDB笔记
重要更改：重做日志写入大型外部存储的BLOB
字段可以覆盖最近的检查点。 5.6.20补丁
将重做日志BLOB写入的大小限制为重做日志文件
大小的10％。 5.7.5补丁在不强加限制的情况下解决错误。
对于MySQL 5.5，错误仍然是一个已知的限制。
由于重做日志BLOB写入限制为MySQL 5.6引入，
innodb_log_file_size设置应该是表的行中找到的
最大的BLOB数据大小的10倍，加上其他变量长度字段（VARCHAR，VARBINARY和TEXT
类型字段）的
长度， 。如果您的innodb_log_file_size
设置已经足够大，或者您的表不包含BLOB
数据，则不需要执行任何操作。
注意在MySQL 5.6.22中，重做日志BLOB写入限制被放宽到总重做日志大小的10％
（innodb_log_file_size *
innodb_log_files_in_group）。
（错误＃16963396 ，Bug＃19030353，Bug＃69477）
如果您更改 innodb_log_file_size 大于50M的东西？ （更改该变量需要一些步骤才能正常工作：
I dumped a working production database from a django app and am trying to migrate it to my local development environment. The production server runs MySQL 5.1, and locally I have 5.6.
When migrating the django-mailer's "messagelog" table, I'm running into the dreaded Error 1118:
ERROR 1118 (42000) at line 2226: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
I've read lots of stuff online about this error, but none of it has solved my problem.
N.B. This error is not coming from the creation of the table, but rather the insertion of a row with pretty large data.
Notes:
- The innodb_file_format and innodb_file_format_max variables are set to Barracuda.
- The ROW_FORMAT is set to DYNAMIC on table creation.
The table does not have very many columns. Schema below:
+----------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | message_data | longtext | NO | | NULL | | | when_added | datetime | NO | | NULL | | | priority | varchar(1) | NO | | NULL | | | when_attempted | datetime | NO | | NULL | | | result | varchar(1) | NO | | NULL | | | log_message | longtext | NO | | NULL | | +----------------+------------+------+-----+---------+----------------+
Again, the error happens ONLY when I try to insert a quite large (message_data is about 5 megabytes) row; creating the table works fine, and about 500,000 rows are added just fine before the failure.
I'm out of ideas; I've tried DYANMIC and COMPRESSED row formats, and I've triple checked the values of the relevant innodb variables:
mysql> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
The creation code (from SHOW CREATE TABLE) looks like:
CREATE TABLE `mailer_messagelog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_data` longtext NOT NULL,
`when_added` datetime NOT NULL,
`priority` varchar(1) NOT NULL,
`when_attempted` datetime NOT NULL,
`result` varchar(1) NOT NULL,
`log_message` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=869906 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
According to one of the answers to this question, your problem might be caused by changes in MySQL 5.6 (see the InnoDB Notes on http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html):
InnoDB Notes
Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.
As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data.
Note In MySQL 5.6.22, the redo log BLOB write limit is relaxed to 10% of the total redo log size (innodb_log_file_size * innodb_log_files_in_group).
(Bug #16963396, Bug #19030353, Bug #69477)
Does it help if you change innodb_log_file_size to something bigger than 50M? (Changing that variable needs some steps to work correctly:
这篇关于恢复Django-mailer数据库时，MySQL错误1118（行大小太大）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
