11

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.

timsabat
  • 2,208
  • 3
  • 25
  • 34

3 Answers3

9

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.

AYTWebSolutions
  • 331
  • 4
  • 6
1

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.

Tasdiqul Islam
  • 21
  • 2
0

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
Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
  • Hm, mysql 5.6 allows "online schema change", meaning you can perform most alter operations without consequence. We alter table all the time to drop/add columns and some migrations take hours. The question I had was related to the documentation and whether "change column type" would online schema change from working. The docs are not really clear. – timsabat May 14 '15 at 22:09
  • "some migrations take hours" during these migrations, do you you still allow writes to occur? In my experience we have never run an alter without doing the maint mode, or the floating ip switch, because the table is indeed locked during the time it takes to execute the alter. – Mike Purcell May 14 '15 at 22:15
  • Yep, writes. I'm not disagreeing with you to be confrontational, just so we're all clear about how this works. Read about it here: https://www.percona.com/blog/2013/07/05/schema-changes-whats-new-in-mysql-5-6/ – timsabat May 14 '15 at 22:30
  • Understood, didn't take your responses as confrontational. That's pretty awesome feature of 5.6, my something new for the day, and you are correct it is not clear. Did you run the alter yet? – Mike Purcell May 14 '15 at 22:35