Bug #16857 Altering BINARY(x) column uses incorrect padding
Submitted: 27 Jan 2006 22:07 Modified: 9 Apr 2006 4:48
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.19 OS:Linux (Linux)
Assigned to: Chad MILLER CPU Architecture:Any

[27 Jan 2006 22:07] Kolbe Kegel
Description:
Given a BINARY(X) column, using ALTER TABLE to change the column to BINARY(Y) where Y > X, the end of the column is padded with 0x20 instead of 0x00.

How to repeat:
create table table1 (col1 binary(4));
insert into table1 values ('a'),('a ');
select hex(col1) from table1;
alter table table1 modify col1 binary(10);
select hex(col1) from table1;
insert into table1 values ('b'),('b ');
select hex(col1) from table1;

mysql 5.0.19-debug (root) [test]> create table table1 (col1 binary(4));
Query OK, 0 rows affected (0.00 sec)

mysql 5.0.19-debug (root) [test]> insert into table1 values ('a'),('a ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0.19-debug (root) [test]> select hex(col1) from table1;
+-----------+
| hex(col1) |
+-----------+
| 61000000  |
| 61200000  |
+-----------+
2 rows in set (0.00 sec)

mysql 5.0.19-debug (root) [test]> alter table table1 modify col1 binary(10);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0.19-debug (root) [test]> select hex(col1) from table1;
+----------------------+
| hex(col1)            |
+----------------------+
| 61000000202020202020 |
| 61200000202020202020 |
+----------------------+
2 rows in set (0.00 sec)

mysql 5.0.19-debug (root) [test]> insert into table1 values ('b'),('b ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0.19-debug (root) [test]> select hex(col1) from table1;
+----------------------+
| hex(col1)            |
+----------------------+
| 61000000202020202020 |
| 61200000202020202020 |
| 62000000000000000000 |
| 62200000000000000000 |
+----------------------+
4 rows in set (0.00 sec)

Suggested fix:
The server should use 0x00 instead of 0x20 to pad BINARY columns when they are altered to a larger size.
[27 Jan 2006 22:46] Jorge del Conde
Verified w/fresh pull of 5.0 under FC4
[2 Mar 2006 19:21] Chad MILLER
Leads to data corruption.
[3 Mar 2006 1:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3410
[3 Mar 2006 1:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3411
[3 Mar 2006 2:10] Chad MILLER
BINARY columns should not be treated as other *CHAR columns when it comes to expanding columns.  Changeset 1.2096, in v5.0.19 changes the behavior to fill new positions in expanded BINARY columns with 0x00 bytes instead of ASCII spaces.
[4 Mar 2006 0:58] Paul Dubois
Noted in 5.0.19 changelog.

Using <literal>ALTER TABLE</literal> to increase the length of
a <literal>BINARY(<replaceable>M</replaceable>)</literal>
column caused column values to be padded with spaces rather
than <literal>0x00</literal> bytes. (Bug #16857)
[9 Apr 2006 4:48] Paul Dubois
Noted in 5.1.8 changelog, too.