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'
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)
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)
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.
Recommended Posts