MySQL：错误代码：1118 行大小太大 (> 8126)。将某些列更改为 TEXT 或 BLOB

MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB

提问by Diego87

I want to create a table of 325column:

我想创建一个325列的表：

CREATE TABLE NAMESCHEMA.NAMETABLE 
(   
      ROW_ID TEXT NOT NULL ,        //this is the primary key

324 column of these types:
      CHAR(1), 
      DATE, 
      DECIMAL(10,0), 
      DECIMAL(10,7), 
      TEXT, 
      LONG,

) ROW_FORMAT=COMPRESSED;

I replaced all the VARCHAR with the TEXT and i have added Barracudain the my.ini file of MySQL, this is the attributes added:

我用 TEXT 替换了所有的 VARCHAR，并在 MySQL 的 my.ini 文件中添加了Barracuda，这是添加的属性：

innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_check = ON

but i still have this error:

但我仍然有这个错误：

Error Code: 1118
 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.

EDIT: I can't change the structure of the database because it's legacy application/system/database. The create of a new table, it's an export of the legacy database.

编辑：我无法更改数据库的结构，因为它是遗留应用程序/系统/数据库。创建一个新表，它是旧数据库的导出。

EDIT2: i wrote this question that is similar to others but inside there are some solution that i found on internet like VARCHAR and Barracuda, but i still have that problem so i decided to open a new question with already the classic answer inside for seeing if someone have other answers

EDIT2：我写的这个问题与其他问题类似，但里面有一些我在互联网上找到的解决方案，如 VARCHAR 和 Barracuda，但我仍然有这个问题，所以我决定打开一个新问题，里面已经有经典答案，看看是否有人有其他答案

采纳答案by user2635717

I struggled with the same error code recently, due to a change in MySQL Server 5.6.20. I was able to solve the problem by changing the innodb_log_file_size in the my.ini text file.

由于 MySQL Server 5.6.20 中的更改，我最近遇到了相同的错误代码。我能够通过更改 my.ini 文本文件中的 innodb_log_file_size 来解决问题。

In the release notes, it is explained that a innodb_log_file_size which is too small will trigger a "Row size too large error."

在发行说明中，解释了太小的 innodb_log_file_size 会触发“Row size too large 错误”。

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

回答by Stefano Brozzi

I tried all the solutions here, but only this parameter

我在这里尝试了所有解决方案，但只有这个参数

innodb_strict_mode             = 0

solved my day...

解决了我的一天...

From the manual:

从手册：

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements. innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

innodb_strict_mode 设置会影响对 CREATE TABLE、ALTER TABLE 和 CREATE INDEX 语句的语法错误的处理。innodb_strict_mode 还启用记录大小检查，以便 INSERT 或 UPDATE 永远不会由于记录对于所选页面大小而言太大而失败。

回答by ?? ???

ERROR 1118 (42000) at line 1852:    
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.

---

---

[mysqld]

innodb_log_file_size = 512M

innodb_strict_mode = 0

ubuntu 16.04 edit path:

ubuntu 16.04 编辑路径：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

on MS Windows the path will be something like:

在 MS Windows 上，路径类似于：

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Don't forget to retart the service (or restart your machine)

不要忘记重新启动服务（或重新启动机器）

回答by fefe

The key parameter is: innodb_page_size

关键参数是：innodb_page_size

Support for 32k and 64k page sizes was added in MySQL 5.7. For both 32k and 64k page sizes, the maximum row length is approximately 16000 bytes.

MySQL 5.7 中添加了对 32k 和 64k 页面大小的支持。对于 32k 和 64k 页面大小，最大行长度约为 16000 字节。

The trick is that this parameter can be only changed during the INITIALIZATIONof the mysql service instance, so it does not have any affect if you change this parameter after the instance is already initialized(the very first run of the instance).

诀窍是这个参数只能在mysql 服务实例的 INITIALIZATION 期间更改，因此如果在实例已经初始化（实例的第一次运行）之后更改此参数，则不会有任何影响。

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

innodb_page_size 只能在初始化 MySQL 实例之前配置，之后不能更改。如果未指定任何值，则使用默认页面大小初始化实例。请参阅第 14.6.1 节，“InnoDB 启动配置”。

So if you do not change this value in my.ini before initialization, the default value will be 16K, which will have row size limit of ~8K. Thats why the error comes up.

因此，如果您在初始化之前不更改 my.ini 中的此值，则默认值为 16K，这将限制 ~8K 的行大小。这就是错误出现的原因。

If you increase the innodb_page_size, the innodb_log_buffer_sizemust be also increased. Set it at least to 16M.Also if the ROW_FORMAT is set to COMPRESSEDyou cannot increase innodb_page_size to 32k, or 64K. It should be DYNAMIC (default in 5.7).

如果增加 innodb_page_size，则innodb_log_buffer_size也必须增加。至少设置为16M。此外，如果 ROW_FORMAT 设置为COMPRESSED，则不能将 innodb_page_size 增加到 32k 或 64K。它应该是动态的（5.7 中的默认值）。

ROW_FORMAT=COMPRESSED is not supported when innodb_page_size is set to 32KB or 64KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB. innodb_log_buffer_size should be set to at least 16M (the default) when using 32k or 64k page sizes.

当 innodb_page_size 设置为 32KB 或 64KB 时，不支持 ROW_FORMAT=COMPRESSED。对于 innodb_page_size=32k，extent 大小为 2MB。对于 innodb_page_size=64k，extent 大小为 4MB。使用 32k 或 64k 页面大小时，innodb_log_buffer_size 应至少设置为 16M（默认值）。

Furthermore the innodb_buffer_pool_sizeshould be increased from 128M to 512Mat least, otherwise you will get an error on initializationof the instance (I do not have the exact error).

此外，innodb_buffer_pool_size 至少应该从128M 增加到 512M，否则你会在实例初始化时出错（我没有确切的错误）。

After this, the row size error gone.

在此之后，行大小错误消失了。

The problem with this is that you have to create a new MySql instance, and migrate data to your new DataBase instance, from old one.

这样做的问题是您必须创建一个新的 MySql 实例，并将数据从旧实例迁移到新的 DataBase 实例。

Parameters that I changed and works (after creating a new instance and initialized with the my.ini that is first modified with these settings):

我更改和工作的参数（在创建新实例并使用首先使用这些设置修改的 my.ini 进行初始化之后）：

innodb_page_size=64k
innodb_log_buffer_size=32M
innodb_buffer_pool_size=512M

All the settings and descriptions in which I found the solution can be found here:

我找到解决方案的所有设置和描述都可以在这里找到：

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Hope this helps!

希望这可以帮助！

Regards!

问候！

回答by Star

Have similar issue this morning and following way saved my life:

今天早上有类似的问题，以下方法救了我的命：

Did you try to turn off the innodb_strict_mode?

你试过关掉innodb_strict_mode吗？

SET GLOBAL innodb_strict_mode = 0;

and then try to import it again.

然后再次尝试导入。

innodb_strict_modeis ON using MySQL >= 5.7.7, before it was OFF.

innodb_strict_mode在关闭之前，使用 MySQL >= 5.7.7 打开。

回答by Guillaume Pommier

I recently created a table with 82 columns and had the same error with InnoDB. To bypass the problem we switched the table format to MyISAMas it was just used for a basic form.

我最近创建了一个包含 82 列的表，并且在使用 InnoDB 时出现了相同的错误。为了绕过这个问题，我们将表格格式切换MyISAM为仅用于基本表格。

回答by Gordon Linoff

MySQL is pretty clear about its maximum row size:

MySQL 非常清楚它的最大行大小：

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

每个表（无论存储引擎如何）的最大行大小为 65,535 字节。存储引擎可能会对此限制设置额外的约束，从而减少有效的最大行大小。

. . .

. . .

Individual storage engines might impose additional restrictions that limit table column count. Examples:

InnoDB permits up to 1000 columns.

InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.

Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page header and trailer data, which affects the amount of storage available for rows.

单个存储引擎可能会施加额外的限制来限制表列数。例子：

InnoDB 最多允许 1000 列。

InnoDB 将行大小限制为小于数据库页面的一半（大约 8000 字节），不包括 VARBINARY、VARCHAR、BLOB 或 TEXT 列。

不同的 InnoDB 存储格式（COMPRESSED、REDUNDANT）使用不同数量的页头和尾部数据，这会影响行可用的存储量。

If you have 325 repeating sets of columns, you are exceeding several of the restrictions. This is also a suspicious data format. You should have 325 rows for each row in the table you want, one for each group of columns.

如果您有 325 组重复的列，则超出了几个限制。这也是一种可疑的数据格式。表格中的每一行应该有 325 行，每组列一个。

回答by imbond

For MySQL 5.7 on Mac OS X El Capitan:

对于 Mac OS X El Capitan 上的 MySQL 5.7：

OS X provides example configuration files at /usr/local/mysql/support-files/my-default.cnf

OS X 在 /usr/local/mysql/support-files/my-default.cnf 提供示例配置文件

To add variables, first stop the server and just copy above file to, /usr/local/mysql/etc/my.cnf

要添加变量，首先停止服务器并将上面的文件复制到，/usr/local/mysql/etc/my.cnf

cmd : sudo cp /usr/local/mysql/support-files/my-default.cnf /usr/local/mysql/etc/my.cnf

NOTE: create 'etc' folder under 'mysql' in case it doesn't exists.

注意：如果它不存在，请在“mysql”下创建“etc”文件夹。

cmd : sudo mkdir /usr/local/mysql/etc

Once the my.cnf is created under etc. it's time to set variable inside that.

一旦 my.cnf 在 etc. 下创建，就该在其中设置变量了。

cmd: sudo nano my.cnf

set variables below [mysqld]

在 [mysqld] 下面设置变量

[mysqld]
innodb_log_file_size = 512M
innodb_strict_mode = 0

now start a server!

现在启动一个服务器！

回答by Julian

I just want to provide some other people with help with a more serious variant of this problem. In some situations, the error ("Row size too large .. Changing some columns to TEXT or BLOB") will occur even with "alter table drop column" and "alter table modify column" statements!

我只是想为其他一些人提供有关此问题的更严重变体的帮助。在某些情况下，即使使用“alter table drop column”和“alter table modify column”语句，也会出现错误（“行大小太大..将某些列更改为 TEXT 或 BLOB”）！

Consequently you can become completely stuck, not able to change a varchar to a text, or drop columns (trying to solve the problem ironically results in the same message).

因此，您可能会完全陷入困境，无法将 varchar 更改为文本或删除列（讽刺的是，尝试解决问题会导致相同的消息）。

If you have this problem, the solution is to alter or drop multiple columns at once. You can do this in MySQL with the syntax "alter table example drop column a, drop column b, drop column c" and if you drop enough columns at once, it will actually execute rather than raising the error.

如果您遇到此问题，解决方案是一次更改或删除多个列。您可以在 MySQL 中使用语法“alter table example drop column a, drop column b, drop column c”来执行此操作，如果您一次删除足够多的列，它实际上会执行而不是引发错误。

回答by Aritz Barrondo

What fixed mine was to add

固定我的是添加什么

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;

At the beginning of my ".sql" file, as it is said in: https://gist.github.com/tonykwon/8910261

在我的“.sql”文件的开头，正如它所说的：https: //gist.github.com/tonykwon/8910261