MySQL: FULLTEXT search returns no results

This was a puzzler. I was running a MySQL query that I knew should return results, like this:

mysql> SELECT * FROM table1 WHERE MATCH (name) AGAINST ('pickle') LIMIT 10;
Empty set (0.00 sec)
mysql> SELECT * FROM table1 WHERE name LIKE 'pickle';
+------+--------+
| id   | name   |
+------+--------+
| 171  | pickel |
+------+--------+
1 row in set (0.00 sec)


The solution involved the history of this particular database. I had migrated the MySQL database from our development server to our stage server (both running Linux). The way I migrated was the "cheating" way: by copying the MySQL database files from one server to the other.

1) I compressed all my database files into one file like this:

tar cvfz /home/wereldmuis/my_db.tgz /var/lib/mysql/my_db

Note that my_db is the name of the directory containing all my database files, like table1.MYI and so on.

2) I copied the tgz file over to my stage server.

3) Stopped MySQL on stage: /etc/init.d/mysql stop (as root).

4) untarred my files into the stage MySQL database directory:

cd /var/lib/mysql
tar xvfz /home/wereldmuis/my_db.tgz

At this point I had to change a few ownerships and permissions on files.

5) Start MySQL on the stage server: /etc/init.d/mysql start.

At this point, I ran some test queries against the db and all was fine. A little bit later, I realized that the version of MySQL running on dev was different from the one on stage (4.1 for the former, 5.0 for the latter). I realized that when I found that one of my more complicated SQL queries, which involved some LEFT JOINs was failing. I figured out the problem was due to the version difference, fixed that query, and went about my business.

At that point, I failed to take the hint that migrating a MySQL database between versions is not quite so simple. When another complicated query, this one using a FULLTEXT index, begain to fail (returning no results), I was stymied. The same query ran fine in my dev environment. Then a coworker realized that a very simple FULLTEXT search was also returning no results. At first I thought maybe FULLTEXT was not working because MySQL was configured differently on the stage server (only working on longer strings). But even long strings would not return a match, when they were clearly right there in the column being searched. I then became suspicious, dropped and rebuilt the FULLTEXT index. Which solved the problem.

So copying files is a handy way to migrate a database from one machine to another; just beware that things may break, in particular when the database version varies between the machines.