phpmyadmin displaying weird chars (utf8)

Question

So I have set mysql for full utf8 support:

my.cnf:

[mysqld]
character_set_client=utf8
character-set-server=utf8
collation_server=utf8_general_ci

and

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like "coll%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Now, I correctly store and get (and json_encode) utf8 characters in my web app with my mysql queries but I have a problem with phpmyadmin. The problem is that phpmyadmin displays those international characters as garbled text.

έλεος

sadaa

just to clarify things out, I'm talking about NEW DATA, not already stored data which may be corrupted.

the session variables for phpmyadmin are:

show variables like "char%";
Variable_name   Value   
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    utf8
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

show variables like "coll%" 
Variable_name   Value   
collation_connection    utf8mb4_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci

The only way I have found till now for the international utf8 characters to display properly in phpmyadmin and not as garbled text is to run

SET NAMES utf8

every time before a client app (php) query which: a) is weird because as you can see everything is properly utf8 b) i would like to avoid running that query every time

is this a phpmyadmin only problem or a general data encoding problem with my settings? Either way, how can I solve this? Preferrably just with configuration files settings, not having to run the SET NAMES utf8 query each time.

thanks in advance.

Answer 1

Answering my own question.

It seems it was not a phpmyadmin problem but a general encoding problem.

The problem was I was not setting my client connection charset.

here's what I got if I displayed the variables from a php file:

{"Variable_name":"character_set_client","Value":"latin1"}
{"Variable_name":"character_set_connection","Value":"latin1"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"latin1"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"\/usr\/share\/mysql\/charsets\/"}



{"Variable_name":"collation_connection","Value":"latin1_swedish_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}

Initially i tried adding in my.cnf

[client]
default-character-set=utf8

IT DOES NOT WORK.

so the solution was to add:

charset=UTF8

in the PDO initialization

so after that I got the proper variable values:

{"Variable_name":"character_set_client","Value":"utf8"}
{"Variable_name":"character_set_connection","Value":"utf8"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"utf8"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"\/usr\/share\/mysql\/charsets\/"}



{"Variable_name":"collation_connection","Value":"utf8_general_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}