<p>InnoDB exceeded the online ALTER modification log limit set by innodb_online_alter_log_max_size when creating or altering an index.</p>
<p>MySQL Error 1799 ER_INNODB_ONLINE_LOG_TOO_BIG appears when an online ALTER TABLE or CREATE INDEX operation needs more modification log space than innodb_online_alter_log_max_size allows. Increase the variable or run the ALTER offline to resolve the issue.</p>
Creating index '%s' required more than
MySQL raises Error 1799 when an online ALTER TABLE or CREATE INDEX statement generates more row changes than InnoDB can store in its online ALTER modification log. The log size ceiling is controlled by the global variable innodb_online_alter_log_max_size.
Because online DDL lets the table stay writable, every INSERT, UPDATE, and DELETE made during the operation must be tracked. When the tracking file outgrows the configured limit, InnoDB cancels the statement and returns ER_INNODB_ONLINE_LOG_TOO_BIG.
The problem surfaces on busy tables that receive continual writes while you build a new secondary index or change column definitions with ALGORITHM=INPLACE or ALGORITHM=INSTANT. Large batch jobs or replication traffic can quickly push the modification log past its maximum size.
The error is version dependent. It most commonly affects MySQL 5.6 to 8.0 where online DDL is widely used. Settings identical in forks such as MariaDB may also trigger similar messages.
Failed DDL blocks schema migrations, delays release cycles, and increases maintenance windows. Re-running large index builds wastes compute and I/O. Continuous failure can also leave applications without needed performance or functional improvements.
InnoDB stores row changes made during online DDL in a temporary log file located in the same directory as the system tablespace. When the DDL finishes, InnoDB replays the log to bring the new index in sync before committing the operation.
The size limit prevents runaway disk usage, but conservative defaults may be too small for high-write workloads. Understanding and tuning this limit ensures reliable online schema changes.
Bulk imports, ETL jobs, or heavy OLTP workloads produce more row modifications than the log size can handle.
The default 128MB limit (varies by version) is insufficient for large tables or long-running index builds.
Prolonged transactions extend the duration of the DDL, allowing the modification log to grow unchecked.
Filesystem quotas or low free space force administrators to keep the log size small, increasing the chance of overflow.
Occurs when DDL waits too long for metadata locks. Reducing transaction time or using NOWAIT clauses helps.
Signals a deadlock between DDL and DML sessions. Rearranging statement order or lowering isolation levels can prevent it.
Raised when ALTER TABLE fails to drop an index or column due to dependencies. Removing constraints first resolves it.
Yes. The variable is dynamic. Use SET GLOBAL and ensure the session has SUPER or SYSTEM_VARIABLES_ADMIN privilege.
Choose a size below available disk free space. Values up to several gigabytes are common on production servers with large tables.
Galaxy flags long-running online DDL in its SQL editor and suggests increasing the log size before execution, reducing trial-and-error migrations.
No. Add innodb_online_alter_log_max_size to my.cnf or use SET PERSIST to make it permanent.
.avif)
.avif){kind=logo}
