TL;DR
- MySQL の全文検索では、変更を commit しなければ index が作成されない。
- これは、全文検索用の index を作成する処理は非常に重いので、内部では commit 後にバッチ処理を実行するため。
- そのために、unittest では rollback ができないので、必要なデータを全て消す方法を採用した。
はじめに
MySQL の全文検索を用いた Web Application を作った際にテスト時にハマったことがありましたので、その原因と対処について記したいと思います。
概要
実行環境
- MySQL 5.7
- Storage Engine は InnoDB を使っています
- Python 3.8
- SqlAlchemy 1.4.7
やろうとしていたこと
- 検索機能を Web Application に作成する
- MySQL の全文検索を用いた
- この箇所のテストコードは session を使ってテスト後に rollback をする設計になっていた
困ったこととその対処
MySQL 全文検索を用いた方法は正しく動きました。しかしながら、そのテストが正しく動きませんでした。詳しく説明すると、対象箇所の全文検索箇所には投入したはずのデータがなく、毎回テストが落ちる結果になっていました。テストコードを何度も見ましたが、特に問題はなさそうだったために、念のためにMySQL のドキュメントを細かく調べることにしました。するとこのように書かれていました。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html より抜粋
InnoDB による全文インデックスのトランザクション処理
InnoDB の FULLTEXT インデックスには、そのキャッシュおよびバッチ処理の動作のために、特別なトランザクション処理の特性が備わっています。特に、FULLTEXT インデックス上の更新および挿入は、トランザクションのコミット時に処理されます。つまり、FULLTEXT 検索では、コミットされたデータのみを表示できます。次の例で、この動作を実演します。FULLTEXT 検索では、挿入された行がコミットされたあとにはじめて、結果が返されます。
mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
ここで初めて知りましたが、InnoDB では FullText 用の Index を作成する際には、commit することが条件だったのです。
テストコードの対処
今回のテストの設計では、DBを使った処理はテストの最後に確実に rollback がなされるような設計にしていました。しかしながら、これではこの方法では、FullText 用のインデックスを作成することができずに、正しくテストを行うことをできません。
苦肉の策ですが、FullText Index を用いる箇所のみデータを commit し、テスト後にはデータを全て削除するという方針に変更しました。もちろん、これが本当に良い方法であるかどうかわかりませんが、この方針で正しくテストが動くようになりました。
まとめ
InnoDB において FullText 用のインデックスが作成されるのは、commit された後に実行される。そのために、テストの時にはこの点を留意して書かなければならない。