0

I am trying to set character sets to utf8mb4 and collation sets to utf8mb4_unicode_ci. On my website the emoji's looks fine when I get the data out of the table with php/mysql select. Only in phpMyAdmin (4.8.4) I see most emoji's as one questionamrk.

I try this:

Add to /etc/my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Restart systemctl restart mariadb

I use on my website mysqli_set_charset($con, "utf8mb4");, <form accept-charset="UTF-8"> and <meta charset="utf-8">.

Result of SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%':

enter image description here

What am I doing wrong?

I use mysql version 5.5.60 (Mariadb).

Edit: The problem might be phpmyadmin (4.8.4). It seems that phpMyAdmin is still using utf8. The global variabelen are set to utf8mb4 or utf8mb4_unicode_ci but I can't change the session variabelen:

enter image description here

I set in config.inc.php: enter image description here

In the 'normal' table in phpMyAdmin: enter image description here

And when I try SET NAMES everything looks fine: enter image description here

Under General settings in phpMyAdmin the server connection collation is set on utf8_unicode_ci. I can select utf8mb4_unicode_ci, but then it switches back to utf8_unicode_ci :(

And on my website with select everything looks oké:

enter image description here

Marianne
  • 41
  • 6
  • I think you cannot force on the server the character set of the client/connection. You have to set it in the client application. – andreaplanet Feb 23 '19 at 01:26
  • @andreaplanet, oh no, you mean that PHPMyAdmin (version 4.8.4) can not display emoji's, because it's using utf8? :see_no_evil: I found out that I only see questionmark's in phpmyadmin and not on my website with mysql select. So this may be the problem. Thnks. – Marianne Feb 23 '19 at 12:39
  • in phpMyadmin main page > Variables tab (Server variables and settings) > search for "character". Try to change them there. – andreaplanet Feb 23 '19 at 13:15
  • I had already set everything to utf8mb4, but there are some session-variabele which i can not change. See new picture in the question. – Marianne Feb 23 '19 at 13:21
  • you may just need to restart the session. Reload phpmyadmin / restart the service – andreaplanet Feb 23 '19 at 13:24
  • what about setting it manually with SET NAMES 'utf8mb4' ? – andreaplanet Feb 23 '19 at 13:39
  • @andreaplanet: I have restart Mariadb and the server, but nothing is changed. And I already tried mysqli_query($con, "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"); and also mysqli_set_charset($con, "utf8mb4"); – Marianne Feb 23 '19 at 13:58
  • I meant SET NAMES 'utf8mb4' in phpmyadmin (just execute the query) and/or restart the phpmyadmin/web service, not mariadb. – andreaplanet Feb 23 '19 at 14:19
  • Oh, sorry, I now execute the query in phpMyAdmin and i closed the webservice. But nothing changed... – Marianne Feb 23 '19 at 14:29
  • 1
    If in phpmyadmin I execute "SET NAMES utf8mb4;SELECT _utf8mb4'' " as a single command then I get the emoji. If I execute "SET NAMES utf8;SELECT _utf8mb4'' " then I get a ?. So I don't understand how the set names command cannot have any effect on the session. And on my phpmyadmin installation I have as character set variables utf8 (not utf8mb4). One note, in phpmyadmin, clicking on home button (or on top server link), in general settings I have under "server connection collation" utf8mb4_unicode_ci. – andreaplanet Feb 23 '19 at 23:38
  • @andreaplanet: Sorry, you are absolutely right. I had tested it in another (wrong?) way. I indeed see emoji in this way. See image that I added to the question. I do not really specialize in this :) so I really appreciate your help. The "server connection collation" under the general settings says utf8_unicode_ci. I can select utf8mb4_unicode_ci, but then it switches back to utf8_unicode_ci :( – Marianne Feb 24 '19 at 08:28
  • Well after setting setting set names at each session you can at least work. It's strange that it doesn't store the server connection collation. Are you configuring the correct my.cnf file? What are the command line options of the mysql service? Check the config.inc.php in PHPMyAdmin. $cfg['DefaultCharset'] = 'utf8mb4'; $cfg['DefaultConnectionCollation'] = 'utf8mb4_general_ci'; I too am not specialized in this. – andreaplanet Feb 24 '19 at 13:09
  • @andreaplanet: It took some time to find out. There was no config.inc.php yet, so I created it in the folder usr/local/psa/admin/htdocs/domains/databases/phpMyAdmin. In this folder was also config.sample.inc.php stored. See the image I added to the question. But nothing change :( The file my.cnf was in the /etc/my.cnf folder. I can not find other files with the same name, so I thnk it is the right one? What do you mean with 'options of the mysql service'? – Marianne Feb 25 '19 at 14:21
  • How many question marks versus how many emoji? – Rick James Mar 06 '19 at 21:53

0 Answers0