MySQL crash when requesting word that is not in the index and encoding is ucs2_unicod

[SABsearch2]

MySQL crash when requesting a keyword that is not in the index and the database encoding is ucs2_unicode_ci

I'm using :
- phpdig version 1.8.8
- easyPhp with Apache 1.3.33, Php 4.3.10, MySQL 4.1.9
on a windows 2000 station (development of the site).

I've made a few changes to my site and wanted to generate a new index. I had problem with the install.php so I've choosen a manual creation of the database.

The spidering was ok but I had to declare a lot of exclusion since the html interface refused the extension of the site name http://www.mydomain.com/MDM and tried to index http://www.mydomain.com.

The search went all right and the results were correct.

Yet, wenether I'm trying to request the word "phpdig" (to control the search engine was not indexed) Mysql is crashing. Instead of a result page I have a message :

Fatal error: Maximum execution time of 30 seconds exceeded in j:\sab\search\phpdig\libs\search_function.php on line 600

It's the execution of an SQL instruction. So I've tried to pass it to the database using phpadmin. Here is the instruction I've executed :

SELECT keyword FROM keywords WHERE SOUNDEX(CONCAT('Q',keyword)) = SOUNDEX(CONCAT('Q','phpdig')) LIMIT 500

I've tried on the new database (named SABsearch) and on the old one (named phpdig).

On the old database the result was as follow :

MySQL a réponduocumentation
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (ucs2_unicode_ci,COERCIBLE) for operation '='

I’ve changed the encoding for the interface and I had a result.

On the new one it ends up with MySQL crashing. I've tried to search another word : "toto". I was sure it was not in the index. MySql crashed too. I've tried with a word that is in the index and MySQL crashed too.

I've choosen ucs2_unicode_ci for the new database and the old one was using latin1_swedish_ci (I don't know why, I'm not the one that has created it).

Have you ever encounter this problem ? Should I change the database encoding ? Which one should I choose exactly ? I will be indexing document in french and english.

Sincerely.

[SABsearch2]

English :
There is no more crash when you use MySQL 5.0.22.

The one wanting to upgdrade have to know that there is a change in the password management. You should either ask MySQL to use the old password encoding or you shoud upgrade our PHP clients. I have choosen upgrading the clients. I will post the result about using PHP5.

The one not wanting to upgrade should use utf8_unicode_ci. It's the default encoding under MySQL 5.0.22 and PHP5.

Français :
Pour ceux qui veulent upgrader il y a une différence dans la gestion des mots de passe entre 4.* et 5.*. Il faut soit demander Ã* MySQL de gérer les mots de passe Ã* l'ancienne mode, soit upgrader les clients PHP... Je n'ai pas essayé le truc des mots de passe. J'ai upgradé tout (je suis encore en développement). Je posterai mes remarques sur PHP5 plus tard.

Pour ceux qui ne veulent pas upgrader, il suffit de passer en codage utf8_unicode_ci. En plus c'est le codage par défaut sous MySQL 5.0.22 et sous PHP5.