Troubleshooting VARCHAR Column Expansion Issues with MySQL Online DDL

1 Background Introduction

Recently a business reported repeated failures when trying to extend a VARCHAR column using the fast‑alter‑table path; the task timed out after 10 seconds despite the ALTER TABLE statement being generated correctly.

The failure raised the question of whether the issue lies in the task‑dispatch system or in MySQL itself.

2 MySQL Online DDL

Operation

Extending VARCHAR column size

In Place

Yes

Rebuilds Table

No

Permits Concurrent DML

Yes

Only Modifies Metadata

Yes

The official MySQL documentation shows that extending a VARCHAR column can be done in‑place because only metadata changes are required.

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0‑255 bytes one length byte is used; for 256 bytes or more two length bytes are required. In‑place ALTER TABLE therefore only supports increasing a VARCHAR from 0‑255 to a larger size within the same byte‑range, or from 256+ to a larger size. Switching between the two ranges requires a table copy (ALGORITHM=COPY). https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

Because the length‑byte count does not change, our internal schema‑change system can safely use ALTER TABLE for fast alterations, which saves a lot of time on large tables.

3 Problem Summary

Our schema‑change workflow chooses the optimal method based on the request:

If the change qualifies for fast alteration, we execute ALTER TABLE directly (e.g., dropping indexes, renaming tables/columns, modifying defaults/comments, extending VARCHAR, adding unique indexes on MySQL 8.0, etc.).

If fast alteration is not applicable, we prefer gh‑ost (requires binlog format ROW).

If gh‑ost cannot be used, we fall back to pt‑osc ; adding a unique index will fail in this path.

Problem 1: Default‑Value Handling

We discovered that changing a column’s default value, comment, or name does not prevent fast alteration, so we stopped comparing COLUMN_NAME , COLUMN_COMMENT , and COLUMN_DEFAULT .

The tricky part is the COLUMN_DEFAULT field. A naïve check that only verifies null‑ness would treat a change from “nullable default 1” to “not nullable default 1” as safe, but in reality this requires a data copy.

if(COLUMN_DEFAULT is null, null, "")

Testing showed that the IS_NULLABLE flag correctly captures this situation: a transition from YES to NO (or vice‑versa) forces a copy, while unchanged nullability allows fast alteration.

Problem 2: Indexed Column Issue

When the target VARCHAR column is indexed, the in‑place algorithm appears to be fast but actually rebuilds the index, causing the 10‑second timeout.

Solution: detect indexed columns via information_schema.STATISTICS and route such cases to gh‑ost or pt‑osc instead of direct ALTER TABLE .

select * from information_schema.STATISTICS where table_schema='db' and table_name='table' and column_name='col';

Problem 3: Other Unexplained Timeouts

Some cases exhibited the same 10‑second timeout without a clear reproduction path. After extensive testing we found that rebuilding the table (e.g., issuing ALTER TABLE … ENGINE=InnoDB ) resolved the issue, suggesting hidden metadata or statistics problems.

4 Summary

MySQL Online DDL greatly simplifies schema changes, but real‑world environments can introduce unexpected edge cases such as default‑value nullability, indexed column handling, and stale statistics. It is essential to keep a safety timeout, verify task termination, and have fallback mechanisms (gh‑ost/pt‑osc) to avoid production impact.

Without a 10‑second safety timeout, a wrong plan could increase replica lag and affect online services. Timeout handling must also verify that the MySQL task has truly stopped; otherwise, lingering queries may cause further issues.

All content is for reference only.