How to Get Emojis in MySQL (fixing Incorrect String Value)

So your Rails application with a MySQL database was humming along and all of a sudden it hit this error:

Mysql2::Error: Incorrect string value: '\xC5\x99\xC3\xA1k

What does that mean? Upon inspection you realize that someone has typed into your website the all-too-favorite Millenial communication tool: the emoji. ?✨

MySQL needs a little massaging. For steps 1 & 2 in Rails, create a new database migration and use execute to run these commands directly onto your database.

1. Make sure you actually alter the database’s character set

ALTER DATABASE <DATABASE NAME> CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Where <DATABASE NAME> is your database. in Rails, because we have different database names for different environments, try this inside of your migration…

execute "ALTER DATABASE #{ActiveRecord::Base.connection.current_database} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"

2. Make sure you ALTER the TABLE AND FIELD where you want to insert the character set

ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE modify TEXT charset utf8mb4;

(If you made a Rails migration, you can do this in the same migration using another execute.)

IMPORTANT: I have assumed above that your field is of type TEXT. If it is not, change your field’s type definition before running this. If you don’t, you’ll convert your existing field to a TEXT field even if you didn’t want to.

3. Restart your MySQL instance (DO NOT FORGET)
4. In Rails, you must also specify the encoding & collation in the MySQL adapter which you will find in your config/database.yml file (where you have specified mysql2 as the adapter).

For example:

production:
  encoding: utf8mb4
  collation: utf8mb4_general_ci

or if you want it in your default configs

default: &default
  encoding: utf8mb4
  collation: utf8mb4_general_ci

See also…

https://stackoverflow.com/questions/22464011/mysql2error-incorrect-string-value#22464749

https://stackoverflow.com/questions/16350310/mysql-mysql2error-incorrect-string-value/18498210#18498210

https://stackoverflow.com/questions/16350310/mysql-mysql2error-incorrect-string-value/16934647#16934647