MySQL Bugs: #67141: In-place ALTER TABLE is impossible for BINARY columns

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:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.67	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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');
--enable_info
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 !!!
--disable_info

Suggested fix:
It seems that in this case Field_str::is_equal() doesn't work correctly for columns of BINARY type.

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.

Bug #69580 "Renaming a column in Online DDL requires table copy for binary data types" was marked as duplicate of this bug.

Posted by developer:
 
Noted in 5.6.15 changelog.