Using the latest version of CVS (040205) and mysql. Converted a 4.5 install to 4.6. Ran cron.php several times to generate a new search_index. That worked fine and the search_index is fully populated with all the words from the various nodes. However, search_total is not being fully rebuilt. For some reason not all the distinct words are making it into search_total. For example:

"SELECT COUNT(DISTINCT(words)) FROM search_index" returns 4641 words, whereas:

"SELECT COUNT(DISTINCT(words)) from search_total" only returns 1889 words.

No search results are returned for any of the 2752 words omitted in the search_total table.

Any ideas on trouble shooting this further. Because there is no ordered index, I'm having trouble pinpointing where the update of the search_total table breaks down.

Thanks in advance,

mitomac

Comments

mitomac’s picture

mitomac commented

I was able to rebuild the search_total table in mysql by deleting the current contents of 'search_total' and regenerating the table using sql. Not sure how this will work over long the long term. Will drupal be able to keep up with new and or deleted nodes?

mitomac

Uwe Hermann’s picture

Uwe Hermann commented

Is this still an issue? Steven?

mitomac’s picture

mitomac commented
Version: » 4.6.1

I just wanted to post a follow up with how I have worked around this problem.

The core of the problem is that in 4.6 the search module only updates search_index after a node is edited. The change is never propagated to search_total. Again this only affects edited nodes and not newly created nodes.

As a work around I have a cron job running that periodically checks and rebuilds the search_total table.

In mysql to see the words in search_index that are not in search_total:

select search_index.word from search_index left join search_total on search_total.word=search_index.word where search_total.word is null group by word;

And to fix the problem:

delete from search_total;
insert into search_total (word,count) select word,sum(score) from search_index group by word;

mikecrowe’s picture

mikecrowe commented
Version: 4.6.1 » 4.6.3

I think I solved this by modifying search.module:

In search_cron(), I added the line:
db_query("insert into search_total (word,count) SELECT i.word, sum(i.score) FROM search_index i LEFT JOIN search_total t ON t.word = i.word WHERE t.word IS NULL group by i.word");

Please LMK comments -- I'm just coming up to speed on this...
Mike

urbanfalcon’s picture

urbanfalcon commented

Worked for me...this should be +1 and put into core. I'll bet it fixes a lot of people's "search is broken" complaints...they just don't know what's wrong so they aren't looking for the right fix.

Steven’s picture

Steven commented

Such a query gets complicated real fast and would slow down cron runs a lot for large sites like drupal.org. Are you sure your cron is simply not timing out or running out of memory? Updating search_total is the final part of search_cron, so it would be an obvious sign.

If you check the code, you can see that what should happen is:

  1. search_cron() is invoked
  2. hook_update_index() is called for all affected modules. Node.module calls search_index($nid, 'node', $content) for any new/modified nodes
  3. search_index() analyses the content, inserts words into table {search_index} and calls search_dirty($word) for each word
  4. when all indexing is done, search_cron() retrieves the list of all new and modified words using search_dirty() and recalculates the total, inserting/updating {search_total} in the process.

Each INSERT/UPDATE to {search_index} is accomodated by a call to search_dirty(), so unless the indexing is stopped prematurely, each word should appear in {search_total}.

AndriusKulikauskas’s picture

AndriusKulikauskas commented

Mitomac, Mike and all,

Thank you very much for chasing this down!

I had the same problem, I think. Finally I chased down the code and realized my index_total table was empty so I searched for "index_total" and found this page. Mitomac, I've used your MySQL statement and now my table has records and the search works! Michael, I added your code to our cron - we have a small site. We'll see how it works.

Andrius Kulikauskas
MInciu Sodas
http://www.ms.lt

magico’s picture

magico commented
Version: 4.6.3 » 4.6.9

Still a problem? Should we create a patch?

magico’s picture

magico commented
Status: Active » Closed (fixed)

To old and to quiet...

gianfrasoft’s picture

gianfrasoft commented
Version: 4.6.9 » 7.22

That's incredible, today is 7 may 2013 and this issue solves my problem on Dtupal 7.22!!!

Why don't you update the core?

baldrs’s picture

baldrs commented
Issue summary: View changes

I've just run into this in 7.34, any updates on how this can be fixed permanently?

r3m’s picture

r3m commented
Version: 7.22 » 7.41
Status: Closed (fixed) » Active

I had the same problem and I have to apply the following query :

delete from search_total;
insert into search_total (word,count) select word,sum(score) from search_index group by word;

After this query, the search results work again. I had this problem on multiple fresh drupal installations

jhodgdon’s picture

jhodgdon
she/her
Primary language English
commented
Version: 7.41 » 4.6.9
Status: Active » Closed (duplicate)
Related issues: +#2367253: [META] Several problems in search_update_totals()

This is most likely a duplicate of one of the issues under #1189484: ER_TOO_BIG_SELECT in search_update_totals().

Look in your log for errors. If you find that the cause is something other than one of the issues listed on that other issue, please open a *new* issue with details of what the SQL or PHP error is that is leading to your problem rather than reopening a vague issue that was closed years ago. Thanks!