2

I just upgraded the MySQL that I am using to MySQL version 5.6.14. When I issued this query,

SELECT FROM_BASE64(TO_BASE64('MySQL'));

I received 4d7953514c (hex value) as the answer instead of 'MySQL'. What is actually the problem? Is there anything that I have to do to unhex it?

NOTE: The UNHEX function in my MySQL also returns the same thing. If a hex value is given to UNHEX function, I will receive the same hex value again.

TQ in advance

Fikri Fadzil
  • 139
  • 1
  • 7
  • 1
    I was trying to replicate that on sqlfiddle, but couldn't. See [this fiddle](http://sqlfiddle.com/#!9/d41d8/354). Don't know if it is some kind of bug in 5.6.14. – Filipe Silva Sep 30 '13 at 17:49
  • 1
    @FilipeSilva - that's the problem. I tried on XAMPP. It works as it should. But on one of my servers, this happen without a clue. Is there any misconfig ot anywhere that might cause this problem? – Fikri Fadzil Sep 30 '13 at 18:45
  • 1
    I managed to found the root cause. This problem was actually due to PhpMyAdmin and not MySQL. – Fikri Fadzil Oct 02 '13 at 04:28

1 Answers1

1

MySQL client displays binary data using hexadecimal notation by default. The result of TO_BASE64 is a VAR_STRING with binary collation and, given that the original collation/character set has been already lost, FROM_BASE64 has no way of guessing what character set was used when producing those Bytes. As such, this particular client application (others may do it differently) chooses the safer route and just display their hexadecimal notation.

You can change this behavior by providing the option --skip-binary-as-hex when starting the mysql client. Just mind that if you don't know the source of the data this yield unexpected results. See the documentation for --binary-as-hex for additional ways of how to workaround this.

I'd also recommend turning on display result set metadata as it allows you to better understand what is causing this behavior.

FilipeSilva
  • 356
  • 3
  • 9