[Rails5.2] Support for emoji of Mysql 5.7 in Docker (change character code to utf8mb4)

Introduction

I'm a Rails beginner creating a portfolio in Ruby on Rails. This time, as the title suggests, it is a memorandum when mysql for saving pictogram information in table is set to utf8mb4.

** I got an error when trying to save a video title from the Youtube Data API. ** **

ActiveRecord::StatementInvalid in VideosController#refresh
Mysql2::Error: Incorrect string value: '\xF0\x9F\x94\xB5\xE8\x87...'

There are some titles with emoji, which seems to have caused an error. Looking at the character code of db on mysql, it is uft8, and it seems that this must be changed ... For the time being, I carried out as follows with reference to various codes.

environment

I'm developing Rails inside a container using Docker for mac.

ruby 2.4.5
mysql 5.7.31
Ruby on rails 5.0.7.2

flow

(Preparation: dump)

1. Modify my.cnf
2. Restart mysql
3. Add options to ActiveRecord
4. Modify database.yml
5. Modify docker-compose.yml and restart the container
6. Execute db: migrate: reset
7. Restore dump data

Preparation: dump

I didn't know the word dump until I did this work in the first place, but it seems that ** dump ** is to output the information in the DB table in the form of SQL. (By the way, the last restore is to put the dumped information into the DB)

Since we will change the database settings this time and change the character code of each table to utf8mb4, we will take the form of temporarily dropping the data as a file and inputting it after changing the settings.

Use the following gem to make dump / restore easy.

Gemfile

gem 'yaml_db'

This time I use yaml_db, a gem that outputs a yaml format file by dumping. GitHub - yamldb/yaml_db

To dump, just execute the following command

Terminal

bundle exec rails db:data:dump

After executing the command, the file is output to db / data.yml. Like this, basically the table name, column information, and record are output in that order.

data.yml

videos:
  columns:
  - id
  - name
  - url
  - upload_at
  - created_at
  - updated_at
  records:
  - - 1
    - "[Impact] A company that decides the amount of bonus payment by quiz"
    - https://www.youtube.com/watch?v=42ofwfioMFM
    - 2020-10-09 09:00:00.000000000 Z
    - 2020-10-10 08:07:38.000000000 Z
    - 2020-10-10 08:07:38.000000000 Z
 :

The preparation is OK below.

1. Modify my.cnf

Set the my.cnf file as follows. I wanted to understand what was written, so I looked at it line by line.

my.cnf

[mysql]
default-character-set=utf8mb4 #Character code setting

[mysqld] #mysqld Mysql server settings
character-set-server = utf8mb4 #Character code setting
skip-character-set-client-handshake #For ignoring the character code specified on the client side
collation-server = utf8mb4_general_ci #Specifying the sort order
init-connect = SET NAMES utf8mb4 #Character set specification used for sending from client to server

Let's take a closer look at each item.

[mysql] and [mysqld] mysqld is the main program that is in charge of various operations in mysql, also called the MySQL server. Since the operation on the mysql side is always done from this mysqld, various settings are required here.

skip-character-set-client-handshake character-set-client-handshake is the act of reflecting the character code on the client side on the MySQL side. By skipping this, you can set to uft8mb4.

collation-server = utf8mb4_general_ci collation is collation: sort order. Set for each word separated by _. See below for details [MySQL] What is collation?

init-connect = SET NAMES utf8mb4 Specifying the character code used for sending from the client to the server See below for details 10.1.4 Connection character set and collation

2. Restart mysql

For now, restart mysql for this setting to take effect. Enter the container in Docker and execute the following command.

Check the original settings for the time being

terminal

mysql>status;
:(Omission)
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
:

It is utf8. So, restart it and try again.

terminal

mysql>service mysql restart

terminal

mysql>status
:(Omission)
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
:

I was able to set it!

3. Add options to ActiveRecord

Next, when Create_table of ActiveRecord is executed, set it to register using utf8mb4.

Create a new config / initializers / utf8mb4.rb and write the following code. When you populate config / initializers with a file, it will be loaded as default before Rails starts.

config/initializers/utf8mb4.rb

#module to set option
module Utf8mb4
  def create_table(table_name, options = {})
    table_options = options.merge(options: 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')
    super(table_name, table_options) do |td|
      yield td if block_given?
    end
  end
end

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters
    class AbstractMysqlAdapter
      #First run module utf8mb4 and then run existing methods(super)      
      prepend Utf8mb4
    end
  end
end

4. Modify database.yml

Add or change the following contents

config/database.yml

  charset: utf8mb4
  encoding: utf8mb4
  collation: utf8mb4_general_ci

5. Modify docker-compose.yml

Change the Docker configuration file as follows.

docker-compose.yml

:
  db:
    image: mysql:5.7
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci #Set utf8mb4
    container_name:Container name
    volumes:
      - ./my.cnf:/etc/mysql/conf.d/my.cnf #my.Set to read cnf
 :

In this state, restart the container once.

6. Execute db: migrate: reset

Reset the DB → migrate to reflect the settings to the DB so far.

7. Restore dump data

Finally, re-input the data dumped at the beginning and you're done. You can restore it with the following command.

terminal

rails db:data:load

This completes the settings!

At the end

I've set various settings, but it was a good study because I was able to know the functions such as dump.

** Sites that have been used as a reference ** Thank you very much···! ・ Change MySQL encoding from utf8 to utf8mb4 to deal with sushi beer problem -Rails DB backup (gem: yaml_db) -Save emoji in Rails5 utf8mb4 (docker)