概要

Rails Consoleでcsvインポート中に以下のエラーが出たのでその対応記録

ActiveRecord::StatementInvalid: 
Mysql2::Error: Incorrect string value: '\xE3\x82\xA8\xE3\x82\xB3...' 
for column 'name' at row 1: 
INSERT INTO `contracts` (`account_id`, `name`, `created_at`, `updated_at`) 
VALUES (101, 'エコノミー', '2020-05-30 01:50:58', '2020-05-30 01:50:58')
from /usr/local/bundle/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'

character_set_databaseの対応

character_set_database、character_set_serverがlatin1になっていた。

MySQL [example]> show variables like "chara%";
+--------------------------+-------------------------------------------------+
| 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       | /rdsdbbin/oscar-5.7.12.200076.0/share/charsets/ |
+--------------------------+-------------------------------------------------+
8 rows in set (0.001 sec)

AWS RDS Auroraのパラメータグループでutf8mb4に設定

MySQL [example]> show variables like "chara%";
+--------------------------+-------------------------------------------------+
| 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     | latin1                                          |
| character_set_system     | utf8                                            |
| character_sets_dir       | /rdsdbbin/oscar-5.7.12.200076.0/share/charsets/ |
+--------------------------+-------------------------------------------------+
8 rows in set (0.001 sec)

DEFAULT_CHARACTER_SET_NAMEの対応

それでもエラーが出たので継続調査。
DEFAULT_CHARACTER_SET_NAMEがlatin1になっていた。

MySQL [example]> select * from INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | example            | latin1                     | latin1_swedish_ci      | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | tmp                | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
6 rows in set (0.004 sec)

MySQL [example]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+
1 row in set (0.000 sec)

以下のSQLでutf8mb4に設定

MySQL [example]> ALTER DATABASE example CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

MySQL [example]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_bin          |
+--------------------------+----------------------+
1 row in set (0.000 sec)

テーブルのDEFAULT CHARSETの対応

それでもエラーが出たので、テーブルを調査。
DEFAULT CHARSETがlatin1になっていた。

MySQL [example]> SHOW CREATE TABLE contracts;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| contracts | CREATE TABLE `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.006 sec)

以下のSQLでutf8mb4に設定

MySQL [example]> ALTER TABLE contracts CONVERT TO CHARACTER SET utf8mb4;

MySQL [example]>  SHOW CREATE TABLE contracts;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| contracts | CREATE TABLE `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

これで正しく実行できるようになった。

テーブルの設定が最優先されていたので、
はじめからテーブルのDEFAULT CHARSETをutf8mb4に設定していれば解決したと思うが、
DBMS、DBの文字コード設定を見直す良い機会になったと思う。