SQL Full Text Search is not indexing binary field

I have a field in my database which contains a word document. This is stored in a varBinary(max) field.

I use the Full-Text Search to search for text in this field. Herefore I have created a field 'Extension' and every record contains '.doc' in the Extension field. I have set the change tracking to automatic.

Everything works okay, until I create a new record. When I create a new record and I search on a word that is only been put in the new record, the FTS finds nothing. But, when I do this:

UPDATE table SET Extension = '.doc'

the FTS finds my new record! How is this possible? What am i missing?

S开发者_如何学运维o, when I perform an update on the records, the records are re-indexed?

I had this exact problem. Seems it was because the catalog was created and fulltext enabled after the table already had data in it, so the fulltext catalog never updated for the original data. The following served to resolve the problem for my existing data, however there's probably a simpler method:

ALTER FULLTEXT INDEX ON table SET CHANGE_TRACKING OFF; -- turn auto off
ALTER FULLTEXT INDEX ON table STOP POPULATION; -- stop anything in progress
ALTER FULLTEXT INDEX ON table START FULL POPULATION; -- do a full population
ALTER FULLTEXT INDEX ON table SET CHANGE_TRACKING AUTO; -- auto back on

Sorry, but maybe the problem is that you can't full text index a binary/Varbinary column, so you are possibly getting some undefined behavior.

From the [5.1] manual:

Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Although I haven't 'tried' it myself to confirm and re-create your exact situation, where the column seems indexed