CODEWITHSUNDEEP
mysqlruby-on-railsruby
Anand Jose
Anand Jose

Reputation: 658

How to solve Unicode character issue in mysql with rails functionality

I got an error Mysql2::Error: Incorrect string value: '\xE0\xA4\xAE\xE0\xA5\x81... in log while running a query.

I identified the reason for the error is MySql database not fully support the Unicode character. some blog suggest to migrate the database to utf8mb4.

But i want a solution with minimum change by using rails or ruby functionality. How we can use escape the unicode character before trying write in DB?

Upvotes: 0

Views: 1287

Answers (2)

Anand Jose
Anand Jose

Reputation: 658

Not have an option to escape the data.

but we can set encode type utf8bin for a specific column in db instead of migrate whole db.

Add a migration to change the column

reversible do |dir|
      dir.up {
        change_column :transaction_details, :merchant, "VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin"
      }
end

Upvotes: 0

tadman
tadman

Reputation: 211560

You need two things in order for this to work:

  • All character columns must be set to type utf8mb4
  • The database connection must be encoding: utf8mb4 in your config/database.yml

Once both of those are set the data should flow through correctly. There's no alternative but to set the encoding correctly. You can't "escape" these as they're valid data, or would be if the column type is utf8mb4. The MySQL database engine is rejecting them as being invalid for that encoding.

MySQL supports 4-byte UTF-8 if and only if you set encoding as utf8mb4. This is unusual as other databases like Postgres do this out of the box without differentiating between 1-3 byte UTF-8 and 4-byte UTF-8.

Upvotes: 1

Related Questions