Thursday, October 26, 2006

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.

Saturday, October 21, 2006

How to do a "fuzzy" MySQL query using Perl DBI

Suppose you have a MySQL query as follows:
SELECT last_name FROM customers WHERE last_name LIKE "%smith%"
and you want to use the Perl DBI prepared statement API to query your database. This is how you code it:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbname='your_database_name';
my $dbh = DBI->connect("dbi:mysql:dbname=$dbname;host=localhost");
my $sth = $dbh->prepare("SELECT last_name FROM customers WHERE last_name LIKE ? ");
my $name = "%smith%";
$sth->execute($name);
while ( my @row = $sth->fetchrow_array ) {
   print "Row @row\n";
}


I was briefly befuddled in trying to put the percent characters in the SQL query itself, along with the placeholder, which does not work.

The DBI section on placeholders does give you a hint about how to handle this case. You just need to read the page carefully!