phpmyadmin shows numbers or blob for mysql's utf8_bin callation columns?

Question

I have a table with a varchar column. Its collation is set to utf8_bin. My software using this table and column works perfectly. But when I look at the content in phpmyadmin, I only see some hex values or [Blob xB]. Can I make phpmyadmin show the content correctly?

Besides, when I set the collation to utf8_general_ci or utf8_unicode_ci, the phpmyadmin shows the content correctly.

Thx Marc

[edit]Hah, I found out, there is a small "+Options" link above every table in phpmyadmin. It opens several options including "Show BLOB contents" - which makes the [blob] to readable text when enabled and "Show binary contents as HEX" which shows the hex codes as text when disabled.

No idea why there are two options though and why sometimes there is a [Blob] and sometimes hex values.

Well. Now I'm still wondering: Setting these options get lost when I go to another table. I have to set them every time I go there. Is there a way to save those options? [/edit]

Answer 1

• DO NOT EDIT THIS FILE, EDIT config.inc.php INSTEAD !!!

So, to do this in line with PMA author's correct documentation, edit config.inc.php. Add the line: $cfg['DisplayBinaryAsHex'] = false;

to your config.inc.php file.

;-)

Answer 2

I think the best solution is to change this line:

$cfg['Servers'][$i]['extension'] = 'mysql';

to this:

$cfg['Servers'][$i]['extension'] = 'mysqli';

If you have the mysqli extension available, use it. It is more secure, a bit more optimized, and it handles the BLOB type of utf-8 better by default. Your [BLOB] entries should start showing up as their values without having to add in any other special configuration options.

Answer 3

As a matter of fact, you can. But you would need to have access to the phpMyAdmin php files. If you have, go to /libraries/config.default.php

Then look for $cfg['DisplayBinaryAsHex'] = true; and change the value to "false". (It should be around line 888)

Cheers,

Robin