Why You Might Not Be Able to Add a Column with INSTANT in MariaDB

• Introduced in MariaDB 10.3.2, INSTANT allows adding columns to InnoDB tables without locking the entire table or rebuilding it. This can significantly reduce downtime for schema changes.

Limitations of INSTANT

However, there are certain conditions that can prevent INSTANT from working:

• Column Position: In MariaDB 10.3.2, INSTANT only works for adding columns to the end of the table. In later versions (10.4+), it's more flexible but still has limitations.
• Hidden FTS_DOC_ID Column: If your table has a hidden FTS_DOC_ID column due to a FULLTEXT index, INSTANT won't be usable for adding columns.
• Incompatible Storage Format: Sometimes, upgrading MariaDB might not immediately convert the storage format for all existing columns. If you're adding a column to a table created in an earlier version, MariaDB might need to update the storage format for existing columns before using INSTANT. This can be a bottleneck for large tables.

Troubleshooting Tips

If you're facing issues with INSTANT, here's how to troubleshoot:

1. Check Column Position: Make sure you're trying to add the column to the end of the table.
2. Verify FTS_DOC_ID Absence: If you have a FULLTEXT index, ensure there's no hidden FTS_DOC_ID column. You can check this using SHOW CREATE TABLE or information schema tables.
3. Consider Rebuilding the Table (if necessary): If the storage format issue is suspected, rebuilding the table using ALTER TABLE table_name ENGINE=InnoDB; might be necessary. However, this can take as long as a non-INSTANT column addition.

Alternative Approaches

• Online DDL with Other Algorithms: MariaDB supports other algorithms like COPY and INPLACE for online DDL (Data Definition Language) operations. These might be suitable depending on your specific scenario.
• Offline Schema Changes: If downtime is acceptable, take the table offline, add the column, and then bring it back online. This ensures a more predictable outcome but might not be ideal for highly available systems.

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

ALTER TABLE my_table ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;

This code successfully adds the created_at column to the end of the my_table using INSTANT (assuming MariaDB 10.4 or later).

Attempting to Use INSTANT for a Column Not at the End (Pre-10.4 or Restricted Scenario)

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT
) ENGINE=InnoDB;

ALTER TABLE my_table ADD COLUMN city VARCHAR(50) AFTER name; -- Might not use INSTANT

In MariaDB versions before 10.4 or if INSTANT is disabled for some reason, this code might not use INSTANT because the city column is being added after the name column.

Rebuilding the Table (if INSTANT fails due to storage format issues)

ALTER TABLE my_table ENGINE=InnoDB; -- Rebuilds the table (might take time)

This code rebuilds the entire my_table with a compatible storage format. This might be necessary if INSTANT fails due to storage format incompatibility in older tables.

Adding a Column Offline

ALTER TABLE my_table ADD COLUMN status ENUM('active', 'inactive');

-- Take the table offline for maintenance (implementation depends on your environment)

-- Perform any additional changes or optimizations

-- Bring the table back online

This approach involves taking the table offline for maintenance to add the status column. While more predictable, it might not be ideal for highly available systems.

MariaDB offers other online DDL (Data Definition Language) algorithms for adding columns:

• COPY Algorithm: This algorithm creates a temporary table with the new column structure, copies data from the original table, and then swaps the tables. It can be a good choice for adding columns to the middle of the table.

Example:

ALTER TABLE my_table ADD COLUMN city VARCHAR(50) ALGORITHM=COPY;

• INPLACE Algorithm: This algorithm rearranges the table data to accommodate the new column. It can be slower than COPY but might be more suitable for certain scenarios.

ALTER TABLE my_table ADD COLUMN phone_number VARCHAR(20) ALGORITHM=INPLACE;

Partitioning (MariaDB 10.2+)

If your table is already partitioned, you can add the new column to a specific partition or all partitions online. This can be faster than modifying the entire table at once.

pt-online-schema-change (Third-party Tool)

This is a popular open-source tool specifically designed for online schema changes in MySQL/MariaDB. It can handle various scenarios, including adding columns to large tables. Its usage involves installing and running the tool on your server.

Offline Schema Change

If downtime is acceptable, you can take the table offline, add the column using a standard ALTER TABLE statement, and then bring it back online. This approach is simpler but can cause downtime for your application.

Choosing the Best Method:

The best method for you depends on several factors:

• MariaDB version: Some algorithms are only available in newer versions.
• Column position: COPY can be useful for adding columns in the middle.
• Performance impact: Consider downtime tolerance and impact on ongoing operations.
• Table size and complexity: Larger tables might benefit from specialized tools.