Rails show question marks(????) for my input utf8 data

Question

I have set every encoding set variable I can figure out to utf8.

In database.yml:

development: &development
  adapter: mysql2
  encoding: utf8

In my.cnf:

[client]
default-character-set = utf8

[mysqld]
default-character-set = utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

And if I run mysql client in terminal:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

But it's to beat the air. When I insert utf8 data from Rails app, it finally becomes ????????????.

What do I miss?

See http://stackoverflow.com/questions/29805029/stored-non-english-characters-got-mysql-character-set-issue/29810725#29810725 for a discussion of question marks and how to deal with them. — Rick James, Jul 11 '15 at 01:15

Ravbaker · Accepted Answer · 2012-06-02T11:18:47.247

Check not global settings but when you are connected to specific database for application. When you changed settings for mysql you have also change settings for your app database.

Simple way to check it is to log to mysql into app db:

mysql app_db_production -u db_user -p

or rails command:

rails dbconsole production

For my app it looks like this:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| 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 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Command for changing database collation and charset:

mysql> alter database app_db_production  CHARACTER SET utf8 COLLATE utf8_general_ci ;
Query OK, 1 row affected (0.00 sec)

And remeber to change charset and collation for all your tables:

ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci; # changes for new records
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; # migrates old records

Now it should work.

Might want to use utf_unicode_ci instead of utf_general_ci. http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci — mahemoff, Apr 01 '13 at 06:23
I've created a GIST that I used to generate the SQL to migrate my rails app that had this issue - although small, it is beautifully formed: https://gist.github.com/richhollis/7153490 — RidingTheRails, Oct 25 '13 at 11:54

score 3 · Answer 2 · answered Apr 28 '14 at 04:54

3

I had the same problem. I added characterEncoding to the end of mysql connection string:

use this: jdbc:mysql://localhost/dbname?characterEncoding=utf8
instead of this: jdbc:mysql://localhost/dbname

answered Apr 28 '14 at 04:54

elahehab

325
3
16

score 1 · Answer 3 · answered Apr 10 '13 at 08:51

Okay for anybody else for whom the @Ravbaker answer does not cut it .. some more tips

MySQL has encoding specified in multiple levels : server, database, connection, table and even field/column. My problem was that the field/column was forced to latin (which over rides all the other encodings). I set the field back to the table encoding (which was utf-8) and the world was good again.

Most of these settings can be set at the usual places: my.cnf, alter queries and rails database.yml file.

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8;

was the query which did the trick for me.

For server / connection encodings use my.cnf and database.yml For database / table / column encodings use queries

(You can also achieve these by other means)

Be cautious... `ALTER ... MODIFY` and `ALTER ... CONVERT TO` do different things. If the only text you have is ascii, either will do. If you currently have correct latin1 bytes in the column, you need `CONVERT TO` to get the bytes changed. — Rick James, Jul 11 '15 at 01:21

score 0 · Answer 4 · answered Aug 29 '12 at 21:54

0

I have some problem today! It's solved by drop my table and creating new, then db:migrate and all is pretty works!
WARNING: IT WILL DELETE ALL YOUR DATA IN THIS TABLE
So:
$ mysql -u USER -p mysql > drop database YOURDB_NAME_development; mysql > create database YOURDB_NAME_development CHARACTER SET utf8 COLLATE utf8_general_ci; mysql > \q $ rake db:migrate Well done!

answered Aug 29 '12 at 21:54

Extazystas

488
2
11

TABLE inherits from DATABASE. COLUMN inherits from TABLE. So, if you have the luxury of dropping the database, this code is simple and effective. – Rick James Jul 11 '15 at 01:22

score 0 · Answer 5 · edited Jun 02 '12 at 15:20

0

Do you have this in the HTML?

<meta http-equiv="content-type" content="text/html;charset=UTF-8" />

or on HTML5 pages with <!doctype html>

<meta charset="utf-8">

You may need this to let the browser send strings in utf8.

edited Jun 02 '12 at 15:20

Ravbaker

1,780
1
13
11

answered Feb 28 '12 at 12:30

Yanhao

5,264
1
22
15

Probably good to have anyway, but keep in mind it won't help you much if the data is stored as "????" in the database already. – Adam Grant May 27 '16 at 00:05

Rails show question marks(????) for my input utf8 data

5 Answers5