Search does not work in 0.8.5 with MySQL 3.23

[heddesheimer]

Hi,

found a strange thing: I installed 0.8.5 on a webspace using MySQL 3.23

When I try to find an article with the search-form I don't get any result, even if the words are in the articles. The funny part is: I installed 0.8.4 some days ago for one of my customers and upgraded to 0.8.5 where the search works fine on the web but not in my local testing environment (I tested the same MySQL Version locally).

So it seems to be a problem with mySQL 3.23, which is the only option I have at the moment if I don't want to change my host.

I nailed it down to this:

Code: Select all

SELECT *
 FROM serendipity_entries e 
 WHERE match (title, body, extended) against ('Test');

does not find anything.

But this:

Code: Select all

 select * 
 FROM serendipity_entries e 
 where concat(title, body, extended) like ('%Test%');

finds the two existing articles without problems.

I have already looked at the table structure and I found a correctly defined fulltext key. The create command that I have exported from the existing table looks like this:

Code: Select all

CREATE TABLE serendipity_entries (
  id int(11) NOT NULL auto_increment,
  title varchar(200) default NULL,
  timestamp int(10) unsigned default NULL,
  body text,
  comments int(4) unsigned default '0',
  trackbacks int(4) unsigned default '0',
  extended text,
  exflag int(1) default NULL,
  author varchar(20) default NULL,
  authorid int(11) default NULL,
  isdraft enum('true','false') NOT NULL default 'true',
  allow_comments enum('true','false') NOT NULL default 'true',
  last_modified int(10) unsigned default NULL,
  moderate_comments enum('true','false') NOT NULL default 'true',
  PRIMARY KEY  (id),
  FULLTEXT KEY entry_idx (title,body,extended),
  KEY date_idx (timestamp),
  KEY mod_idx (last_modified)
) TYPE=MyISAM;

So do you know if there is a known problem with MATCH AGAINST when using MySQL 3.23? Should I implement a workaround in the code or can you suggest another solution?

Marian

[MySchizoBuddy]

yup it doesn't
the search requires Mysql 4.*
try using google search plugin

[heddesheimer]

Thanks.

So what about a little switch inside the search function:

Code: Select all

        $mysql_version = serendipity_db_query('select version() as version');
        if ((int)$mysql_version[0]['version'] == 3)
        {
            // M.H. Hack
            // hot-fix because my MySQL 3.23 don't work with MATCH correctly
            $find_part = "concat(title, body, extended) like '%$term%' ";
        } else {
            $find_part = "MATCH(title,body,extended) AGAINST('$term')";
        }

This should solve the problem. Maybe it would be a good idea, to put the mySQL Version somewhere into as systemwide variable just like the PHP-Version?

Marian

[garvinhicking]

Serendipity is preparedfor MySQL 4.0+, so it's basically just luck that it even works with 3.23

I am sorry, but including this hack for supporting a very old mysql version is IMHO not really good.Fulltextsearching with your method is also extremely slow, so I don'treally want to support DOS on theserver:)

Regards,
Garvin

[heddesheimer]

thanks for the clarification. I should have known this earlier

I suggest that you put a check into the installation script and issue a warning, if it finds a MySQL version older than 4.0.

Unfortunately, I know a lot of hosts who still use 3.23, so I have to stick with WordPress for those customers

Thanks anyway

Marian

[garvinhicking]

Well, of course I made it sound worse that in it is: Current Serendipity versions only use the FULLTEXT search feature of MySQL 4.0, and no other sole features for that. So you are able to use your hack and have a properly working s9y for everyplace.

Just for future versions of s9y it might easily happen that we require MySQL 4.0 features (which will be announced first, of course).

This is the reason why I am reluctant to include a patch for 3.23, because we are aiming for future versions instead of adding backward compatibility for a really slow performing search under 3.23...

Best regards,
Garvin

[heddesheimer]

Hi Garvin,

thanks for the explanation. I really understand your concern about not supporting old DB-Versions and I hope that most of the hosts will upgrade their databases soon.

I was confused because the docs at mysql said, that MATCH ... AGAINST was introduced in version 3.23.23 so it should work with my older version too.

I will see, if I can live with the workaround until my host will upgrade to MySQL 4.1

Marian

[heddesheimer]

Hi, it's me again.

I have just seen that my host already has MySQL 4 installed. "select version()" reports: 4.0.25-standard

But the search still do not work (nothing will be found). So what could be the problem here?

Marian

[garvinhicking]

Does the query report an error? What's the URL to your blog so I can try it?

Did you try to execute that match against... query in phpMyAdmin?

Best regards,
Garvin

[heddesheimer]

Does the query report an error?

No Error, just no results

What's the URL to your blog so I can try it?

it's not officially online yet. If you need it, I can create an account for you, so that you can access the page.

Did you try to execute that match against... query in phpMyAdmin?

yes I did. I got the same results that I described in my original post.

Maybe the problem is the PHP Client API version for mySQL. My Host is using php 4.3.10 with Client-API version 3.23.49 for mySQL.

Best regards

Marian

[heddesheimer]

The problem ist meanwhile solved. The search worked fine even in MySQL 3.23

Here is an explanation of the problem:

http://faq.nucleuscms.org/item/86

Regards

Marian

[garvinhicking]

Ah, I misunderstood the problem the whole time. What you mention is also contained in our FAQ at http://www.s9y.org/11.html, see the bottom

Best regards,
Garvin