如何为大型二进制数据配置 MySQL 5.6 LONGBLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25277452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to configure MySQL 5.6 LONGBLOB for large binary data
提问by nrapopor
Before I ask my question a little background: I'm doing the Data Export/Import using the MySQL Workbench 6.1 of a MySQL 5.5 database from one machine to a 5.6 on another. both machines are ubuntu one 32-bit the other 64-bit.
在我问我的问题之前，先了解一下背景：我正在使用 MySQL 5.5 数据库的 MySQL Workbench 6.1 从一台机器到另一台机器上的 5.6 进行数据导出/导入。两台机器都是ubuntu，一个是32位，另一个是64位。
I dump the data no problem, but when I try to load it I get the:
我转储数据没问题，但是当我尝试加载它时，我得到：
ERROR 1118 (42000) at line 1807: 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.
第 1807 行的错误 1118 (42000)：行大小太大 (> 8126)。将某些列更改为 TEXT 或 BLOB 或使用 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED 可能会有所帮助。在当前行格式中，内联存储 768 字节的 BLOB 前缀。
Here is the table create:
这是表创建：
CREATE TABLE `file_content` (
`fileid` bigint(20) NOT NULL,
`content` LONGBLOB NOT NULL,
PRIMARY KEY (`fileid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I have the following relevant my.cnf settings ...
我有以下相关的 my.cnf 设置...
max_allowed_packet=1G
innodb_file_per_table=1
innodb_file_format=Barracuda
max_allowed_packet=1G
innodb_file_per_table=1
innodb_file_format=梭子鱼
I spend a lot of googling time trying to figure out what the problem is. BTW if I remove the primary key (no problem here, but another table that has a foreign key into this table complains.
我花了很多谷歌搜索时间试图找出问题所在。顺便说一句，如果我删除主键（这里没问题，但是另一个表中有一个外键到这个表抱怨。
As luck would have it, I have the ssh access to the box so I can see actual mysqldb.log. why I find there is really interesting ...
幸运的是，我可以通过 ssh 访问该框，因此我可以看到实际的 mysqldb.log。为什么我觉得那里真的很有趣......
2014-08-12 20:42:12 25246 [ERROR] InnoDB: The total blob data length (14179167) is greater than 10% of the redo log file size (3072). Please increase innodb_log_file_size.
2014-08-12 20:42:12 25246 [ERROR] InnoDB：总 blob 数据长度 (14179167) 大于重做日志文件大小 (3072) 的 10%。请增加 innodb_log_file_size。
So increasing the redo log file size to 10x LONGBLOB size fixed my issue. However does that mean that to insert a 1G LONGBLOB (thats the actual maximum because of the packet size) I will need a 10G innodb_log_file_size.
因此，将重做日志文件大小增加到 10 倍 LONGBLOB 大小解决了我的问题。但是，这是否意味着要插入 1G LONGBLOB（由于数据包大小，这是实际最大值）我需要 10G innodb_log_file_size。
Can anyone explain how "redo log size error" turns into "row size too large (>8126)" error.
谁能解释“重做日志大小错误”如何变成“行大小太大（> 8126）”错误。
BTW I have no control over the structure of this db so no "why are you storing large blobs in the database".
顺便说一句，我无法控制这个数据库的结构，所以没有“你为什么要在数据库中存储大型 blob”。
TIA
TIA
回答by naitsirch
The reason for this issue is a change in MySQL 5.6.20as one could read in the change log:
出现此问题的原因是MySQL 5.6.20中的一项更改，您可以在更改日志中看到：
As a result of the redo log BLOB write limit introduced for MySQL 5.6, the innodb_log_file_sizesetting 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_sizesetting is already sufficiently large or your tables contain no BLOB data.
由于为 MySQL 5.6 引入了重做日志 BLOB 写入限制，innodb_log_file_size设置应该比在表的行中找到的最大 BLOB 数据大小加上其他可变长度字段（VARCHAR、VARBINARY 和文本类型字段）。如果您的innodb_log_file_size设置已经足够大或者您的表不包含 BLOB 数据，则不需要任何操作。
To resolve your issue you have to increase the value of the innodb_log_file_sizeoption in your my.inibelow the [mysqld]section. Its default value is 48M. Setting it to
要解决您的问题，您必须在下面的部分中增加innodb_log_file_size选项的值。其默认值为。将其设置为my.ini[mysqld]48M
[mysqld]
innodb_log_file_size=256M
helped in my case.
在我的情况下有帮助。
Be careful when changing the value of innodb_log_file_sizethat you do this safely:
更改innodb_log_file_size您安全执行此操作的值时要小心：
- You need to shut the server down cleanly and normally.
- Move away (don't delete) the log files, which are named ib_logfile0, ib_logfile1, and so on.
- Check the error log to ensure there was no problem shutting down.
- Then restart the server and watch the error log output carefully. You should see InnoDB print messages saying that the log files don't exist. It will create new ones and then start.
- At this point you can verify that InnoDB is working, and then you can delete the old log files.
- 您需要干净且正常地关闭服务器。
- 移开（不要删除）名为 ib_logfile0、ib_logfile1 等的日志文件。
- 检查错误日志以确保关闭没有问题。
- 然后重启服务器，仔细观察错误日志输出。您应该会看到 InnoDB 打印消息，指出日志文件不存在。它将创建新的，然后开始。
- 此时您可以验证 InnoDB 是否正常工作，然后您可以删除旧的日志文件。
回答by Ingus
For these who cant find this for XAMPP:
At first i could not find correct file to edit innodb_log_file_size
对于那些无法为 XAMPP 找到此
文件的人：起初我找不到要编辑的正确文件innodb_log_file_size
Actual file: xampp/mysql/bin/my.ini
实际文件： xampp/mysql/bin/my.ini
回答by Natnael Abate
Just navigate in this locations: xampp\mysql\bin\my.ini and try to change the size of innodb_log_file_size to 256 MB
只需在以下位置导航：xampp\mysql\bin\my.ini 并尝试将 innodb_log_file_size 的大小更改为 256 MB
