[RUBY] Rails console Incorrect string value error handling

Overview

The following error occurred while importing csv in Rails Console, so the corresponding record

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, 'Economy', '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'

Correspondence of character_set_database

character_set_database and character_set_server were 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)

Set to utf8mb4 in AWS RDS Aurora parameter group

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)

Correspondence of DEFAULT_CHARACTER_SET_NAME

Still, an error occurred, so I continued to investigate. DEFAULT_CHARACTER_SET_NAME was 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)

Set to utf8mb4 with the following SQL

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)

Correspondence of DEFAULT CHARSET of table

I still got an error, so I checked the table. The DEFAULT CHARSET was 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)

Set to utf8mb4 with the following SQL

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)

Now you can run it correctly.

Since the table settings had the highest priority, I think it would have been solved if the DEFAULT CHARSET of the table was set to utf8mb4 from the beginning. I think it was a good opportunity to review the character code settings of DBMS and DB.