'mysql 5.6 adjust varchar length to longer value without table locking

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.

mysqlmysql-5.6


Solution 1:[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.

Solution 2:[2]

I'm 99% certain that an alter against any table regardless of engine type will result in the table being locked until the op is complete, even with InnoDB's 'row level' locking capabilities.

If you can stand a 30-45 second outage where some connections will may and ultimately lost, then the easiest choice is to just pull the trigger. Or you could implement on of the following:

Put your site into 'maintenance mode' a few minutes before op, execute the op, then take the site out of maintenance mode.

Or, if you have a master master replication setup with a floating ip and dns, you could stop do this:

  1. Stop replication on standby master
  2. Run alter
  3. Switch floating ip to standby master
  4. Stop replication on primary master
  5. Run alter
  6. Restart replication on both masters
  7. Switch floating ip back to primary master

Solution 3:[3]

I believe you are trying this in production and you wish not to hamper your system.

You can do this in another way. Lets say, you want to change a column url(varchar 255) to url(varchar 400).

  1. create another column, url2(varchar 400)
  2. copy all data from url-> url2
  3. rename "url" column to "url3"
  4. rename "url2" column to name

run query for 2rd and 4th step together and it will take milli seconds to execute.

So There will be no table lock and your application will run smoothly.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AYTWebSolutions
Solution 2
Solution 3 Tasdiqul Islam

Related Questions