mysql ERROR 1118 (42000) - Row size too large (> 8126).

mysql ERROR 1118 (42000) - Row size too large (> 8126).

在执行创建表、增加字段或者还原备份时，表的row size长度过长，导致出现以下错误。

 

D:\\mysql\mysql-8.0.17-winx64\bin>mysql -uroot -p --default-character-set=utf8 <C:\Users\Administrator\Desktop\backup.sql
Enter password: ********
ERROR 1118 (42000) at line 3760: 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.

官方文档对此有详细的说明，8.4.7 Limits on Table Column Count and Row Size。

Row Size Limits

The maximum row size for a given table is determined by several factors:

• The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.

• The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB. See Section 15.22, “InnoDB Limits”.

  If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see Section 15.10, “InnoDB Row Formats”.

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

• For information about InnoDB row formats, see Section 15.10, “InnoDB Row Formats”.

• For information about MyISAM storage formats, see Section 16.2.3, “MyISAM Table Storage Formats”.

row size 其实就是所有字段的长度的总和，也就是表太宽了，在不分拆表的情况如何解决。

1. 按提示将某些列更改为TEXT或BLOB，无效，不能解决问题。
2. 更改系统参数 set global innodb_strict_mode=0，此参数默认是开启的。重新还原时正常完成。

参数说明

innodb_strict_mode

Command-Line Format	--innodb-strict-mode[={OFF|ON}]
System Variable	innodb_strict_mode
Scope	Global, Session
Dynamic	Yes
SET_VAR Hint Applies	No
Type	Boolean
Default Value	ON

When innodb_strict_mode is enabled, InnoDB returns errors rather than warnings for certain
conditions.
Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended
consequences of various combinations of operational modes and SQL statements. When
innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than
issuing a warning and processing the specified statement (perhaps with unintended behavior).
This is analogous to sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and
determines whether it silently ignores errors, or validates input syntax and data values.
The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER
TABLE, CREATE INDEX, and OPTIMIZE TABLE 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.
Oracle recommends enabling innodb_strict_mode when using ROW_FORMAT and
KEY_BLOCK_SIZE clauses in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements.
When innodb_strict_mode is disabled, InnoDB ignores conflicting clauses and creates the
table or index with only a warning in the message log. The resulting table might have different
characteristics than intended, such as lack of compression support when attempting to create a
compressed table. When innodb_strict_mode is enabled, such problems generate an immediate
error and the table or index is not created.
You can enable or disable innodb_strict_mode on the command line when starting mysqld, or
in a MySQL configuration file. You can also enable or disable innodb_strict_mode at runtime
with the statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is
either ON or OFF. Changing the GLOBAL setting requires privileges sufficient to set global system
variables (see Section 5.1.9.1, “System Variable Privileges”) and affects the operation of all clients
that subsequently connect. Any client can change the SESSION setting for innodb_strict_mode,
and the setting affects only that client.
innodb_strict_mode is not applicable to general tablespaces. Tablespace management rules
for general tablespaces are strictly enforced independently of innodb_strict_mode. For more
information, see Section 13.1.21, “CREATE TABLESPACE Statement”.


参考：

https://stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te