📋

Description

MariaDB Error 1799 indicates that an online DDL operation, specifically creating an index, generated more modification log data than allowed by the 'innodb_online_alter_log_max_size' system variable. This error typically occurs when the table undergoing the index creation experiences significant write activity (INSERT, UPDATE, DELETE) during the online DDL process.

💬

Error Message

Creating index '%s' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

🔍

Known Causes

3 known causes

⚠️

High Table Write Activity

The table being modified by the online index creation experienced a high volume of concurrent write operations (INSERT, UPDATE, DELETE), which filled the modification log buffer too quickly.

⚠️

Insufficient Log Buffer Size

The 'innodb_online_alter_log_max_size' system variable is set to a value that is too low to accommodate the changes occurring during the online index creation.

⚠️

Extensive Concurrent DML

A large number of data manipulation language (DML) statements were executed against the table while the online index creation was in progress, leading to excessive log generation.

🛠️

Solutions

3 solutions available

1. Increase innodb_online_alter_log_max_size easy

Temporarily or permanently increase the size of the InnoDB online alter log.

1

Connect to your MariaDB server.

mysql -u your_user -p

2

Check the current value of `innodb_online_alter_log_max_size`.

SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

3

To temporarily increase it for the current session (until server restart), use the `SET GLOBAL` command. Replace `2147483648` with a larger value (e.g., 4GB). The value is in bytes.

SET GLOBAL innodb_online_alter_log_max_size = 2147483648; -- Example: 2GB

4

Re-run your `CREATE INDEX` or `ALTER TABLE` statement.

CREATE INDEX index_name ON table_name (column_name); -- Or your original DDL statement

5

To make this change permanent, edit your MariaDB configuration file (e.g., `my.cnf` or `my.ini`) and add or modify the `innodb_online_alter_log_max_size` parameter under the `[mysqld]` section. Restart MariaDB for the change to take effect.

[mysqld]
innodb_online_alter_log_max_size = 4294967296 -- Example: 4GB

2. Reduce the impact of the DDL operation medium

Perform the DDL operation during periods of low activity or on smaller datasets.

1

Identify the table and the index being created. Understand the size of the table and the expected impact of the index creation.

SELECT table_name, index_name FROM information_schema.statistics WHERE table_schema = 'your_database_name' AND index_name = 'your_index_name';

2

If possible, schedule the DDL operation during off-peak hours when there is less write activity on the table. This reduces the amount of data that needs to be logged.

N/A (This is a scheduling consideration)

3

If the table is very large and you can afford to break down the operation, consider creating the index on smaller chunks of data. This is a more advanced technique and might involve creating a temporary table, copying data, creating the index on the temporary table, and then swapping.

N/A (This is a complex operational strategy and depends heavily on table structure and data)

4

If the index is being created as part of a larger `ALTER TABLE` statement that also involves significant data modifications, consider performing the index creation as a separate, earlier step if possible. This allows the log to be potentially cleared between operations.

N/A (This is a DDL statement restructuring consideration)

3. Perform the DDL operation offline medium

If online operations are consistently problematic due to log size, perform the DDL operation while the table is locked.

1

Identify the table you need to modify.

N/A

2

For a more controlled, offline operation, you might need to stop writes to the table or even the application that uses it. This ensures no new data is being written while the DDL is processed.

FLUSH TABLES WITH READ LOCK; -- Use with extreme caution and only if you can tolerate downtime

3

Perform the `CREATE INDEX` operation. This will lock the table for the duration of the operation.

CREATE INDEX index_name ON table_name (column_name);

4

Once the index creation is complete, release the lock.

UNLOCK TABLES;

5

Alternatively, if you are using `pt-online-schema-change` or `gh-ost`, these tools manage table copying and swapping in a way that minimizes downtime. However, they still might require sufficient temporary space and can be affected by the underlying DDL operation's log requirements if not configured carefully.

N/A (This refers to external tools)

🔗

Related Errors

5 related errors

MariaDB Error 1799: Online DDL Log Exceeded