Bug #16859 mysql client improperly handles NULL-terminated strings
Submitted: 27 Jan 2006 22:28 Modified: 4 Mar 2006 0:50
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.19 OS:Linux (Linux)
Assigned to: Chad MILLER

[27 Jan 2006 22:28] Kolbe Kegel
Description:
BINARY columns are now padded with 0x00, and the mysql command line client doesn't seem to be able to handle this in a sane way.

How to repeat:
create table table1 (col1 binary(4));
insert into table1 values ('a'),('a ');
select concat('>',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 concat('>',col1,'<') from table1;
+----------------------+
| concat('>',col1,'<') |
+----------------------+
| >a                   |
| >a                   |
+----------------------+
2 rows in set (0.00 sec)

Suggested fix:
Note that the final '<' is never shown.

I am not sure what would be the most appropriate behavior. The server should either display 0x00 as 0x20 like this:

mysql 5.0.19-debug (root) [test]> select concat('>',col1,'<') from table1;
+----------------------+
| concat('>',col1,'<') |
+----------------------+
| >a    <              |
| >a    <              |
+----------------------+
2 rows in set (0.00 sec)

Or the server could truncate final 0x00 bytes, like this:

mysql 5.0.19-debug (root) [test]> select concat('>',col1,'<') from table1;
+----------------------+
| concat('>',col1,'<') |
+----------------------+
| >a<                  |
| >a <                 |
+----------------------+
2 rows in set (0.00 sec)

The latter of those two, truncation of 0x00, is more in-line with what was done in previous versions of the server, except that the padding character was 0x20, so that was truncated from the resultset as well, yielding this:

mysql 4.1.16-max (root) "test"> select concat('>',col1,'<') from table1;
+----------------------+
| concat('>',col1,'<') |
+----------------------+
| >a<                  |
| >a<                  |
+----------------------+
2 rows in set (0.00 sec)

In any case, never has the client shown the full padding added to the end of this type of column (though it's worth noting that the server didn't actually used to send the padding at all, so the client has never actually been responsible for making any decisions such as this). 

It might be most appropriate to translate 0x00 to 0x20 in the client's display, as this would more appropriately illustrate the size of the column. This could be significant if a user wants to use a technique such as the concat('>',col1,'<') used above to visually illustrate the size of a column. When a table is altered from BINARY(4) to BLOB, for example, the 0x00 padding is retained, so not being able to see this might be misleading.
[27 Jan 2006 22:47] Jorge del Conde
Verified under FC4 with a fresh pull of 5.0.  Tested under XP/5.0.18 too with same results
[2 Mar 2006 19:21] Chad MILLER
Leads to data corruption.  Ouch.
[3 Mar 2006 18:56] 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/3443
[3 Mar 2006 19:16] 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/3444
[3 Mar 2006 20:07] 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/3447
[3 Mar 2006 22:30] 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/3452
[3 Mar 2006 22:41] Chad MILLER
This patch results in columns that contain NUL characters (as opposed to "terminating" with them, which is a programming idiom and has nothing to do with DB data) displaying correctly in the 'mysql' client program.

NUL characters are replaced with ASCII spaces, so that the table layout is preserved.
[3 Mar 2006 23:29] Trudy Pelzer
Fixed in 5.0.19.
[4 Mar 2006 0:50] Paul Dubois
Noted in 5.0.19 changelog.

<command>mysql</command> no longer terminates data value
display when it encounters a NUL byte. Instead, it displays
NUL bytes as spaces. (Bug #16859)