Smart Search indexing failing (MS SQL) - Joomla! Forum - community, help and support

we noticed fault plug-in joomla, may being caused failure of joomla index content. may not related component doesn't work , when looked in found smart search indexing doesn't work thought ought fix first built in joomla issue may affect other things too.

firstly, can smart search disabled completely, won't called when other plug-in creates content?

secondly, guess need fix indexing on ms sql. spent ages going through updating php have got point can't continue don't know enough values coming from. reproduce indexing issue, on ms sql system, go components \ smart search , click index button. below steps have taken far deal each of 12 errors in turn. failing copy come values xxx_finder_tokens_aggregate xxx_finder_links_terms0 because contain null term_id. 2 things concern me this, why can't continue:
1) reason have null term_id because records created earlier query partly xxx_finder_terms table, on system (both test , live) empty. if earlier query found records in here term_ids solve problem; should table have values in perhaps not created during installation due issue?
2) there seem section in php deal (it populates null values):
/*
* now, inserted bunch of new records terms table
* need go , update aggregate table the
* new term ids.
*/
$query = $db->getquery(true);
$query->update('ta');
$query->set('ta.term_id = t.term_id #__finder_tokens_aggregate ta inner join #__finder_terms t on t.term = ta.term');
$query->where('ta.term_id null');
$db->setquery($query);
$db->query();
but assumes xxx_finder_terms table have missing values in it.

so, re-iterate; quick fix see if plug-in work disable whole smart search together. failing that, can tell me should in xxx_finder_terms table , me understand why mine blank , how populate it?

below steps have worked out far fix indexing issue:

firstly run:
alter table [xxx_finder_tokens_aggregate] alter column [term_id] [bigint] null;
alter table [xxx_finder_tokens_aggregate] alter column [map_suffix] [nchar](1) null;
alter table [xxx_finder_tokens_aggregate] add default ((0)) [term_id];
alter table [xxx_finder_tokens_aggregate] add default ((0)) [total_weight];

which allows nulls in couple of fields , sets blank values zeros instead of nulls in others.

then, in c:\inetpub\xxx\administrator\components\com_finder\helpers\indexer change indexer.php follows:

change this:
$query = 'insert ' . $db->quotename('#__finder_tokens_aggregate') .
' (' . $db->quotename('term_id') .
', ' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('term_weight') .
', ' . $db->quotename('context') .
', ' . $db->quotename('context_weight') . ')' .
' select' .
' t.term_id, t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context,' .
' round( t1.weight * count( t2.term ) * %f, 8 ) context_weight' .
' (' .
' select distinct t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context' .
' ' . $db->quotename('#__finder_tokens') . ' t1' .
' t1.context = %d' .
' ) t1' .
' join ' . $db->quotename('#__finder_tokens') . ' t2 on t2.term = t1.term' .
' left join ' . $db->quotename('#__finder_terms') . ' t on t.term = t1.term' .
' t2.context = %d' .
' group t1.term' .
' order t1.term desc';

to:
$query = 'insert ' . $db->quotename('#__finder_tokens_aggregate') .
' (' . $db->quotename('term_id') .
', ' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('term_weight') .
', ' . $db->quotename('context') .
', ' . $db->quotename('context_weight') . ')' .
' select' .
' t.term_id, t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context,' .
' round( t1.weight * count( t2.term ) * %f, 8 ) context_weight' .
' (' .
' select distinct t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context' .
' ' . $db->quotename('#__finder_tokens') . ' t1' .
' t1.context = %d' .
' ) t1' .
' join ' . $db->quotename('#__finder_tokens') . ' t2 on t2.term = t1.term' .
' left join ' . $db->quotename('#__finder_terms') . ' t on t.term = t1.term' .
' t2.context = %d' .
' group t1.term, t.term_id, t1.term, t1.stem, t1.common, t1.phrase, t1.weight, t1.context' .
' order t1.term desc';


this:
$db->setquery(
'insert ignore ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' ta.term_id = 0' .
' group ta.term'
);
$db->query();

to:
$db->setquery(
'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' ta.term_id = 0' .
' group ta.term'
);
$db->query();


this:
$queryinsign = 'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' 1 not in ' .
'( select 1 ' . $db->quotename('#__finder_terms') .
' ta.term_id = 0 )' .
' , ta.term_id = 0' .
' group ta.term';

to:
$queryinsign = 'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' 1 not in ' .
'( select 1 ' . $db->quotename('#__finder_terms') .
' ta.term_id = 0 )' .
' , ta.term_id = 0' .
' group ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight';


this:
$qurepl_p2 = 'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' 1 not in ' .
'( select 1 ' . $db->quotename('#__finder_terms') .
' ta.term_id = 0 )' .
' , ta.term_id = 0' .
' group ta.term';

to:
$qurepl_p2 = 'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' 1 not in ' .
'( select 1 ' . $db->quotename('#__finder_terms') .
' ta.term_id = 0 )' .
' , ta.term_id = 0' .
' group ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight';


this:
$db->setquery(
'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' ta.term_id = 0' .
' group ta.term'
);
$db->query();

to:
$db->setquery(
'insert ' . $db->quotename('#__finder_terms') .
' (' . $db->quotename('term') .
', ' . $db->quotename('stem') .
', ' . $db->quotename('common') .
', ' . $db->quotename('phrase') .
', ' . $db->quotename('weight') .
', ' . $db->quotename('soundex') . ')' .
' select ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight, soundex(ta.term)' .
' ' . $db->quotename('#__finder_tokens_aggregate') . ' ta' .
' ta.term_id = 0' .
' group ta.term, ta.stem, ta.common, ta.phrase, ta.term_weight'
);
$db->query();


this:
$query->update($db->quotename('#__finder_tokens_aggregate') . ' ta');
$query->join('inner', $db->quotename('#__finder_terms') . ' t on t.term = ta.term');
$query->set('ta.term_id = t.term_id');
$query->where('ta.term_id = 0');
$db->setquery($query);

to:
$query->update('ta');
$query->set('ta.term_id = t.term_id #__finder_tokens_aggregate ta inner join #__finder_terms t on t.term = ta.term');
$query->where('ta.term_id null');
$db->setquery($query);


this:
$query->update($db->quotename('#__finder_terms') . ' t');
$query->join('inner', $db->quotename('#__finder_tokens_aggregate') . ' ta on ta.term_id = t.term_id');
$query->set('t.' . $db->quotename('links') . ' = t.links + 1');
$db->setquery($query);

to:
$query->update('t');
$query->set($db->quotename('t.links') . ' = t.links + 1 #__finder_terms t inner join #__finder_tokens_aggregate ta on

ta.term_id = t.term_id');
$db->setquery($query);


this:
$query->update($db->quotename('#__finder_tokens_aggregate'));
$query->set($db->quotename('map_suffix') . ' = substr(md5(substr(' . $db->quotename('term') . ', 1, 1)), 1, 1)');
$db->setquery($query);

to:
$query->update($db->quotename('#__finder_tokens_aggregate'));
$query->set($db->quotename('map_suffix') . " = substring(hashbytes('md5', substring(" . $db->quotename('term') . ', 1, 1)), 1, 1)');
$db->setquery($query);


this:
$db->setquery(
'insert ' . $db->quotename('#__finder_links_terms' . $suffix) .
' (' . $db->quotename('link_id') .
', ' . $db->quotename('term_id') .
', ' . $db->quotename('weight') . ')' .
' select ' . (int) $linkid . ', ' . $db->quotename('term_id') . ',' .
' round(sum(' . $db->quotename('context_weight') . '), ' .
' ' . $db->quotename('#__finder_tokens_aggregate') .
' ' . $db->quotename('map_suffix') . ' = ' . $db->quote($suffix) .
' group ' . $db->quotename('term') .
' order ' . $db->quotename('term') . ' desc'
);
$db->query();

to:
$db->setquery(
'insert ' . $db->quotename('#__finder_links_terms' . $suffix) .
' (' . $db->quotename('link_id') .
', ' . $db->quotename('term_id') .
', ' . $db->quotename('weight') . ')' .
' select ' . (int) $linkid . ', ' . $db->quotename('term_id') . ',' .
' round(sum(' . $db->quotename('context_weight') . '), ' .
' ' . $db->quotename('#__finder_tokens_aggregate') .
' ' . $db->quotename('map_suffix') . ' = ' . $db->quote($suffix) .
' group ' . $db->quotename('term') . ', ' . $db->quotename('term_id') .
' order ' . $db->quotename('term') . ' desc'
);
$db->query();

ok, appreciate above might bit of mouth-full can @ least tell me _finder_terms table , @ point should populated?