PAM searches returning incorrect results

Media managers working with me on a major sporting event in South Korea have spotted an unusual behaviour within their production asset management (PAM) system. I have gone in to investigate this strange problem.

When trying to filter search results on a clip unique identifier column (VarId), only one result has been returned, whilst actually there should have been three matches. Here is a list of clips that we have been trying to filter. The highlighted clips share the same VarId:

When filtered by that VarId we are getting only one result:

 

Why does that happen? The three VarId values for those clips are identical, so we should be seeing all of them as a result of such filtering.

To answer that question, I had a look at the query that our PAM is sending to the database, in order to give us this single result:

 

To see what part of the query has been causing the issue, I have started to strip down parts of it, until I have been left with a simple query that still reproduced the issue:

Clearly, the CONTAINS clause hasn’t been doing what it was supposed to do. When I ran it side-by-side with a query based on a LIKE expression I received two different results:

So the CONTAINS query appeared to be correct, as at least one match is being made. But why does it return only one row?

Could it be because there are some hidden characters in the other VarIds? I have tried converting them to VARBINARY but the binary values from all three rows were identical. What else could be going wrong?

One of the best troubleshooting techniques is isolating the problem, so I have started to re-create the problematic environment from scratch to try and reproduce the issue:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE Asset (
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](64) NULL,
[SuperID] [nchar](32) NULL
)

INSERT INTO Asset
(Name, SuperID)
VALUES
('Item1','(LO}(bJ$                        '),
('Item2','(LO}(bJ$                        '),
('Item3','(LO}(bJ$                        ');

CREATE UNIQUE CLUSTERED INDEX PK_Asset ON dbo.Asset (ID Asc);
CREATE FULLTEXT CATALOG FT_Cat_Asset as DEFAULT
GO
CREATE FULLTEXT INDEX ON dbo.Asset
(Name Language 1033,
SuperID Language 1033)
KEY INDEX PK_Asset on FT_Cat_Asset
WITH STOPLIST = OFF, CHANGE_TRACKING AUTO;
GO

After performing operations above, I have decided to compare FT catalog settings in production database to those in my test system. All matched, apart from one thing (!):

So there are 21,219 changes in the Asset table of the production system, which haven’t been reflected in the respective full-text catalogue. Why? A careful trawl through the MS SQL Server logs reveals that after a certain point in time the full-text index for the Asset table is no longer updated. Instead, we can only see indices for other tables being populated after that point.

Right around that time logs show an error:

2018-02-13 18:16:30.42 spid33s     An internal query to load data for a crawl on database ‘Media’ and table ‘Asset’ failed with error code 1205. Check the sql error code for more information about the condition causing this failure. The crawl needs to be restarted after this condition is removed.

Error code 1205 refers to a deadlock that has occurred during the FT catalogue population.

2018-02-13 18:16:30.42 spid33s     A fatal error occurred during a full-text population and caused the population to be cancelled. Population type is: AUTO; database name is Media (id: 6); catalog name is FT_Cat_Media_Asset (id: 6); table name Asset (id: 1076966963). Fix the errors that are logged in the full-text crawl log. Then, resume the population. The basic Transact-SQL syntax for this is: ALTER FULLTEXT INDEX ON table_name RESUME POPULATION.

 

Let’s follow the advice and run “ALTER FULLTEXT INDEX ON Asset RESUME POPULATION”.

After running the command above, the number of Pending Changes has dropped significantly:

So let’s try our DB query now:

 

Now we are getting 3 results – as expected!

Thanks to TheCollectv for inviting me to work on yet another exciting event!