Bug #67141 In-place ALTER TABLE is impossible for BINARY columns
Submitted: 9 Oct 2012 5:18 Modified: 7 Dec 2012 20:04
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.67 OS:Any
Assigned to: CPU Architecture:Any

[9 Oct 2012 5:18] Dmitry Lenev
Renaming or changing default value for column which has a BINARY type, causes full table copy and is not executed in-place. The same operation for columns of CHAR is executed in-place, without copy.

This issue also affects new in-place ALTER implementation in 5.6.

It was originally reported by Rich Prohaska on internals@ mailing list.
See: http://lists.mysql.com/internals/38601

How to repeat:
# Here is the script for mysqltest which shows the problem:

create table t1 (b binary(1));
create table t2 (c char(1));
insert into t1 values ('a');
insert into t2 values ('a');
alter table t1 change b bb binary(1);
# affected rows: 1
# info: Records: 1  Duplicates: 0  Warnings: 0
# The above means that copying happens.
alter table t2 change c cc char(1);
# affected rows: 0
# info: Records: 0  Duplicates: 0  Warnings: 0
# Works without copying !!!

Suggested fix:
It seems that in this case Field_str::is_equal() doesn't work correctly for columns of BINARY type.
[7 Dec 2012 20:04] Paul Dubois
Noted in 5.7.0 changelog.

For an ALTER TABLE statement that renamed or changed the default
value of a BINARY column, the alteration was done using a table copy 
and not in place.
[26 Jun 2013 12:56] Dmitry Lenev
Bug #69580 "Renaming a column in Online DDL requires table copy for binary data types" was marked as duplicate of this bug.
[11 Oct 2013 16:39] Paul Dubois
Posted by developer:
Noted in 5.6.15 changelog.