概要
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の文字コード設定を見直す良い機会になったと思う。