1

This is how I connect to my database on my Windows cmd: mysql -u mydb -h myip -p.

It has always worked since I tried to swap my utf8 database to utf8mb4. The changes I apply with php or java work fine and I can see those characters correctly when loading from php, java or phpmyadmin but not on my cmd.

I have tried adding --default-character-set=utf8 and --default-character-set=utf8mb4 to the cmd connection with no succeed. It is curious, by the way, that if I run a simple sentence like SELECT '☺'; it shows that character correctly.

Am I missing something?

danibg
  • 214
  • 1
  • 15
  • Have you tried the chcp command? In the following thread they were setting cmd.exe to run unicode with this command. I would try to work with this to achieve utf8mb4 as well: http://stackoverflow.com/questions/14109024/how-to-make-unicode-charset-in-cmd-exe-by-default – Lajos Arpad May 31 '16 at 09:31
  • Just tried it and same thing happens. It shows a `?` where it should appear the `☺` – danibg May 31 '16 at 10:24
  • Can you tell me how did you try? – Lajos Arpad May 31 '16 at 10:28
  • `Windows + R` > `C:\Windows\System32\cmd.exe /K chcp 65001` and then, after the cmd opens, write: `cd c:\wamp\bin\mysql\mysql5.6.17\bin` and then `mysql -u mydb -h myip -p`. Also tried with `mysql --default-character-set=utf8 -u mydb -h myip -p` and `mysql --default-character-set=utf8mb4 -u mydb -h myip -p`. None of them worked. Thank you for your interest :) – danibg May 31 '16 at 10:36
  • With that you were defining the character encoding to UTF8, if I am not mistaken, however, you need UTF8MB4. You will need to find out the number needed after chcp to achieve that. I would send a question to Microsoft if I were you. – Lajos Arpad May 31 '16 at 10:46
  • 1
    https://bugs.mysql.com/bug.php?id=66682 Reading this, it looks like a bug from Mysql 5.5. Should probably update to 5.6 – danibg May 31 '16 at 11:02

1 Answers1

1

Based on the findings of danibg in the end of our brainstorming session, he has found this link which describes that this is a MySQL bug which was applicable to 5.5, used by the op and apparently this was fixed for MySQL 5.6+

We tested 5.6 version of client against 5.5 version of server, especially with respect to non ANSI characters. The 5.6 client worked as expected and the issue mentioned in this bug was also fixed.

As a result, the solution is as follows:

  • create a backup and store at least 2 copies
  • uninstall MySQL 5.5
  • install MySQL 5.6+
  • import the copy
  • do a lot of tests
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Mysql 6.0 does not exist at this time. But installing a 5.6+ version should do the trick. Thank you, you help me on way or another to find out what was wrong on that. – danibg May 31 '16 at 13:11
  • @danibg, you are perfectly right, sorry for the typo, edited my answer to fix it. – Lajos Arpad May 31 '16 at 13:29