Menu
▾
▴
#3006 (ok 3.3.3) non binary fields shown as hex
PMA v3.3.0
MySQL Server 5.0.51a-24+lenny3-log
Apache 2.2.14-7
PHP v5.2.6-1+lenny8
PHP Extension : mysqli
The following query executed using the "SQL" tab always returns data in HEX :
SELECT INET_NTOA(2130706433)
=> 3132372e302e302e31
Where, of course, this is nothing more than plain ascii "127.0.0.1"
Possibly due to this change : https://sourceforge.net/tracker/index.php?func=detail&aid=2308632&group_id=23067&atid=377410
Indeed, MySQL reports this to be a binary result. By default the DisplayBinaryAsHex is set to true, giving the result you are seeing. Not sure what is the best default for this setting.
Also, normally you could click Options and untick "Show binary contents as HEX", but there is a problem with this slider (it's fixed in version 3.4-dev, however).
Yes, the "show binary contents as hex" unticked does the trick. But it's re-ticked again at the next query. I should probably set DisplayBinaryAsHex to False in my config, as I'm not using binary data very much...
The fact that MySQL reports this result to be binary is strange : the output of inet_ntoa should always be [0-9.]+, or NULL... :(
Now, you could implement some logic in PMA to try to detect when binary output is in fact plain-ascii, as this is the case here, and force it to be displayed as ascii. But I suppose this would add some overhead, and is maybe not a very good idea...
There are many situations where MySQL reports a field in results as being binary. It would be much job (and overhead) to try to outguess MySQL about the field type. This bug will probably be closed as "won't fix".
Well, I guess I'll just change my config then.
I suppose a lot of people will be hit by this "bug", it took me some minutes to get it sorted (I tried to play with MySQL's unhex(), and it didn't do what I expected, of course, because in the end it was just a PMA choice to display it as hex).
Maybe a default value of False for DisplayBinaryAsHex would be a good idea ?
Maybe. It's difficult to evaluate which default will make users complain less.
Indeed. Well, time (and this bugtracker) will tell !
While there is probably no "good" defaut value for this parameter, it would probably help when PMA decides to display some column values in hex, to put a little notice at the top of the results page ? So that people know what they're looking at... so that they know that they can toggle between hex/not hex (using the options tab).
Good idea about displaying a notice (maybe in the field's header).
Meanwhile I asked Olaf to come in this artifact and make comments, as he often uses binary fields.
> It would be much job (and overhead) to try to outguess MySQL about the field type.
Why? Checking whether the string is ASCII should be trivial.
I prefer to solve this properly though. Could you find that MySQL bug report?
Olaf,
I did not look for this MySQL bug report; anyway, even if MySQL would acknowledge this as a bug (which they did not IIRC) and would fix it, phpMyAdmin would still have to support the MySQL versions before the fix.
About checking ASCII: it's not that simple. Think about the possible charsets we have to support, even Chinese characters in UTF-8.
But I welcome a patch from you if you have a solution.
http://bugs.mysql.com/bug.php?id=52505
With ASCII I literally meant ASCII. So only bytes between 0x20 and 0x80. This doesn't work in cases where 'real' UTF-8 chars are used.
The MySQL folks closed the bug as NOTABUG, while still agreeing this _is_ a bug. Which really sucks.
Back to PMA. Detecting plain-stupid-printable ascii should be easy, and it is possible to display it, whatever the charset is. Other cases (UTF-8, KOI8-R, etc.) are too complicated to detect, and it would not really be a detection, it would be a guess, which seems bad to me.
Here's what I suggest for returned data marked as "binary", and when DisplayBinaryAsHex is true:
1) If the bytes of the returned data are [0x20-0x7E], display the data as ascii
2) Else, display it as hex but show a little notice somewhere, either at the top of the page (as we sometimes see when using PMA, you probably have a dedicated and ready to use function to do just that), or as lem9 suggested, at the top of the concerned column, which might be more "user-friendly". So that users know what they're looking at, and know that they can easily toggle binary display on/off.
Implemented the verification for printable characters, please have a look at current QA_3_3 or master version. See http://phpmyadmin.git.sourceforge.net/git/gitweb.cgi?p=phpmyadmin/phpmyadmin;a=commitdiff;h=acdf39e95b355bc656409b66174afe6b7e0f39c6