【メモ】MySQL内のUTF-8のデータが文字化けしていたのを直す
はじめに
例の如くの備忘録です。
ある日、「MySQLのデフォルトの文字コードがlatin1になっていたのでUTF-8に変えたところ、Teampassからデータを見た時文字化けするようになってしまい、たぶんUTF-8でない文字コードでデータが保存されてると思うから原因を調べて欲しい」というお話がありました。
調べてみたところ、結果的にはTeampass側というよりはMySQL側の問題だったようです。
説明の都合上Teampassで利用しているテーブル名そのままの記述となっていますが、適宜自身の環境に合わせてDB名、テーブル名などを読み替えていただければと。
以下、調査手順。
手順
まあ何はともあれまずはDBを見に行きますか、ということでMySQL（MariaDB）のteampassデータベースにアクセスしてみます。
# mysql -u root -p #mysqlに接続
MariaDB [(none)]> show databases; #データベースの一覧を確認
MariaDB [(none)]> use teampass; #teampassデータベースに接続
MariaDB [teampass]> show tables; #テーブル一覧を確認
MariaDB [teampass]> select * from teampass_items\G #各アイテムのレコードを確認こうしてレコードを確認してみると、確かにlabelやdescriptionなどに『î』みたいな文字が入っているのが見えました。
ほうほうと思いつつ、もう少し詳しく調べてみます。
MariaDB [teampass]> show variables like '%char%'; #mysqlの文字コードを確認
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+MySQL全体の文字コードは基本的にutf8になっている様子。
個別に文字コードがどうなっているか掘り下げます。
MariaDB [teampass]> show columns from teampass_items; #データ型の確認
+---------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+----------------+
| id | int(12) | NO | PRI | NULL | auto_increment |
| label | varchar(500) | NO | | NULL | |
| description | text | YES | | NULL | |
| pw | text | YES | | NULL | |
| pw_iv | text | YES | | NULL | |
| pw_len | int(5) | NO | | 0 | |
| url | varchar(500) | YES | | NULL | |
| id_tree | varchar(10) | YES | | NULL | |
| perso | tinyint(1) | NO | | 0 | |
| login | varchar(200) | YES | | NULL | |
| inactif | tinyint(1) | NO | | 0 | |
| restricted_to | varchar(200) | YES | MUL | NULL | |
| anyone_can_modify | tinyint(1) | NO | | 0 | |
| email | varchar(100) | YES | | NULL | |
| notification | varchar(250) | YES | | NULL | |
| viewed_no | int(12) | NO | | 0 | |
| complexity_level | varchar(3) | NO | | -1 | |
| auto_update_pwd_frequency | tinyint(2) | NO | | 0 | |
| auto_update_pwd_next_date | varchar(100) | NO | | 0 | |
| encryption_type | varchar(20) | NO | | not_set | |
+---------------------------+--------------+------+-----+---------+----------------+
20 rows in set (0.003 sec)
MariaDB [teampass]> ALTER TABLE teampass_items MODIFY description text CHARACTER SET utf8 COLLATE utf8_general_ci; #descriptionカラムをUTF8に文字コードを変更してみる
Query OK, 0 rows affected (0.029 sec)
Records: 0 Duplicates: 0 Warnings: 0更新できたレコードは0件。つまりdescriptionはみんなUTF-8っぽいです。
さらに調べてみます。
MariaDB [teampass]> show create table teampass_items\G #create tableステートメントを確認
*************************** 1. row ***************************
Table: teampass_items
Create Table: CREATE TABLE `teampass_items` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`label` varchar(500) NOT NULL,
`description` text DEFAULT NULL,
`pw` text DEFAULT NULL,
`pw_iv` text DEFAULT NULL,
`pw_len` int(5) NOT NULL DEFAULT 0,
`url` varchar(500) DEFAULT NULL,
`id_tree` varchar(10) DEFAULT NULL,
`perso` tinyint(1) NOT NULL DEFAULT 0,
`login` varchar(200) DEFAULT NULL,
`inactif` tinyint(1) NOT NULL DEFAULT 0,
`restricted_to` varchar(200) DEFAULT NULL,
`anyone_can_modify` tinyint(1) NOT NULL DEFAULT 0,
`email` varchar(100) DEFAULT NULL,
`notification` varchar(250) DEFAULT NULL,
`viewed_no` int(12) NOT NULL DEFAULT 0,
`complexity_level` varchar(3) NOT NULL DEFAULT '-1',
`auto_update_pwd_frequency` tinyint(2) NOT NULL DEFAULT 0,
`auto_update_pwd_next_date` varchar(100) NOT NULL DEFAULT '0',
`encryption_type` varchar(20) NOT NULL DEFAULT 'not_set',
PRIMARY KEY (`id`),
KEY `restricted_inactif_idx` (`restricted_to`,`inactif`)
) ENGINE=InnoDB AUTO_INCREMENT=1343 DEFAULT CHARSET=utf8
1 row in set (0.161 sec)
MariaDB [teampass]> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.COLUMNS where COLLATION_NAME like 'utf8%'; #UTF8のカラムを確認
~~中略~~
| teampass | teampass_items | label |
| teampass | teampass_items | description |
| teampass | teampass_items | pw |
| teampass | teampass_items | pw_iv |
| teampass | teampass_items | url |
| teampass | teampass_items | id_tree |
| teampass | teampass_items | login |
| teampass | teampass_items | restricted_to |
| teampass | teampass_items | email |
| teampass | teampass_items | notification |
| teampass | teampass_items | complexity_level |
| teampass | teampass_items | auto_update_pwd_next_date |
| teampass | teampass_items | encryption_type |
~~中略~~やはりteampass_itemsの各カラムの文字コードはUTF-8。
これは別のところに原因がありそうだな、ということで調べてみたところ、こちらの記事が見つかりました。
どうやらこちらの文字化けはUTF-8で二重にエンコードされることで生じる文字化けのようです。
こちらの現象はmysql上でレコードの文字コードを変更しても改善しないことが多いので、一旦元の文字コードでdumpを取り、そのdumpの文字コードをutf8に変えてインポートするのが良いと。ふむふむ。
その通りに対応してみます。
元の文字コードはlatin1なのでlatin1でdumpを取得し、UTF-8でインポートします。
#teampassデータベース中のteampass_itemsテーブルのdumpをlatin1の文字コードで取得
# mysqldump -u root -p --skip-set-charset --default-character-set=latin1 teampass teampass_items > convert-dump.sql
#上記dumpの文字コードをutf8にしてteampassデータベースにインポート
# mysql -u root -p --default-character-set=utf8 -D teampass < convert-dump.sqlこれで文字化けが無事解消されました！