MySQL ALTER TABLE takes hours on a big table
ALTER TABLE runs for hours on a large tableShort answer
An ALTER TABLE is slow when it has to rebuild the table. Changing a column's data type always copies the whole table. Adding a column, renaming one or setting a default is a metadata change and is near instant in current MySQL. Name the algorithm explicitly: MySQL then either uses it or fails with an error, and never silently falls back to a copy.
Fix it fast
1Find out which cause you have
Decide which operation you really run, then ask MySQL for the cheapest algorithm. If it cannot do it that way, MySQL returns an error instead of copying the table.
ALTER TABLE orders ADD COLUMN note VARCHAR(100), ALGORITHM=INSTANT;2Match what you see, then jump to its fix
- 1
The statement changes INT to BIGINT, VARCHAR to TEXT, a character set, or similarChanging a column's data type needs a full table copyFix - 2
You extend VARCHAR(100) to VARCHAR(300) or similar and it is slowA VARCHAR is being grown across the 255 to 256 byte boundaryFix - 3
Reordering columns, or changing NULL to NOT NULLThe change rebuilds the table in placeFix
- 1
What does the error look like?
ALTER TABLE runs for hours on a large tableWrites to the table wait while the ALTER runs
Cause 1 of 3
Changing a column's data type needs a full table copy
1Check
Changing the data type is supported only with ALGORITHM=COPY, which rebuilds the table and does not allow concurrent DML.
ALTER TABLE orders MODIFY id BIGINT, ALGORITHM=INPLACE;2Fix
Plan it as a maintenance job, off-peak. If the downtime is too long, build the new column or table beside the old one and move rows in batches.
ALTER TABLE orders ADD COLUMN id_big BIGINT, ALGORITHM=INSTANT;
-- fill id_big in small batches, then switch the application3Verify
The first statement finishes in moments, because adding a column is a metadata change.
SHOW CREATE TABLE orders\GConfirmed by dev.mysql.com
Cause 2 of 3
A VARCHAR is being grown across the 255 to 256 byte boundary
1Check
Growing a VARCHAR is an in-place change only while it stays under 256 bytes or stays at 256 bytes or more. Crossing that boundary is not supported in place, and shrinking a VARCHAR needs a table copy.
SELECT column_name, character_maximum_length FROM information_schema.columns WHERE table_name = 'orders';2Fix
If you can, choose a size that stays on the same side of the boundary. Remember that the limit is in bytes, so a multibyte character set counts several bytes per character.
ALTER TABLE orders MODIFY name VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE;3Verify
The statement is accepted with the clause, or fails with an error at once instead of copying.
SHOW WARNINGS;Confirmed by dev.mysql.com
Cause 3 of 3
The change rebuilds the table in place
1Check
These run in place but may rebuild the table, so they take time proportional to the table size even though DML is allowed.
ALTER TABLE orders MODIFY note VARCHAR(100) NOT NULL, ALGORITHM=INSTANT;2Fix
Run these when load is low, and state ALGORITHM=INPLACE, LOCK=NONE so a statement that would block writes is refused.
ALTER TABLE orders MODIFY note VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;3Verify
The statement runs while writes continue.
SHOW PROCESSLIST;Confirmed by dev.mysql.com
How do I stop it coming back?
- Always write ALGORITHM and LOCK in production ALTER statements.
- Test the exact statement on a copy of the table to see how long it takes.
- Choose column types with room to grow, so later changes stay metadata-only.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix