MySQL does not work fine with UTF-8

Question

I added this rows to my-default file, restarted server, recreated schema, table and data in the tables, but my cyrillic data still looks like '????'. Now I have a new user and such config, but utf8 still does not work.

[mysqld]
init-connect=SET NAMES utf8
character-set-server=utf8
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysql]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8
#end

Please see my answer [here](http://stackoverflow.com/questions/28790757/cannot-display-trademark-symbol-in-mysql-to-html). If it helps let me know and I will post an answer. — EternalHour, Mar 22 '15 at 21:45
my-default is just a sample. my.cnf (or my.ini) is the file that is used unless you modify the arguments to `mysqld`. — Rick James, Mar 22 '15 at 22:01
Rick, thank you!! But it still does not work. I can not believe. — Arthur Kharkivskiy, Mar 22 '15 at 22:35

score 1 · Accepted Answer · answered Mar 24 '15 at 11:28

1

I replaced my.cnf by the default my.cnf, added

CHARACTER SET utf8 COLLATE utf8_general_ci;

for the DB and all tables.

Now it is works fine! Thank you very much!

answered Mar 24 '15 at 11:28

0xFF

585
1
6
22

score 0 · Answer 2 · edited Mar 22 '15 at 21:23

0

This will probably do the trick. You need to perform a small query directly before you do INSERT or UPDATE. In my db-class it looks like this, which is to be adapted to your needs. $charset needs to be "utf8"

$db->query('SET CHARACTER SET "' . $charset . '";');
$result = $db->query($query);

I call the function like this

$result = $ddlab->db->db($sql,'utf8');

Good luck !

edited Mar 22 '15 at 21:23

Einar Sundgren

4,325
9
40
59

answered Mar 22 '15 at 11:34

ddlab

918
13
28

Thank you very much for the answer! But sorry for stupid question: what I need to do from mysql console? Cause you queries looks like not pure mySQL. – Arthur Kharkivskiy Mar 22 '15 at 13:00

Rick James · Answer 3 · 2015-03-23T21:01:16.483

0

init-connect=SET NAMES utf8

That command is important. However, when you connect as root (or other SUPER user), that command is skipped.

Also, you need to make sure that the bytes in your client are utf8.

And the display is utf8.

If you are using the mysql commandline tool: The command "chcp" controls the "code page". chcp 65001 provides utf8, but it needs a special charset installed, too. To set the font in the console window: Right-click on the title of the window → Properties → Font → pick Lucida Console "

More discussion.

Edit 2

Go into Workbench (which you used for INSERTing the text?) and do SELECT HEX('Ремонтные'); This should give a clue of the character set in Workbench. utf8 will say D0A0D0B5D0BCD0BED0BDD182D0BDD18BD0B5 (note the Dxyy pattern); cp866 will say 90A5ACAEADE2ADEBA5 (note: high bit on). Aha! And latin1 will say 3F3F3F3F3F3F3F3F3F. Look familiar????? Hint: I am pointing finger at Workbench, or your use of it.

edited Mar 23 '15 at 21:01

answered Mar 22 '15 at 20:48

Rick James

135,179
13
127
222

Let's verify what is in the table. `Please do SELECT HEX(column) ...` to display some field that is coming out "????". Perhaps the data was mangled as it was _stored_, rather than as it was _read_. – Rick James Mar 23 '15 at 17:43
'3F3F3F3F3F3F3F3F3F203F3F3F3F3F3F' – Arthur Kharkivskiy Mar 23 '15 at 18:21
Bad news. "3F" is "?". The `INSERT`s did not have `SET NAMES` set correctly (or some other problem). Please edit your Question to lay out the details of the INSERT -- including my.cnf settings, connection, SET NAMES (if used), source of data (need to get a feel for what byte encoding was used), etc. – Rick James Mar 23 '15 at 18:25
Also, please provide (in the Edit), `SHOW CREATE TABLE` for the table in question. – Rick James Mar 23 '15 at 18:25
http://pastebin.com/rn5wrCxS - config. http://pastebin.com/msuZ4HsV table, etc. Best wishes to California! You are great man! https://www.facebook.com/arthur.kharkivskiy I'll glad you to be my friend (not for SQL questions:-)) – Arthur Kharkivskiy Mar 23 '15 at 19:01
OK, nothing obviously wrong in CREATE TABLE or my.cnf. Let's see the insert steps. (Yes, I'm in California -- Silicon Valley.) Any change you are using cp1251 or cp866 or 8859-5? Those have (I think) Cyrillic characters; but they are different than utf8. – Rick James Mar 23 '15 at 19:17
proc. body + insert http://pastebin.com/ac5rd0r2 I am not using cp1251 or cp866 or 8859-5, but not sure cause of Workbench. – Arthur Kharkivskiy Mar 23 '15 at 19:25
Do `SHOW CREATE PROCEDURE insertCategory;` -- that will show the character set and the "security invoker/definer" that are associated with the procedure. – Rick James Mar 23 '15 at 20:52
'insertCategory', 'NO_ENGINE_SUBSTITUTION', 'CREATE DEFINER=`root`@`localhost` PROCEDURE `insertCategory`(IN request_categoryParentId INT,\n IN request_category VARCHAR(128),\n IN request_categoryPicture VARCHAR(512))\nBEGIN\n INSERT INTO Category (category, categoryParentId, categoryPicture)\n VALUES (request_category, request_categoryParentId, request_categoryPicture);\nEND', 'utf8', 'utf8_general_ci', 'latin1_swedish_ci'. 'latin1_swedish_ci' - hm... – Arthur Kharkivskiy Mar 23 '15 at 23:34
What happens if you do the INSERT inside Workbench? That is, avoid the PROCEDURE. (I don't yet see what caused the problem.) – Rick James Mar 23 '15 at 23:56
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/73655/discussion-between-arthur-kharkivskiy-and-rick-james). – Arthur Kharkivskiy Mar 24 '15 at 10:45

MySQL does not work fine with UTF-8

3 Answers3