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?

Answer 1

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.

Answer 2

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

Answer 3

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)