MySQLで文字列による完全一致検索がうまく動作しなかった時の修復メモ
WordPressのwp_postmetaテーブルで同一のpost_id, meta_keyに値が重複してしまう問題がありました。
このWordPressは最近ウェブサーバー、DBサーバーを引っ越した環境です。
新環境にてカスタムフィールドを更新すると、本来post_idとmeta_keyの一致する値が更新されるべきところ、これらの重複するレコードが書き込まれてしまうという現象でした。
結論としては、自分自身のmysqldumpを取って再投入することで解消しました。
これが課題となったレコードの例です。
MariaDB > select * from wp_postmeta where post_id=1458 and meta_key regexp '\_thumbnail\_id';
+---------+---------+---------------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+---------------+------------+
| 11068 | 1458 | _thumbnail_id | 519 |
| 11201 | 1458 | _thumbnail_id | 1471 |
+---------+---------+---------------+------------+
2 rows in set (0.001 sec)
全く同じに見えるmeta_key="_thumbnail_id"ですが、検索すると片方しかヒットしません。色々動作を見ると、アンダースコアから始まるmeta_keyで再現するようでした。
MariaDB > select * from wp_postmeta where post_id=1458 and meta_key='_thumbnail_id';
+---------+---------+---------------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+---------------+------------+
| 11201 | 1458 | _thumbnail_id | 1471 |
+---------+---------+---------------+------------+
1 row in set (0.000 sec)
このようなクエリで制御文字が含まれているか確認できるようですが、該当しませんでした。mysqldumpを取得してエディタで該当するレコードを確認しても、制御文字等は見当たりません。
参考）https://sucrose.hatenablog.com/entry/2018/10/22/202049
MariaDB > select * from wp_postmeta where post_id=1458 and meta_key regexp '[[:cntrl:]]';
Empty set (0.001 sec)
indexがおかしくなっているかな、と思いindexの確認と再生成を行いました。
analyzeコマンドでindexが再生成されるようです。
参考） https://stackoverflow.com/a/30051747
MariaDB > show index from wp_postmeta;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta | 0 | PRIMARY | 1 | meta_id | A | 5347 | NULL | NULL | | BTREE | | |
| wp_postmeta | 1 | post_id | 1 | post_id | A | 2673 | NULL | NULL | | BTREE | | |
| wp_postmeta | 1 | meta_key | 1 | meta_key | A | 113 | 191 | NULL | YES | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.000 sec)
MariaDB > analyze table wp_postmeta;
+-------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| xxx.wp_postmeta | analyze | status | OK |
+-------------------------+---------+----------+----------+
1 row in set (0.006 sec)
しかし、検索結果に変化はありませんでした。
MariaDB > select * from wp_postmeta where post_id=1458 and meta_key='_thumbnail_id';
+---------+---------+---------------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+---------------+------------+
| 11201 | 1458 | _thumbnail_id | 1471 |
+---------+---------+---------------+------------+
1 row in set (0.001 sec)
MySQLの公式ドキュメントに、tableやindexがおかしかったらダンプ取って入れ直してみてね、と書いてあったのでやってみたところ、なんと期待する動作になりました。
MariaDB > select * from wp_postmeta where post_id=1458 and meta_key='_thumbnail_id';
+---------+---------+---------------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+---------------+------------+
| 11068 | 1458 | _thumbnail_id | 519 |
| 11201 | 1458 | _thumbnail_id | 1471 |
+---------+---------+---------------+------------+
2 rows in set (0.001 sec)
環境の移行がきっかけとは思われるものの、原因がはっきりしないことが気がかりです。また、解決の糸口もなかなか見つからず、苦労しました。
Discussion