[RUBY] Dealing with Mysql2 :: Error: Incorrect string value error

Introduction

I created a database, executed migration, and when I said "Let's put data (Japanese) in and move it!", The following error occurred. .. ..

Mysql2::Error: Incorrect string value: '\xE3\x82\xB2\xE3\x82\xB9...' for column 'name' at row 1

Apparently the character code is inappropriate. .. .. So, in this article, I'll write about what I've tried and how to solve this error!

Technology / environment

Docker/docker-compose ruby 2.7.2 rails 6.0.2.3

What I tried

① ** I checked the character code of the table. ** **

First, connect to MySQL.

$ docker-compose run web rails db

Next, check the character code of the database.

> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

After all, the character code of the database was "latin1". .. ..

• In MySQL, "latin1" is set as the character code by default.

② ** Correct the character code of the database to utf8mb4 ** I was using Docker, so I modified docker-compose.yml as follows.

• I referred to the official document below. https://hub.docker.com/_/mysql

docker-compose.yml

version: "3"
services:
  db:
    image: mysql:5.7
    #I added a line below this
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_USER: root
    ports:
      - "3306:3306"
    volumes:
      - ./db/mysql/volumes:/var/lib/mysql

  web:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    volumes:
      - .:/share-read
    ports:
      - "3000:3000"
    depends_on:
      - db
volumes:
  mysql-data:
    driver: local

Check the character code of the database again.

> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

I thought "OK! Correction completed!", But I still get an error. Maybe the character code of the table itself remains "latin1"? When I think about it. .. ..

> SHOW CREATE TABLE users;

| users | CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  #Omission
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

After all, the character code of the table did not change. .. ..

③ ** Correct the character code of the table to utf8mb4 ** So, I am going to modify the character code of the table, but there are about 10 tables and it is difficult to modify them one by one! Also, I haven't stored the data in the table yet, so I decided to redo the migration this time.

$ docker-compose run web rails db:reset
$ docker-compose run web rails db:migrate

So I solved it safely. If you get a similar error, please refer to it.