[RUBY] Rails-Konsole Falsche Behandlung von Zeichenfolgenwertfehlern

Überblick

Der folgende Fehler ist beim Importieren von CSV in die Rails Console aufgetreten, also der entsprechende Datensatz

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

Korrespondenz von character_set_database

Character_set_database und Character_set_server waren 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)

In der AWS RDS Aurora-Parametergruppe auf utf8mb4 setzen

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)

Korrespondenz von DEFAULT_CHARACTER_SET_NAME

Trotzdem ist ein Fehler aufgetreten, also habe ich weiter nachgeforscht. DEFAULT_CHARACTER_SET_NAME war 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)

Setzen Sie mit der folgenden SQL auf 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)

Entsprechung der Tabelle DEFAULT CHARSET

Ich habe immer noch einen Fehler, also habe ich die Tabelle überprüft. STANDARD CHARSET war 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)

Setzen Sie mit der folgenden SQL auf 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)

Jetzt können Sie es richtig ausführen.

Da die Tabelleneinstellungen die höchste Priorität hatten, Ich denke, es wäre gelöst worden, wenn das DEFAULT CHARSET der Tabelle von Anfang an auf utf8mb4 gesetzt worden wäre. Ich denke, es war eine gute Gelegenheit, die Zeichencodeeinstellungen von DBMS und DB zu überprüfen.