search

phpmyadmin / phpmyadmin

A web interface for MySQL and MariaDB
https://www.phpmyadmin.net/
GNU General Public License v2.0
7.28k stars 3.42k forks source link

(var)char displayed as binary #9539

Closed pma-import closed 9 years ago

pma-import commented 14 years ago

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).

pma-import commented 14 years ago

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/

pma-import commented 14 years ago
pma-import commented 14 years ago

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.

pma-import commented 14 years ago
pma-import commented 14 years ago

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

pma-import commented 14 years ago

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!

pma-import commented 14 years ago

Yes, that's expected behavior for binary field.

pma-import commented 14 years ago
pma-import commented 13 years ago

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).

pma-import commented 13 years ago
pma-import commented 11 years ago