Re: UTF-8, MySQL (MariaDB) and the head-shaped dent in my desk

On 13 Feb 2014, at 08:01, Ian Evans <dheianevans@gmail.com> wrote: > I recently moved to a new server and took the move time as a chance to > migrate my database from Latin1 to UTF-8. > > With my site's data forms I'm able to enter accented characters just fine > and the when PHP grabs the info from the DB the accents display just fine. > Sometimes for quick little changes, it's easier to go into a program like > Adminer or phpmyadmin. > > I've already posted questions about this at stackoverlflow ( > > http://stackoverflow.com/questions/21439798/utf-8-input-problems-with-adminer-and-phpmyadmin) > and the adminer forum ( > > http://sourceforge.net/p/adminer/discussion/960418/thread/33595373/) > but so > far no one's been able to crack this particular nut. > > With the UTF-8 in place, I wanted to change director Alfonso Cuaron's name > to Cuarón. I went to his entry in Adminer. Edit. Cuar[alt+0243]n. It showed > in the edit box as Cuarón. But when I saved the change, Adminer showed it > as Cuarón. Okay. Looked at page info in Firefox. Says the character > encoding of the page is UTF-8. So all should be well, right? > > I went to one of my php data entry forms and created a Bob Cuarón. It > showed up fine. I SSH'd into the server fired up a mysql command line and > ran an update sql line with Cuarón. That worked. But trying to change it in > Adminer still kept giving me Cuarón. I installed phpmyadmin (which was > giving me some issues with my nginx config) but I was able to edit his name > and...sigh...it too gave me Cuarón. I installed SQLbuddy and...success...I > was able to make the changes, but the program is lacking some of the things > I need, like the ability to edit search results. > > I'm sure I've nailed everything down: > > nginx.conf: > > charset UTF-8; > > my.cnf: > > [client] > default-character-set=utf8 > [mysqld] > character-set-server=utf8 > collation-server=utf8_general_ci > init-connect='SET NAMES utf8' > > /etc/php5/fpm/php.ini > > mbstring.language = Neutral > mbstring.internal_encoding = UTF-8 > mbstring.encoding_translation = On > mbstring.http_input = auto > mbstring.http_output = UTF-8 > mbstring.detect_order = auto > mbstring.substitute_character = none > default_charset = UTF-8 > > Iconv support is enabled. the implentation is glibc, the library version is > 2.17. and the iconv encodings are set to UTF-8. > > > SHOW VARIABLES LIKE "%character_set%"; > > +--------------------------+----------------------------+ > | 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/ | > > I can't see what I could be missing. Both Adminer and phpmyadmin handle > UTF-8 so I don't know why it's not working. It worked right out of the box > with SQLBuddy, but as I said it's missing some features. > > Any thoughts where I should look? I feel like I'm an edge case as the > adminer devs can't duplicate the issue, Are you sure the source files itself are saved in UTF-8? Also check that set_names (in PHP) is not overriding character_set_client and that you don't have a meta tag that overrides the header tags. Apart from that, I can only tell you that the problem you are having is that you try to print an UTF-8 character in a ISO-8859-1 encoding. Just a silly question: you did remember to restart the db and nginx after making the changes in your confs right? Greetings.