How to solve Unicode character issue in mysql with rails functionality

Question

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?

tadman · Accepted Answer · 2018-12-19 18:04:05Z

1

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.

answered Dec 19, 2018 at 18:04

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anand Jose Over a year ago

The error happen when i move the data from one table column to another table column. if it not supported at all how get saved in existing table

tadman Over a year ago

It depends on the encoding you're using and how you're moving it. Make sure everything is utf8m4b.

Anand Jose · Accepted Answer · 2018-12-21 09:10:08Z

0

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

answered Dec 21, 2018 at 9:10

Anand Jose

6589 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to solve Unicode character issue in mysql with rails functionality

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related