#3150 (var)char displayed as binary

3.3.7
fixed
5
2013-06-11
2010-12-01
Kai Schätzl
No

I've just started to work with PHPMyAdmin 3 and stumbled over a change in display that is wrong in my eyes. It is probably related to this patch that asked for display of binary fields in binary by default:
https://sourceforge.net/tracker/index.php?func=detail&aid=2308632&group_id=23067&atid=377410
However, it seems this default setting is also applied to (var)char fields that have some sort of binary collation. This produces very inconsistent (and in my eyes wrong) results.
Example:
I have the following three fields:

LangID type binary(2)
ar type varchar(40) collation utf8_bin
de type varchar(20) collation utf8_bin

The content in these fields is displayed as follows.

table display:
LangID de
ar d8a3d984d985d8a7d986d98a
de Deutsch

edit record display:
LangID UNHEX(6465)
ar "content in Arabic"
de Deutsch

So, LangID is displayed non-binary although it is a binary field. However, when editing it suddenly is displayed in binary. (And I don't understand why this would have to be unhexed when submitting to the db.)
On the other hand, of the varchar fields one is displayed in binary, the other isn't. But when editing both are displayed with the string content.

1. Whatever "display mode" may seem to be correct, a mix is surely not correct.
2. I think the varchar binary fields should not be displayed in binary by default. According to http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html: The BINARY and VARBINARY data types are distinct from the CHAR BINARY and VARCHAR BINARY data types. For the latter types, the BINARY attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary collation for the column character set to be used, and the column itself contains nonbinary character strings rather than binary byte strings.

As a workaround I have set $cfg['DisplayBinaryAsHex'] to FALSE. This seems to give the desired results for both display and edit. But I think this should get corrected as the current behavior is very unexpected for the binary collation fields. I'm neither convinced that the binary display of binary fields by default is a good idea as this is a change to the version 2 default. Deducing from the patch mentioned above only a single person asked for that since the time this setting got introduced (the changelog doesn't mention this variable, so I couldn't check when it got introduced).

Discussion

1 2 > >> (Page 1 of 2)
  • Michal Čihař
    Michal Čihař
    2010-12-02

    • assigned_to: nobody --> nijel
    • status: open --> pending
     
  • Michal Čihař
    Michal Čihař
    2010-12-02

    I can not reproduce it neither on 3.3.7 nor on 3.4. It might be actually bug in your MySQL library, which reports this column as binary.

    Or are you able to reproduce the issue on our demo server? http://demo.phpmyadmin.net/trunk-config/

     
  • Kai Schätzl
    Kai Schätzl
    2010-12-02

    I agree. I tested it on your demo server and can't reproduce it. Our MySQL server version is 5.0.77 on CentOS5. There's still one inconsistency, though. The reported difference between the results display of the varbinary(2) field as "de" and the edit display of UNHEX(6465) is visible on your demo server as well. You can observe this in language.language_test (until it gets wiped).
    Thinking this over, there *must* be something going wrong in PHPMyAdmin as well, it can't just be the server. e.g. you would expect the results display and the edit display look the same. It may be advertised wrongly as binary, but if so, shouldn't it look the same both in results and edit display and also the same for two varchar fields? However, it is different in both modes and for two varchar fields with the same collation. It looks like PHPMyAdmin uses the (wrong) "is binary" information in one display mode, but not in the other and for one field, but not the other. It looks like all the fields with double-byte character languages are affected (= shown in binary). Is there anything I can check to clarify if it is our MySQL or the client library or the server or some configuration setting? When I fetch results with my own code and display in utf-8 encoded pages I get it all displayed fine and not in binary.
    I just found one difference between my original table and the test table I created on your demo server. The global collation for the table here is utf8_bin while it was utf8_general_ci for the table on your server. I changed that on your server and it made no difference for the display, though.

     
  • Kai Schätzl
    Kai Schätzl
    2010-12-02

    • status: pending --> open
     
  • Marc Delisle
    Marc Delisle
    2010-12-02

    Are you using mysqli extension? This is the one recommended for servers running MySQL 4.1 and above.

     
  • Kai Schätzl
    Kai Schätzl
    2010-12-02

    Bingo! I checked config.inc.php and found it uses 'mysql'. I changed to 'mysqli' and changed $cfg['DisplayBinaryAsHex'] back to TRUE. Voila, I get now the same results as on your demo server. Including the difference between results and edit page for the varbinary(2) field. That seems to happen because UNHEX is shown by default for binary fields in edit mode. That probably makes sense in general. In my case it's a nuisance, so I'll change $cfg['DisplayBinaryAsHex'] back to FALSE.
    I think you can close this as "works for me". Thanks for your time!

     
  • Michal Čihař
    Michal Čihař
    2010-12-02

    • status: open --> pending
     
  • Michal Čihař
    Michal Čihař
    2010-12-02

    Yes, that's expected behavior for binary field.

     
  • This Tracker item was closed automatically by the system. It was
    previously set to a Pending status, and the original submitter
    did not respond within 14 days (the time period specified by
    the administrator of this Tracker).

     
    • status: pending --> closed
     
1 2 > >> (Page 1 of 2)