Ruby on Rails Incorrect string value error resolution when posting a form in Japanese

Introduction

environment

• Docker
• ruby 2.3.7
• Rails 5.2.4.4
• MySQL 5.7.32

error contents

Incorrect string value error occurs when I enter Japanese in the new post form and submit it.

Cause

From the error statement of Incorrect string value, it is inferred that the cause is the character string entered in the form. I entered English as a trial and sent it, and I was able to post without problems.

Does that mean that the DB does not support Japanese?

Connect to MySQL and check the settings of the database in use.

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

The DB character code setting was latin1 instead of utf8. It seems that latin1 does not support Japanese in Western European languages.

The cause was that the character code setting in the database.yml file was missing when building the Docker environment.

database.yml

default: &default
  adapter: mysql2
  #Here "charset:The cause was that "utf8" was missing
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: hideki1104
  host: db

Solutions

in the etc/mysql/my.cnf file

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
skip-character-set-client-handshake
[mysqldump]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

You can change the character code by writing.

I tried to add a description using the vi editor, but the vi command is not set and cannot be used.

# vi my.cnf
/bin/sh: 5: vi: not found

It seems that you can install vim using apt_get.

# apt-get -v
apt 1.8.2.2 (amd64)

Update once with the following command

# apt-get update

Install vim.

# apt-get install vim

You can now use vim.

etc/mysql/my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
skip-character-set-client-handshake
[mysqldump]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

I was able to change the character code by writing.

In this state, the character cards of DB, table, and column that have already been created have not been changed.

I typed the following command in MySQL DB character code setting

ALTER DATABASE DB name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Table character code setting

ALTER TABLE table name CONVERT TO character SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Column character code setting

ALTER TABLE table name CHANGE column_name Column name VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

You can now submit Japanese using the post form.

Reference article

-Change MySQL VARIABLES from command -[When the vi command cannot be used in the docker container] (https://blog.azimicat.com/entry/2019/09/12/docker%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A%E3%81%AE%E4%B8%AD%E3%81%A7vi%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89%E3%81%8C%E4%BD%BF%E3%81%88%E3%81%AA%E3%81%84%E3%81%A8%E3%81%8D) -MySQL cannot change character code to UTF-8