Incompatible character encodings: UTF-8 and ASCII-8BIT

addis_a · December 30, 2014, 12:53pm

Hello you all, hope you’re all having some nice holidays!

Here is the deal with me: incompatible character encodings: UTF-8 and
ASCII-8BIT
I used a seed (GitHub - celsodantas/br_populate: Arquivo seed contendo todas as cidades e estados do Brasil.) to populate
my
DB with cities and states of my country, so I can fill a select box for
registering, but when I try to exhibit it I get this error:

incompatible character encodings: UTF-8 and ASCII-8BIT

this is one of my select_boxes:
<%= f.collection_select(:birth_state, State.all, :id, :name, {:prompt =>
“State”}, {:class=> ‘form-control’}) %>

And also I’m using a mysql database, with the mysql gem, and I have seen
some records like:
<City id: 5560, name: “Xambio*\xC3\xA1*”, capital: false, state_id:
27,…>

Making me think it’s a problem cause of special portuguese characters
like
ç, á, à, ã… I have heard also that mysql/rails doesn’t convert
automatically the encodings or something like that, but honestly I have
no
clue on how doing that. If anyone got a clue, happy to hear it.

Thank you all in advance!
Happy holidays,

Diego Dillenburg Bueno
Graduando em Ciências da Computação
UNESP - Rio Claro
(12) 98116-7741
https://www.facebook.com/diegodillenburg
http://br.linkedin.com/in/diegodillenburg
https://github.com/diegodillenburg

Diego_Dillenburg_Bu · December 30, 2014, 6:23pm

On Tue, Dec 30, 2014 at 3:50 AM, Diego Dillenburg Bueno
[email protected] wrote:

And also I’m using a mysql database, with the mysql gem, and I have seen some
records like:
<City id: 5560, name: “Xambio\xC3\xA1”, capital: false, state_id: 27,…>

You need to make sure your database (or at least the tables you’re
using for your app) is set up with the appropriate “character set” and
“collation” for the language(s) you’re using.

google: mysql collation portugues brasil

for some specific references; also read the MySQL docs for the DB
version you’re running, e.g.

http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

HTH,

Hassan S. ------------------------ [email protected]

twitter: @hassan

Diego_Dillenburg_Bu · January 6, 2015, 1:37pm

Hello,

sorry for the really late reply, had some internet troubles where I was.

I have researched about the collation, but I see that my database is ok,
as
when I input records manually it saves without any problems special
characters, what I’m thinking that is happening is that the data I’m
populating comes from a JSON request, and it’s coming encoded in
ASCII-8BIT
I guess. The trouble I’m having is actually on how to convert the
strings
before I save them on database. The code for the rake can be checked at

github.com

celsodantas/br_populate/blob/master/br_populate.rb

require 'net/http'
require 'net/https' # for ruby 1.8.7
require 'json'

module BRPopulate
  def self.states
    http = Net::HTTP.new('raw.githubusercontent.com', 443); http.use_ssl = true
    JSON.parse http.get('/celsodantas/br_populate/master/states.json').body
  end

  def self.capital?(city, state)
    city["name"] == state["capital"]
  end

  def self.populate
    states.each do |state|
      region_obj = Region.find_or_create_by(:name => state["region"])
      state_obj = State.new(:acronym => state["acronym"], :name => state["name"], :region => region_obj)
      state_obj.save

This file has been truncated. show original

I believe it has something to do with it.

Thanks for the help

Diego Dillenburg Bueno
Graduando em Ciências da Computação
UNESP - Rio Claro
(12) 98116-7741
https://www.facebook.com/diegodillenburg
http://br.linkedin.com/in/diegodillenburg
https://github.com/diegodillenburg

2014-12-30 15:22 GMT-02:00 Hassan S.
[email protected]:

Diego_Dillenburg_Bu · January 6, 2015, 5:30pm

On Tue, Jan 6, 2015 at 4:36 AM, Diego Dillenburg Bueno
[email protected] wrote:

I have researched about the collation, but I see that my database is ok, as
when I input records manually

Meaningless. The MySQL command line client is not the same
as the driver “client” used by your Rails app.

I repeat:

You need to make sure your database (or at least the tables you’re
using for your app) is set up with the appropriate “character set” and
“collation” for the language(s) you’re using.

Until you confirm that all the encoding-related variables used by
your DB (server and client) are correct, you’re wasting your time.

… what I’m thinking that is happening is that the data I’m
populating comes from a JSON request, and it’s coming encoded in ASCII-8BIT
I guess.

“I guess”? Why not put some debugging/logging statements in your
code so you can say “I know”? Just a suggestion

–
Hassan S. ------------------------ [email protected]

twitter: @hassan

Diego_Dillenburg_Bu · January 8, 2015, 3:08am

Hi Diego,

There’s definitely an encoding mismatch going on, and my guess would
also be that it has to do with your db (and likely your mysql server
settings as well).

(Note that the data in your …/states.json looks good to me, in terms
of being valid utf8. The problem is likely how that data is being
stored in the db, such that when pulled out and used in the rails app
again, it results in that encoding error.)

Try the following in your rails console and see what you get:

$ rails console
Loading development environment …

what’s the encoding for the env’s db conn

per ./config/database.yml?:

… :001 > ActiveRecord::Base.configurations[Rails.env][“encoding”]
=> “utf8”

how was this db created (as

recorded in mysql)?:

… :002 > ActiveRecord::Base.connection.exec_query(“show create
database #{ActiveRecord::Base.connection.current_database}”)
=> #<ActiveRecord::Result:…, @rows=[[“foo_dev”, “CREATE DATABASE
foo_dev /*… DEFAULT CHARACTER SET utf8 */”]], …>

what are the encoding settings

for mysql server?:

… :003 > ActiveRecord::Base.connection.exec_query(“show variables like
‘char%’”)
=> #<ActiveRecord::Result:… @rows=[[“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”], …>

My guess is that one (or more) of the results above for you will show
latin1 instead of utf8.

If that’s the case, and there’s an issue with the specific db, and you
can afford to blow it away and start over, then you should drop that db
and create it again specifying utf8, something like:

…
mysql> create database foo_dev character set utf8 collate
utf8_general_ci;
…

Also, if this is the case, and there’s an issue with the mysql server
settings, then you’ll probably want to mod your mysql server’s config
defaults to use utf8 (and then restart mysql), something like:

$ cat /etc/mysql/my.cnf
…
[client]
…
default-character-set = utf8

…
[mysqld]
…
collation-server = utf8_unicode_ci
init-connect = ‘SET NAMES utf8’
character-set-server = utf8

…
[mysql]
…
default-character-set = utf8

…

Hope that helps,

Jeff

Diego_Dillenburg_Bu · January 13, 2015, 5:40pm

“I guess”? Why not put some debugging/logging statements in your

code so you can say “I know”? Just a suggestion

Yeah, sorry for all the ‘guessing’, but I’m still on the start of the
road
around here Indeed gotta improve my debugging skills, thanks for the
suggestion.

what’s the encoding for the env’s db conn

for mysql server?:

… :003 > ActiveRecord::Base.connection.exec_query(“show variables like
‘char%’”)
=> #<ActiveRecord::Result:… @rows=[[“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”], …>

In fact the problem had to do something with the database, my collation
was “utf8_unicode_ci”, now changing it to utf8_general_ci everything
works
fine. Thanks!

Thank you both for the replies, all the help was much appreciated!

Regards,

Diego Dillenburg Bueno
Graduando em Ciências da Computação
UNESP - Rio Claro
(12) 98116-7741
https://www.facebook.com/diegodillenburg
http://br.linkedin.com/in/diegodillenburg
https://github.com/diegodillenburg

2015-01-08 0:07 GMT-02:00 Jeff B. [email protected]: