Ask Maclean

MySQL InnoDB表的Table is Full错误

症状

4.x, 5.0.84 及以下, 5.1.37 及以下, 带 InnoDB 插件的5.1.40及以下

使用 InnoDB 表空间且不使用 innodb_file_per_table 的所有版本

 

重现问题:

使用自动扩展 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

 

原因

4.x, 5.0.84 及以下, 5.1.37 及以下, 及带 InnoDB 插件的5.1.40 及以下

使用 fixed size InnoDB 表空间和不使用 innodb_file_per_table的所有版本

使用自动扩展InnoDB 表空间包括使用 innodb_file_per_table的所有版本

 

解决方案

Versions 4.x, 5.0.84 及以下, 5.1.37 及以下, 带 InnoDB 插件的5.1.40 及以下

使用固定大小InnoDB 表空间和不使用 innodb_file_per_table 的所有版本

使用自动扩展 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

 

 

应用于：

MySQL服务器版本4.0 及以上

本文信息适用于所有平台。

 

症状

这个错误的方式有两种可能：

4.x, 5.0.84 及以下, 5.1.37 及以下，以及带InnoDB插件的5.1.40及以下版本

当运行以下MySQL 服务器版本之一：

  • MySQL 4.0 和1的所有版本
  • 0.84 及以下
  • 不带InnoDB插件的MySQL 5.1：1.37 及以下
  • 带InnoDB插件的MySQL 5.1：1.40 及以下

 

当有多于1023个动态事务且错误信息”Table is Full”被返回到客户端。有时会发生插入错误。错误日志会包含一个更描述性的错误信息：

 

110912 14:52:14 InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?

 

所有版本 使用固定大小的 InnoDB 表空间且不使用 innodb_file_per_table

当 InnoDB 表空间设为固定大小且不使用 innodb_file_per_table 选项时，尝试将数据插入InnoDB 表时，会发生以下错误：

 

The table ‘<tablename>’ is full

 

其中<tablename> 是表名。相应的 MySQL 错误代码和SQL 状态是：

 

MySQL Error Code: 1114

SQLState: HY000

 

显示在MySQL错误日志中的错误取决于在使用的InnoDB存储引擎版本：

  • 对于带InnoDB插件的MySQL 5.1 和MySQL 5.5 及以上，错误日志包含如下错误：

 

InnoDB: Error: Data file(s) ran out of space.

Please add another data file or use ‘autoextend’ for the last data file.

110908 12:18:13 [ERROR] /usr/sbin/mysqld: The table ‘<tablename>’ is full

 

 

  • 对于MySQL/InnoDB 更早的版本(包括有InnoDB的buildin版本的MySQL 5.1 )，错误信息是：

110908 13:25:37 [ERROR] /usr/sbin/mysqld: The table ‘t1’ is full

 

即使 SHOW TABLE STATUS 或 SELECT * FROM information_schema.TABLES 显示InnoDB 表有大于0的Data_free 。

重现问题：

该问题能通过以下步骤重现：

  1. 以重新安装的MySQL启动
  2. 在首次启动MySQL之前，在你的my.cnf中有这样的设置

innodb_data_file_path = ibdata1:100M

且确保没有启动 innodb_file_per_table

  1. 启动MySQL并让InnoDB 创建数据文件
  2. 登录到MySQL并运行：

CREATE DATABASE innodbtest;

use innodbtest

CREATE TABLE t1 (id mediumint unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB;

 

 

  1. 不断将行插入到表中，直到查询返回 The table ‘t1’ is full。
  2. 当空间即将耗尽，错误信息类似于：

 

SQLSTATE[HY000]: General error: 1114 The table ‘t1’ is full

 

 

  1. 运行SHOW TABLE STATUS：

 

mysql> SHOW TABLE STATUS LIKE 't1'\G

*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3918789
Avg_row_length: 23
Data_length: 90800128
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 3920001
Create_time: 2011-09-08 12:14:35
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

 

 

注意即使没有更多可用的数据，Data_free 仍是4194304 。

 

 

使用自动扩展的 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

当出现Table is full error ，在MySQL错误日志的常见症状为：

 

2014-09-15 08:33:38 7f28aeb8a700 InnoDB: Error: Write to file /mysql/data/ibdata1 failed at offset 20971520.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2014-09-15 08:33:38 7101 [ERROR] /usr/sbin/mysqld: The table 't1' is full
2014-09-15 08:33:39 7101 [ERROR] /usr/sbin/mysqld: The table 't1' is full

 

 

在Windows中，错误可能像这样：

 

 

2014-09-15 10:04:06 12AB InnoDB: Encountered a problem with file C:\ProgramData\MySQL\ibdata1
2014-09-15 10:04:06 12AB InnoDB: Disk is full. Try to clean the disk to free space.
2014-09-15 10:04:06 13D7 [ERROR] C:\Program Files\MySQL\bin\mysqld.exe: The table 't1' is full

 

 

以上是共享的表空间的错误。相似错误能在各个表空间发生。

 

原因

4.x, 5.0.84 及以下, 5.1.37 及以下, 带InnoDB插件的5.1.40 及以下版本

 

这是由于bug If InnoDB Runs Out of Undo Slots, It Returns Misleading ‘Table is Full’。在MySQL 5.1 及以下 InnoDB 支持最多1023个动态事务。要启动更多将会导致错误或在受该bug影响的版本中可能产生声明assertion。

 

使用固定大小的InnoDB 表空间和不使用 innodb_file_per_table 的所有版本

 

该问题是由于InnoDB 耗尽了在共享表空间中的可用空间。在每个参考手册中，Table is full 错误表示磁盘用尽或表达到最大。对于InnoDB，当InnoDB被设为有共享的表空间，InnoDB 表空间的大小 (数据文件) 决定了表的最大空间。

 

InnoDB 表空间包含默认大小为16KB的数据页。有可能一些页没有被完全使用，但是实际上不太可能对新的数据进行提示。因此，SHOW TABLE STATUS报告仍有空间而实际上无法再插入任何数据是有可能的。

 

参见MySQL参考手册中以下页：

  • The table is full
  • Limits on Table Size
  • Configuring InnoDB
  • InnoDB File Space Management

 

使用自动扩展的InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

在这个情况下，起因可能是：

储存表空间的磁盘已满。

已达到设置的磁盘配额。

表空间文件达到文件系统支持的最大尺寸。

 

解决方案

4.x, 5.0.84 及以下, 5.1.37 及以下,和带InnoDB插件的5.1.40及以下版本

解决方案是升级到MySQL的补丁版本。以下版本被打过补丁：

  • MySQL 5.0: 5.0.85及以上
  • 不带InnoDB插件的MySQL 5.1：1.38及以上
  • 带InnoDB插件的MySQL 5.1：1.41及以上
  • MySQL 5.5 及以上：所有版本

 

建议升级到可用的最新GA版本。

 

在升级之前总是创建数据库部分！这包括schema，数据和数据库需要执行的程序（函数，触发器等）。安全总是最好的策略，所以如果在升级过程中有一些问题，很容易恢复到之前的版本。

 

升级教程参见：

  • Downloading MySQL Server
  • 升级或下载MySQL。检查表或索引是否必须重建的部分尤其重要。对下载的版本使用页: 5, 5.1, 5.0。参见 How to Upgrade your Existing MySQL Server to MySQL 5.6 Version 获得更多升级MySQL的帮助。

 

总是在对生产服务器执行升级之前开发或阶段系统中进行测试。

 

使用固定大小的InnoDB表空间和不使用 innodb_file_per_table的所有版本

解决方案使让InnoDB表空间增长，通过执行以下步骤完成：

 

总是在更改应用于生产之前，在开发或阶段系统中尝试步骤。

 

  1. 停止MySQL
  2. 在你喜欢的编辑器中打开MySQL配置文件。
  3. 找到配置innodb_data_file_path 选项的行
  4. 添加一个新表空间或让最近的表空间自动扩展：
  5. 将 innodb_data_file_path 设为

innodb_data_file_path = ibdata1:100M;ibdata2:100M

或类似的

  1. 将 innodb_data_file_path 设为

innodb_data_file_path = ibdata1:100M:autoextend

  1. 保存MySQL配置文件
  2. 再次启动MySQL
  3. 确认现在能够再次将数据插入InnoDB表
  4. 将解决方法迁移到合适的其他环境。

 

参见MySQL参考手册中以下页：

Configuring InnoDB

Adding, Removing, or Resizing InnoDB Data and Log Files

 

使用自动扩展的InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

由于原因是无法进一步增加表空间文件的大小，解决方法包括：

  • 确保表空间文件储存的路径有多余的磁盘空间(参见以下建议，如何释放磁盘空间)。

不要为了释放空间而删除任何InnoDB文件，包括共享的表空间。没有它的这些文件，InnoDB将无法启动。

如何取回空间的教程，参见以下。

  • 确保没有预设的定量会阻止表空间文件的增长。
  • 确保文件系统支持的最大文件尺寸还未达到。如果这是Table is full错误的原因，一些解决方案如下：
    1. 通过你将所有表储存在共享的表空间 (innodb_file_per_table = 0):
      1. 另外创建一个共享的表空间文件。
      2. 或者，你能另外创建一个额外共享的表空间文件启用 innodb_file_per_table ，从而能将表移出共享的表空间。注意到这不会降低共享的表空间大小。参见MySQL Server Variable: innodb_file_per_table获得关于innodb_file_per_table的更多信息。
    2. 对于每个表的表空间的文件，对表进行分区。对已分区的表，每个分区在 自己的表空间中且innodb_file_per_table = 1，所以这是你能将总大小分割到多个文件。

 

释放磁盘空间的可能操作有：

 

  • 使用OPTIMIZE TABLE <table_name>取回每个表空间的空闲空间

这需要重建在表空间文件位置的表。所以只有当你已经释放了空间或将表迁移到另外的位置才有作用。（参见以下）

  • 要取回在共享表空间文件中的文件，参见：How to Reclaim Unused Space and Decrease InnoDB ibdata1 Data File Size.
  • 如果错误日志，慢查询日志，一般查询日志，审计日志等储存在与表空间相同的磁盘。考虑重定向停止并压缩旧日志或将旧日志移动到其他磁盘。参见：
    1. 服务器日志维护
    2. audit_log_rotate_on_size
  • 如果二进制日志位于与数据相同的磁盘上，考虑清楚旧的二进制日志。

确保被清楚二进制日志是不再需要的，例，用于复制或时间点恢复。如果二进制不再需要用于复制但仍要用于恢复，你能在清楚日志之前创建备份。

确保你使用PURGE BINARY LOGS 命令来删除旧的二进制日志。

  • 在MySQL 5.6 中，你能使用 InnoDB native links将在自己tablespace的表空间移到另一个磁盘。
  • 将日志移到另一个磁盘。参见：
    1. What is the General Query Log and How to Control Where the Log is Stored?
    2. What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged?
    3. –log_ error
    4. –audit_ log_file

 

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *