MariaDB FULLTEXT searches and transactions

Another in a series of "things I had to learn the hard way, and you shouldn't".

The key takeaway from this is that MariaDB FULLTEXT search indices are not updated until a transaction is COMMIT'ed.

Allow me to demonstrate:

MariaDB [fulltext_demo]> create table text_data (
   -> id serial, info text);
Query OK, 0 rows affected (0.022 sec)

MariaDB [fulltext_demo]> CREATE FULLTEXT INDEX 
   -> ft_idx on text_data(info);

Now we have a table fulltext_demo with a row info indexed for a fulltext search. And just to prove it:

MariaDB [fulltext_demo]> insert into text_data set info="banana";
Query OK, 1 row affected (0.015 sec)

MariaDB [fulltext_demo]> select * from text_data where 
   -> match(info) against ('banana');
+----+--------+
| id | info   |
+----+--------+
|  1 | banana |
+----+--------+
1 row in set (0.000 sec)

So far, so good. Now let's do that inside a transaction.

MariaDB [fulltext_demo]> begin;
Query OK, 0 rows affected (0.000 sec)

MariaDB [fulltext_demo]> insert into text_data set info="orange";
Query OK, 1 row affected (0.000 sec)

MariaDB [fulltext_demo]> select * from text_data where 
   -> match(info) against ('orange');
Empty set (0.000 sec)

If like me, you are going "WTF" at this point, see above. The updated row does not get added to the fulltext search index until the commit. Allow me to demonstrate.

MariaDB [fulltext_demo]> commit;
Query OK, 0 rows affected (0.000 sec)

MariaDB [fulltext_demo]> select * from text_data where
   -> match(info) against ('orange');
+----+--------+
| id | info   |
+----+--------+
|  2 | orange |
+----+--------+
1 row in set (0.000 sec)

This makes testing things inside a transaction (where you intend to roll back the test fixtures afterwards) just a little bit more irritating than it could be. :D