UTF8 MySQL problems on Rails - encoding issues with utf8_general_ci

Question

I have a staging Rails site up that's running on MySQL 5.0.32-Debian.

On this particular site, all of my tables are using utf8 / utf8_general_ci encoding.

Inside that database, I have some data that looks like so:

mysql> select * from currency_types limit 1,10;
+------+-----------------+---------+
| code | name            | symbol  |
+------+-----------------+---------+
| CAD  | Canadian Dollar | $       |
| CNY  | Chinese Yuan    | å…ƒ     |
| EUR  | Euro            | €     |
| GBP  | Pound           | £      |
| INR  | Indian Rupees   | ₨     |
| JPY  | Yen             | ¥      |
| MXN  | Mexican Peso    | $       |
| USD  | US Dollar       | $       |
| PHP  | Philippine Peso | ₱     |
| DKK  | Denmark Kroner  | kr      |
+------+-----------------+---------+

Here's the issue I'm having

On staging (with the db and Rails site running on the debian box), the characters for symbols are appearing correctly when displayed from Rails. For instance, the Chinese Yuan is appearing as 元 in my browser, not å…ƒ as it shows inside the database.

When I download that data to my local OS X development machine and run the db and Rails locally, I see the representation from inside the DB (å…ƒ) on my browser, not the character 元 as I see in staging.

Debugging I've done

I've ensured all headers for Content-Type are coming back as utf8 from each webserver (local, staging).

My local mysql server and the staging server are both setup to use utf8 as the default charset. I'm using "set names 'utf8'" before I make any calls.

I can even connect to my staging db from my OS X Rails host, and I still see the characters å…ƒ representing the yuan. I'm guessing then, perhaps there's an issue with my mysql local client, but I can't figure out what the issue is.

Perhaps this might lend a clue

To make it even more confusing, if I paste the character 元 into the db on my local machine, I see that in the web browser fine. --- YET if I paste that same character into my staging db, I get a ? mark in it's place on the page from my staging Rails site.

Also, locally on my OS X rails machine if I use "set names 'latin1'" before my queries, the characters all come back properly. I did have these tables set as latin1 before - could this be the issue?

Someone please help me out here, I'm going crazy trying to figure out what's wrong!

Answer 1

AHA! Seems I had some table information encoded in latin1 before, and stupidly changed the databases to utf8 without converting.

Running the following fixed that currency_types table:

mysqldump -u root -p --opt --default-character-set=latin1 --skip-set-charset  DBNAME > DBNAME.sql

mysql -u root -p --default-character-set=utf8  DBNAME < DBNAME.sql

Now I just have to ensure that the other content generated after the latin1 > utf8 switch isn't messed up by that :(

Answer 2

Do you have these two lines in your database.yml under the proper section?

encoding: utf8
collation: utf8_general_ci

Answer 3

1. The problem could have been with you MySQL client in staging it does not support UTF-8.
2. Your local OSX ruby installation configuration might not have declared the proper configs. You should have "encoding: utf8" in "config/database.yml" for the MySQL database. You should have "$KCODE = 'u'" in "config/environment.rb" for the ruby enviroment.

Answer 4

Another simple approach is to set the encode type by using SQL Alter statement. You can do this using the below bash script.

for t in $(mysql --user=root --password=admin  --database=DBNAME -e "show tables";);do echo "Altering" $t;mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";done

prettified

  for t in $(mysql --user=root --password=admin  --database=DBNAME -e "show tables";);
    do 
       echo "Altering" $t;
       mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
    done

Answer 5

My DB was already set by default to utf8, but I encountered the same problem.

Also after adding the following usual meta tag, the problem was still there:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Then I created a dedicated connection.php to ensure all communication with MySQL is set to charset utf8. Note that there is no - in utf8 in mysqli_set_charset($bd, 'utf8')!

Here is my Connection.php:

<?php
    $mysql_hostname = "localhost";
    $mysql_user = "username";
    $mysql_password = "password";
    $mysql_database = "dbname";
    $prefix = "";
    $bd = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
    mysqli_select_db($bd, $mysql_database) or die("Could not select database");
    if(!mysqli_set_charset($bd, 'utf8'))  {
        exit() ;
    }
?>

Another php file:

<?php
    //Include database connection details
    require_once('connection.php');

    //Enter code here...

    //Create query
    $qry = "SELECT * FROM subject";
    $result = mysqli_query($bd, $qry);
?>

//Other stuff

Answer 6

For Rails run the following code snippet into rails console. It will generate an sql for all tables. Then log in to mysql and execute copied sql from rails console. It will alter all tables encoding.

schema = File.open('db/schema.rb', 'r').read
rows = schema.split("\n")

table_name = nil
rows.each do |row|
  if row =~ /create_table/
     table_name = row.match(/create_table "(.+)"/)[1]
     puts "ALTER TABLE `#{table_name}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"
  end
end

Answer 7

You can generate a migration, the Rails way, to change the collation type on your databases:

rails generate migration ChangeDatabaseCollation

Then you can edit the generated file and paste:

def change
  # for each table that will store the new collation execute:
  execute "ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci"
end

And run the migration:

rake db:migrate

You can also enforce the new collation on your database.yml:

development:
  adapter: mysql2
  encoding: utf8
  collation: utf8_general_ci

For more information on Rails migrations:

http://edgeguides.rubyonrails.org/active_record_migrations.html

For more information on collation types:

http://collation-charts.org/