05.05.2010

Full text search message "Query is not understandable"

>>Author:  Bernd Hort
>>Ort:     Hamburg

URL: http://www.assono.de/blog/d6plinks/FullTextSearchDifferentFieldTypes

Category: Lotus Notes

We all love Lotus Notes for its RAD flexibility. But sometimes Notes has a memory like an elephant.

A customer had recently a problem with the full text search. Early in the project a field in one of the Notes forms had been defined as a number field. Later they changed the field type to text. No big deal until a user tried to make a full text search specially for this field. The following message came as quite a surprise.

Query is not understandable

The search string FIELD fieldname= "Value" was correct.

It took a little while before we found out the right measures and the correct order to fix this problem.

I had read in the forum that the indexer determines the type of each field on the basis of the first found document while creating a new full text index. So we corrected all the documents with the wrong data type. Then we deleted the full text index and rebuilded it. The error was still the same.

After further research we have come to the conclusion that the UNK table might be the problem. UNK stands for "UNique Key Table" and refers to an internal table that in every Notes database / application holds up a list of all fields of the database. Compacting the database using a copy (option "-c") normally recreates that table. As we had to figure out the UNK table is not rebuilt, if a full text index exists.

The following order has finally led to success.
1. Delete the full text index
2. On the console Load compact database path -c
3. Create the full text index again

After that the query worked as expected.

The described procedure had to be repeated for each replica.

Comments

Die Situation hatte ich leider auch schon und bin dann den beschriebenen Weg zu Korrektur gegangen.
Mit dem Tool NotesPeek kann man sich diese UNK Table ansehen unter "Database Information" gibt es eine Option "Item Def Table" dort ist aufgelistet welches Feld mit welchem Datentyp in der UNK Table gelistet ist.

Posted by Markus At 07:27:36 On 06.05.2010 | - Website -

This has driven me crazy a few times! Thanks for documenting the steps necessary to fix this.

Posted by Martin At 14:52:23 On 06.05.2010 | - Website -

I've also had this problem and found that sometimes the UNK table is not rebuilt by compact -c.
After consulting IBM the solution was:
- delete FTI
- stop the Domino server
- from the DOS prompt on the actual server run ncompact.exe filename -c (this totally dumps the UNK table & rebuilds it)
- restart Domino server
- rebuild FTI

Fun fun fun as one database that was affected is only 45 Gig & it take awhile to compact that one.

Posted by sue At 10:53:29 On 11.05.2010 | - Website -

hier noch ein Hinweis vom IBM-Support zu diesem Thema:

Re-add and Delete Field - If an obsolete field is no longer in use on the form, but is listed under design properties of the form, it will be included in the UNK. To try to delete it, edit the form, re-add the field with the same spelling, and save the form. Now delete the field by placing your cursor to the right of the field and pressing the BACKSPACE key. Notes will prompt you to delete the field. Select Yes. If this does not work, you may need to create a new form by copy/pasting fields from the old form to a new form, and then delete the old form.

für den oben beschriebenen Weg existiert auch eine Technote
{ Link }