UTF8 MySQL problems on Rails - encoding issues with utf8_general_ci

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

encoding: utf8
collation: utf8_general_ci

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.

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

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

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

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/