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 された後に実行される。そのために、テストの時にはこの点を留意して書かなければならない。