One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table.

I watched PROCESSLIST and saw that ALTER TABLE sentence was being locked for 5 minutos in 'copy to tmp table' state.

I'm wondering why MySQL needs to copy table to a tmp table to enlarge a VARCHAR.
I mean, isn't supposed that one of the benefits of VARCHAR is that every row only uses needed bytes for this column as opposed to CHAR that uses all available bytes?.

From http://dev.mysql.com/doc/refman/5.1/en/char.html:

VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value

I understood that, while enlarging a CHAR will need to modify all table data to add new bytes in each row, enlarging a VARCHAR will only need modifications in table schema leaving data as is.

Why does MySQL need to copy the table to a tmp table?

  • As to why exactly, I can't say - DDL operations are always expensive with RDBMSs. However, if this is presenting you with problems, take a look oak-online-alter-table. It is designed to "perform a non-blocking ALTER TABLE operation". It's available from here code.openark.org/forge/openark-kit. Author's creds here – Vérace Jun 10 '14 at 15:12
  • Can you provide the actual ALTER TABLE statement and the character set of the column? Is it a utf8 column or an ASCII column? If utf8, then the conversion from 20 to 100 (characters) will require a change in the length prefix from 1 byte (20x3=60) to 2 bytes (3x100=300) as 300 is bigger than 255. But I think MySQL will need a temp table even if you modified the column to, say 80 characters. – ypercubeᵀᴹ Jun 10 '14 at 15:34
  • Your question intrigued me so I looked a bit further. You don't mention if your tables are MyISAM or InnoDB. Another tool is here (see here). Not enough room in comment, but if you Google terms like "innodb alter table locking" you find this. It appears that InnoDB is getting progressively better, but from what I can see, ALTER TABLE ADD COLUMN is one op which is still blocking. Forget MyISAM. – Vérace Jun 10 '14 at 15:51

This is the paradigm of ALTER TABLE. It is a simpler approach to making changes. Keep in mind that MySQL supports an open storage engine plugin architecture. The same calls to perform every operation fits InnoDB, MyISAM, and other storage engines.

Under the hood, doing


would mechanically work like this

CREATE TABLE tblname_new LIKE tblname;
ALTER TABLE tblname_new MODIFY COLUMN mycolumn VARCHAR(255);
INSERT INTO tblname_new SELECT * FROM tblname;
DROP TABLE tblname;
ALTER TABLE tblname_new RENAME tblname;

This approach is totally storage engine agnostic.

The alternative to this would be for the storage engine to actively manage a list of blocks that can hold new rows.

What happens when you deal with...

CHAR columns

Even if such management was in place, it would never get summoned because none of the fragments would be large enough to hold changed rows since the column had a smaller size before. This would result in appending all the rows to the physical file for the table. Now, the space occupied by the rows before the change would appear before the first of the table after the ALTER TABLE was done. This would be true for expanding CHAR variables.

VARCHAR columns

In the case of expanding a VARCHAR, altering lengths to be larger may require changing from 1 to 2 bytes for every row should you expand a VARCHAR(255) (255 is a 1-byte unsigned number) to VARCHAR(256) (256 is a 2-byte unsigned number). The introduction of that one extra byte would still require vigorous fragmentation block list management just for the sake of accommodating one extra byte. Therefore, actual space management via maintaining a fragmentation block list would be highly impractical for the open storage engine plugin architecture of MySQL. Such management would probably be in Oracle, SQL Server and PostgreSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.