MySQL creates an index and throws "requiredmorethan 'innodb _ online_alter _ MySQL

MySQL creates an index and throws "requiredmorethan bitsCN.com ".

MySQL creates an index and throws a "required more than 'innodb _ online_alter_log_max_size 'bytes of modification log" exception.

1. problem description

An exception occurs when an idx_clock (clock) index is created for the history_unit table of the zabbix2 database in the MySQL Server on two machines.

The executed SQL statement is as follows:

alter table history_uint add index idx_clock (clock);

The error message is as follows:

ERROR 1799 (HY000): Creating index 'idx _ clock 'required more than 'innodb _ online_alter_log_max_size' bytes of modification log. Please try again.

2. cause

The innodb_online_alter_log_max_size parameter is found in the MySQL manual based on the error information. This parameter is a new parameter in MySQL 128. it is used to specify the maximum size of temporary log files used for online DDL operations on InnoDB tables (in bytes, the default value is mb ). This temporary file is used when you create an index or ALTER table. This file records the data inserted, updated, and deleted during the DDL operation. When necessary, the size of the log file will increase according to the innodb_sort_buffer_size value until the maximum value specified by innodb_online_alter_log_max_size is reached. If the size of the temporary table exceeds the upper limit, the ALTER table operation fails. Currently, all uncommitted DML operations will be rolled back. Therefore, a large value allows more DML to be executed during online DDL operations, however, if the value is too large, it takes a long time for the table to be locked after the DDL operation is completed to apply the data in the log.

3. Solution

Specify the innodb_online_alter_log_max_size variable as a relatively large value. Because the variable is dynamic and global, you can directly re-specify the value of the variable in the connection session and it will take effect immediately. (It is best to change the value of this variable to the corresponding value in the MySQL configuration file so that the new value can be used after MySQL is restarted .) The statement is as follows:

SET GLOBAL innodb_online_alter_log_max_size=A BIGER VALUE

If the value of innodb_online_alter_log_max_size is changed successfully, run the index creation statement again.

BitsCN.com