MySQL创建索引抛出“requiredmorethan'innodb_online_alter__MySQL

MySQL创建索引抛出“required more than 'innodb_online_alter_log_max_size' bytes of modification log”异常的解决方案

1.问题描述

为两台机器上MySQL Server中zabbix2数据库history_unit表创建idx_clock(clock)索引时出现异常。

执行的SQL语句如下：

alter table history_uint add index idx_clock (clock);

抛出的错误信息如下：

ERROR 1799 (HY000): Creating index 'idx_clock' required more than'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

2.问题原因

根据错误信息中的内容在MySQL手册中找到了innodb_online_alter_log_max_size这一参数。该参数是MySQL5.6.6新加入的一个参数，用以指定对InnoDB表做在线DDL操作时所使用的临时日志文件的最大大小（以字节为单位，默认128M）。在创建索引或者ALTER表时会使用该临时文件。该文件记录了DDL操作期间插入、更新、删除的数据。在必要的时候该日志文件的大小会根据innodb_sort_buffer_size的值增加容量直至达到innodb_online_alter_log_max_size指定的最大值。若临时表的大小超出此上限则ALTER表的操作会失败，当前所有未提交的DML操作会回滚。因此，一个较大的值允许在线DDL操作期间有更多的DML被执行，但是过大的值会使DDL操作结束后表被锁定起来以应用日志中的数据时花很长的时间。

3.解决办法

重新指定innodb_online_alter_log_max_size变量为一个比较大的值。因为该变量是动态的而且是全局的，所以可直接在连接会话中重新指定变量的值并能即刻生效。（最好在MySQL配置文件中将该变量的值也更改为对应的值以便于MySQL重启后能使用新设置的值。）语句如下：

SET GLOBAL innodb_online_alter_log_max_size=A BIGER VALUE

变量innodb_online_alter_log_max_size的值更改成功后重新执行创建索引的语句即可。