BuildNot yet confirmed elsewhere1 publisher2 min readPublished
A FULLTEXT key turns MariaDB's instant ADD COLUMN into a table rebuild that blocks writes
MariaDB rebuilt a 320 MB table for a plain ADD COLUMN and blocked a write for over 30 seconds when a FULLTEXT key was present, a dev.to test found. An identical table without the index took 60 milliseconds.
The Engineer · Build desk
Key figures: in the reproduction, two separate ALTER statements took 177 seconds in total, versus 34 seconds for one combined statement. The change took 60 milliseconds without the FULLTEXT index. Each test table had 200,000 rows.
| Figure | Value | Note | Claim |
|---|---|---|---|
| Two separate ALTERs in total, vs 34 for one combined | 177 seconds | 16 | |
| Change, no FULLTEXT index | 60 milliseconds | 10 | |
| Rows per test table | 200,000 rows | 6 |
What happened
- On the larger installs of a news CMS release, the articles table, a few hundred MB, was rebuilt, the release took minutes instead of seconds, and view-counter updates waited.
- The only difference between the installs that rebuilt and those that finished at once was one line in the table definition: FULLTEXT KEY ft_search (title, body).
- An in-place alter with LOCK=NONE on the FULLTEXT table returned ERROR 1846, and the server suggested LOCK=SHARED.
Why it matters
- exposure A table that takes an UPDATE on every page view and carries a FULLTEXT key is exposed, because each visitor holds a connection while the rebuild runs.
- constraint Development databases will not surface it. With a few hundred rows every ALTER finishes instantly, so only a schema check or an explicit algorithm will.
- decision Teams with FULLTEXT tables choose between pinning ALGORITHM=INSTANT so the deploy fails fast, and planning the column add as a rebuild with a write stall.
- cost Every extra ALTER statement on such a table is another full rebuild, so migrations should fold their column adds into one statement.
An ALTER that names no algorithm leaves the choice to the server. The CMS migration was three ADD COLUMN statements with no ALGORITHM clause [4], so the server was free to pick, and on the installs that rebuilt, it did [2]. The post gives MariaDB 10.3 and MySQL 8.0 as the versions where this add is usually instant [1].
The instant path changes metadata only and leaves existing rows physically untouched. Tables with a FULLTEXT index are excluded from it [12]. InnoDB ties each row to its full-text entries through auxiliary tables and a hidden FTS_DOC_ID column [11], so the server rebuilds the table and the full-text index with it. That index rebuild cannot run alongside concurrent writes [12]. The best the server offered was a shared lock [8]: reads continue and writes wait [9].
The author says this is documented behaviour in both MariaDB and MySQL [13]. The reproduction ran on a local MariaDB instance only [6].
The useful part of the test is the probe. Asking for ALGORITHM=INSTANT explicitly succeeded on the plain table and failed with ERROR 1845 on the FULLTEXT one [7]. A statement carrying that clause cannot turn into a rebuild without telling you. We would put it on every migration that is meant to be instant. The post shows the error on a 200,000-row table [6]. We'd expect the same refusal on a small development table, since it concerns the operation and not the row count, but the post does not test that.
Treat the timings as one laptop's, run with nothing else on it [10]. The author says numbers will differ on other hardware [17]. For the stall to transfer, your table has to be large enough that a rebuild takes real time, and something has to write to it on the hot path. The production table was a few hundred MB and the release took minutes [2], the same size class as the 320 MB test table [6]. The write was the counter update on every article page [5].
Batching shows an odd number. Two separate statements took 177 seconds against 34 for one combined statement [16], a ratio of about 5.2 [18]. Counting rebuilds alone, two against one predicts 2x [19]. The post does not explain the gap, and it is a single run. Production had three statements, each a full rebuild [15], and the fix was one ALTER carrying all three ADD COLUMN clauses [15].
What to watch
- A repeat of the test on MySQL 8.0, which the post says behaves the same way but did not run.
- Timings on server hardware for the 177 seconds versus 34 seconds comparison, which the post reports from a single laptop run.
- Whether the CMS project's migration tooling adds an explicit ALGORITHM clause or a pre-flight FULLTEXT check.
Clarity's read
What the record supports and how the coverage leans. The claims behind it follow.
Reality
- Evidence62
- Adoption
- Insufficient
- Hype gap−5
- Incentives
- Insufficient
- Confidence65
Claim ledger
Ranked by verification strength, evidence, and original report placement.
- [1]
On MariaDB 10.3+ and MySQL 8.0+, adding a nullable column to an InnoDB table is usually an instant operation: a metadata change with no data copied.
- [2]
On the larger installations of a news CMS release, the articles table (a few hundred MB) was rebuilt from scratch, the release took minutes instead of seconds, and every page view that tried to bump a view counter waited until the rebuild finished.
- [3]
The difference between the installations that finished instantly and those that rebuilt was one line in the table definition: FULLTEXT KEY ft_search (title, body).
- [4]
The migration was three separate ALTER TABLE articles ADD COLUMN statements, one per change, with no ALGORITHM clause.
- [5]
Every article page runs an update of the form UPDATE articles SET hits = hits + 1 WHERE id = ?.
- [6]
The author reproduced the problem on a local MariaDB instance with two identical tables of 200,000 rows each (about 320 MB), one with a FULLTEXT index and one without.
- [7]
ALTER TABLE ... ADD COLUMN c1 INT NULL, ALGORITHM=INSTANT returned Query OK on the table without the FULLTEXT index and ERROR 1845 (0A000), 'ALGORITHM=INSTANT is not supported for this operation', on the table with it.
- [8]
On the FULLTEXT table, ALGORITHM=INPLACE, LOCK=NONE returned ERROR 1846 (0A000): LOCK=NONE is not supported, because fulltext index creation requires a lock, with the suggestion to try LOCK=SHARED.
- [9]
Because the full-text index has to be rebuilt along with the table, the server needs at least a shared lock: reads keep working, writes wait.
- [10]
With a second connection firing a single counter update one second into each ALTER, the change took 60 milliseconds without the FULLTEXT index; with it, the update was blocked for more than half a minute, on a laptop with nothing else running.
- [11]
InnoDB implements full-text search with a set of auxiliary tables and a hidden FTS_DOC_ID column that ties each row to its entries in the full-text index.
- [12]
Instant ADD COLUMN works by changing only metadata and leaving existing rows untouched; tables with a FULLTEXT index are excluded from that path, so the server rebuilds the table and the full-text index, a step that cannot run with concurrent writes.
- [13]
The author says this is not a bug but documented behaviour in both MariaDB and MySQL.
- [14]
The behaviour is easy to never hit in development, where the table has a few hundred rows and every ALTER finishes instantly regardless of the algorithm.
- [15]
The migration's three ALTER TABLE statements meant three full rebuilds and three times the write freeze; the fix was a single ALTER TABLE with three ADD COLUMN clauses, giving one statement and one rebuild.
- [16]
In the reproduction, two separate ALTER statements took 177 seconds in total versus 34 seconds for the combined statement.
- [17]
The author says readers will see different numbers on their own hardware.
- [18]
The separate statements took about 5.2 times as long as the combined statement.
- [19]
Counting rebuilds alone, two separate statements against one combined statement predicts a ratio of 2x.
- [20]
On a live site every page view during the rebuild window holds a connection while it waits; depending on connection limits and timeouts, that is the difference between a slow release and a site returning errors for a minute.
Sources
1 independent publisher whose own reporting we read for this story.
- dev.toInstant ADD COLUMN, Until a FULLTEXT Index Shows Up: A MariaDB Migration That Froze Writes
1 article · October 12, 2026
Topics and entities
Follow any of these and your For You feed starts watching them — no settings page required.
Topics
- In-Database Full-Text SearchFollow
- MySQL and MariaDBFollow
- Database Schema MigrationsFollow