[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)

Recommended Posts

[Rails5.2] Support for emoji of Mysql 5.7 in Docker (change character code to utf8mb4)
Procedure to change lower_case_table_names = 1 in MySQL 8.0 of CentOS 8.3
How to install Docker in the local environment of an existing Rails application [Rails 6 / MySQL 8]
For those who want to use MySQL for the database in the environment construction of Rails6 ~.
Summary of steps for developing in Docker container with VS Code
[Rails] How to change the page title of the browser for each page
How to change app name in rails
Deploy to heroku with Docker (Rails 6, MySQL)
Support for gdb errors in Docker containers
How to use MySQL in Rails tutorial
Japanese setting of mysql in Docker container
Change the save destination of the image to S3 in the Rails app. Part 2
Complete roadmap for building environment up to Docker + rails6 + MySQL + bootstrap, jquery
Procedure for introducing Docker into the development environment of existing Rails applications [Rails, MySQL, Docker]
Sample code to get the values of major SQL types in Java + MySQL 8.0
How to use JQuery in js.erb of Rails6
[Rails] How to use Gem'rails-i18n' for Japanese support
Support out of support in docker environment using centos6
Summary of frequently used commands in Rails and Docker
Change date and time to Japanese notation in Rails
SSL in the local environment of Docker / Rails / puma
[Ruby on Rails] From MySQL construction to database change
How to specify character code and line feed code in JAXB
How to set character code and line feed code in Eclipse
Things to be aware of when writing code in Java
Exit code 1 occurs when Rails is stopped in Docker environment
When you want to change the MySQL password of docker-compose
[Rails] Where to be careful in the description of validation
How to change BackgroundColor etc. of NavigationBar in Swift UI
Procedure for migrating Rails application development environment to Docker even if you are inexperienced (Rails5 + MySQL8.0 + docker-compose)
[Docker] How to back up and restore the DB data of Rails application on docker-compose [MySQL]