:An error is reported when you create an index on the MySQL database

Issue

When you perform operations in the DMS console, such as creating indexes on an apsaradb RDS for MySQL instance or a user-created MySQL database (a user-created database on ECS or a user-created database in IDCs), the information similar to the following is prompted.

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

Cause

Index creation fails because the value of the innodb_online_alter_log_max_size parameter is set to an excessively small value. For more information about innodb_online_alter_log_max_size parameters, see more information.

Solutions

• ApsaraDB RDS for MySQL instance
  If you want to modify this parameter, see set instance parametersset innodb_online_alter_log_max_size parameters.
  
• User-created MySQL database
  Log on to the database and refer to the following statement to modify the size of this parameter.

  SET GLOBAL innodb_online_alter_log_max_size=[$Size];

  note: contact the DBA support personnel for the Size evaluation of the [$Size] value.

  
  

References

The innodb_online_alter_log_max_size parameter is a new parameter added to MySQL 5.6.6 to specify the maximum size of a temporary log file (128MB by Byte by default) that is used when you perform online DDL operations on InnoDB tables. The temporary file is used when you create an index or run the ALTER Statement to modify a table. This file records the data inserted, updated, and deleted during DDL operations. When necessary, the log file size increases by capacity based on the innodb_sort_buffer_size value until the maximum value specified by the innodb_online_alter_log_max_size is reached. If the size of the temporary table exceeds this upper limit, using the ALTER statement to modify the table will fail, that is, all the current uncommitted DML operations will be rolled back.

Therefore, setting a large value allows more DML operations to be performed during an online DDL operation. However, an excessively large value leads to a longer period of time to lock a table after DDL operation (lock the table, application log entry to the table). That is, too much new data comes in during task execution, making it impossible to store temporary files. For more information innodb_online_alter_log_max_size parameters, see MySQL documentation.

Applicable scope

• Data Management (DMS)
• ApsaraDB RDS