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

rubyonrails-talk
1

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

Here is the deal with me: incompatible character encodings: UTF-8 and ASCII-8BIT

I used a seed (https://github.com/celsodantas/br_populate) 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,

2

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,

3

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 https://github.com/celsodantas/br_populate/blob/master/br_populate.rb

I believe it has something to do with it.

Thanks for the help

4

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 :slight_smile:

5 

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:
...
> 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
6

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

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

 ### 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"], ...>

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,