[support] search errors

[Mohammed Al-shar']

hi Laura and all.

I have fixed this collation issue by altering the defected tables to utf_general_si by running the following sql commands one by one:
ALTER TABLE files_backup CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE mailman_lists CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE mailman_users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE node_comment_statistics CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE queue CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE search_index CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
ALTER TABLE search_total CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

those were the tables that needed to be fixed in my specific case. 

thanks.
Mohammed Al-shar'
  ----- Original Message ----- 
  From: Laura Scott 
  To: support at drupal.org 
  Sent: Friday, February 16, 2007 9:25 PM
  Subject: Re: [support] search errors


  http://drupal.org/search/node/database+collation


  The top several hits seem to have discussion on this very topic.


  Laura


  On Feb 16, 2007, at 11:46 AM, Mohammed Al-shar' wrote:


    hi Laura!

    you are right. "it looks like your tables in your database have varying collations." I am almost possitive this is the problem, but I don't know what the default collation is for drupal, especially that my site has arabic, which might be an issue here. I know for certain that whatever collation is the default works, because whenever I setup a new drupal site I have no problem. this particular site was transformed from host to another, and from mysql 4.0 to mysql 5.27. there's a known issue with utf when upgrading. I solved that in part, it seems, because my site performs so well aside from this search problem. how do I change the collation?  it looks it's not an easy task for someone like me, who's not a mysql expert.

    thanks.

    Mohammed Al-shar'
      ----- Original Message ----- 
      From: Laura Scott 
      To: support at drupal.org 
      Sent: Friday, February 16, 2007 6:27 PM
      Subject: Re: [support] search errors


      I am not a mysql expert, but it looks like your tables in your database have varying collations. They should all be using the same collation. utf8_general_ci has worked for us, but maybe either would be fine. I don't know. 


      There are several forum discussions on ways you can fix this, which some Googling should bring up for you. You will also want to make sure your default collation matches, so that when you install new modules and/or updates, you don't end up with mixed tables again.


      Again, I am not a mysql expert, so I may be wrong here.


      Laura




      On Feb 16, 2007, at 4:23 AM, Mohammed Al-shar' wrote:


        hi.

        I am noticing a very wierd problem at my website, each time I try to search, either as guest or authenticated user, I get the following error:

        • user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: do_search CREATE TEMPORARY
        TABLE temp_search_results SELECT i.type, i.sid, 5 * (9.71305588655 * i.relevance) + 5 * POW(2, (GREATEST(n.created, n.changed, c.last_comment_timestamp)
        - 1171617940) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + c.comment_count * 0.111111111111)) + 5 * (2.0 - 2.0 / (1.0 + nc.totalcount * 0.00061804697157)) AS score
        FROM temp_search_sids i INNER JOIN search_dataset d ON i.sid = d.sid AND i.type = d.type INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics
        c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE (d.data LIKE '% auda %') ORDER BY score DESC in /home/nattiqc/public_html/includes/database.mysql.inc
        on line 172. 
        • user warning: Table 'nattiqc_nattiq.temp_search_results' doesn't exist query: do_search SELECT COUNT(*) FROM temp_search_results in /home/nattiqc/public_html/includes/database.mysql.inc
        on line 172. 

        I tried to "reindex" but that didn't work.  I read in the forums about a similar wierd issue but it wasn't about search in particular.

        any clues?

        the site url is: www.nattiq.com you may want to see www.nattiq.com/en as the default language is arabic.

        Regards,

        Mohammed Al-shar'







--------------------------------------------------------------------------



      -- 
      [ Drupal support list | http://lists.drupal.org/ ]
    -- 
    [ Drupal support list | http://lists.drupal.org/ ]




------------------------------------------------------------------------------


  -- 
  [ Drupal support list | http://lists.drupal.org/ ]
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20070217/0a20bd71/attachment.htm