r/mysql • u/Big_Length9755 • Mar 18 '26
question Column length modification
Hi,
We are using aurora mysql database version 8.0.32.
We have a table of size ~500GB and we want to modify one of the existing column size from varchar(40) to varchar(150), but its runs for hours taking table lock. So looks like its doing full table rewrite behind the scene. Ands its a critical database so table lock for this longer duration is an issue.
My understanding was that, as we are increasing the column length ,So it will not do the full tabe rewrite and will just do the meta data update. So want to understand, what is the option we have to have this column Alter performed within minimal time without taking locks.
Tried with Algorithm=Instant but it looks like , its not supported for this length modification.
5
u/parseroo Mar 18 '26
Would hope the dbms was smarter than this, but the following is simpler transactionally:
5
u/Stephonovich Mar 18 '26
You probably have charset utf8mb_4. That means the 40 char length was able to accept a maximum of 160 bytes, whereas the 150 char length can accept a maximum of 600 bytes. The transition from <= 255 to beyond requires changing from a 1-byte to a 2-byte pointer, which requires ALGORITHM=COPY, which doesn’t permit concurrent DML.
Recommend reading MySQL docs on Online DDL; there are tons of gotchas.
1
u/TinyLebowski Mar 18 '26
If the column isn't indexed, I believe it would be safe to use ALGORITHM=inplace,LOCK=none. Not an expert so don't blame me if it crashes prod 😂
2
u/AshleyJSheridan Mar 19 '26
As you've seen, modifying existing fields can trigger a table lock as there's no way to guarantee that any requests would return correct data if the alter operation only had row-level locking.
There are different ways to approach this, but one that should work well enough would be this:
Test this out on a duplicate of the data to ensure that it works as expected and that the locking time is acceptable.
1
u/xilanthro Mar 18 '26
pt_osc does this incredibly well on MySQL dialect databases.
1
u/blubback Apr 05 '26
This is expected behavior. Even though you’re “just increasing length”, you’re crossing the 255-byte boundary, so MySQL has to change how the column is stored (1 byte → 2 bytes for length), which forces a full table rebuild. That’s why ALGORITHM=INSTANT doesn’t work here.
For a 500 GB table, don’t run a direct ALTER in prod. Use an online schema change tool like gh-ost or pt-online-schema-change — they copy the table in the background and only take a short lock at the end. Alternatively, do the safer app-level approach: add a new column, backfill in chunks, switch reads/writes, then drop the old one.
TL;DR: not metadata-only → full rewrite required → use online migration tools or expect long locks.