MYSQL ERROR 1118

创建新表时报错：

ERROR 1118 (42000): Row size too large (> 4030). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

该表在另一个数据库已经存在，但当前库创建报错，所以有点疑问.

 

查阅官方文档：发现InnoDB表row size和innodb page size有关；

• 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 14.23, “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 14.11, “InnoDB Row Formats”.

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

第二段大致意思：对于4KB、8KB、16KB和32KB InnoDB_页面大小设置来说略小于半页。例如，对于默认的16KB InnoDB页面大小，最大行大小略小于8KB。对于64KB页面，最大行大小略小于16KB。

 

然后对比两个库得innodb_page_size参数发现创建成功得是16KB，报错得是8KB，坑！！！

 

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. 

然后这个只能通过初始化得时候配置，后面不能修改；

 

之后通过以下步骤修改page size（1.5T得数据，很烦）：

停机时间长得做法：

逻辑导出数据

重新初始化数据库

逻辑导入数据

 

停机时间短做法：

额外弄个服务器新建数据库innodb_page_size为16K

源库mydumper导出数据

新库导入数据

主从同步

主从切换

然后源数据库重建

xtrabackup新得主库

源服务器用xtrabackup还原做从库

主从切换

额外服务器资源回收