mysql 5.6 adjust varchar length to longer value without table locking

Question

We have a varchar column right now that is 255 chars in length. We're about to up it to 400 using this statement:

ALTER TABLE `resources` CHANGE `url` `url` varchar(400) NOT NULL;

I've read the docs about online ddl which states

Operation                   In-Place?   Copies Table?   Allows Concurrent DML?  Allows Concurrent Query?
---------------------------|-----------|---------------|-----------------------|---------------------------------
Change data type of column  No          Yes             No                      Yes

And I have these two questions:

• does changing the col from varchar(255) to varchar(400) constitute a changing of data type?
• will this lock the table for writes?

I guess on question two, it just seems unclear what concurrent DML really means. Does it mean I can't write to this table at all, or that the table goes through the copy/swap process?

We only have about 2.5 million rows in this table, so the migration only takes about 30 seconds, but I'd prefer the table not be locked out during the time period.

Answer 1

I had the same question and ran some tests based on advice from Percona. Here are my findings:

ALTER TABLE `resources` CHANGE `url` `url` varchar(400), ALGORITHM=INPLACE, LOCK=NONE;

Running this on 5.6 should produce something similar to:

[SQL]ALTER TABLE `resources` CHANGE `url` `url` varchar(400), ALGORITHM=INPLACE, LOCK=NONE;
[Err] 1846 - ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

What this means is that you cannot perform this operation as MySQL is considering this to be a column type change, and therefore a full table copy must be performed.

So let's try to use the COPY algorithm as suggested in the output, but set LOCK=NONE:

ALTER TABLE `resources` CHANGE `url` `url` varchar(400), ALGORITHM=COPY, LOCK=NONE;

And we get:

[SQL]ALTER `resources` CHANGE `url` `url` varchar(400), ALGORITHM=COPY, LOCK=NONE;
[Err] 1846 - LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

Trying to set LOCK=SHARED and attempting an insert on the table results in the query waiting for a metadata lock.