Search fails to function

[Regular Joe]

I have also been having trouble with the search function, geting the "no entries to print" result. I have looked over the forums and found the following

So the FULLTEXT index on (title, body, extended) wasn't created.
I created them manually but per column means I created von FULLTEXT index for body, one for extended and one for title.
And because of the seperated indexes the search function does not work when the index on those columns is seperated.

I had than to alter the table and add the rest of the columns, set back the old search function and - voilá - it works again.

Can someone perhaps explain this for the layman? i don't see an index as described in my installtion. [/i]

[Regular Joe]

I neglected to mention that I am running php 4.3.9 and mySQL 3.22.32. I also assume you are taling about the table serendipity_entries.

Here is an image of what it looks like in PHPmyadmin;




Thanx for any input. I spent a bunch of time customizing Serendipity before i realized that the search was broken.

Regualr Joe

[tadpole]

Try running

Code: Select all

CREATE FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended);

If that doesn't work try manually running the search query in phpMyAdmin and let me know if it gives you an error (and if so, what it is). The search query is...

Code: Select all

                    SELECT
                            e.id,
                            e.author,
                            a.username,
                            a.email,
                            ec.categoryid,
                            e.timestamp,
                            e.comments,
                            e.title,
                            e.body,
                            e.extended,
                            e.trackbacks,
                            e.exflag
                    FROM
                            serendipity_entries e,
                            serendipity_authors a,
                            serendipity_entrycat ec
                    WHERE
                            a.authorid = e.authorid
                            AND e.id = ec.entryid
                            AND MATCH(title,body,extended) AGAINST('whatever you want to search for')
                            AND isdraft = 'false'
                            AND timestamp <= " . time() . "
                    GROUP BY e.id
                    ORDER BY
                            timestamp DESC

[Regular Joe]

Hi,

Thanx for the reply. I ran the first code snippet (to create the index)in mySQL via telnet and got the following error:

ERROR 1064: You have an error in your SQL syntax near 'FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended)' at line 1

Thanx for any additional input.

Regualr Joe



[/code]

[tadpole]

What version of MySQL are you using? This query requires MySQL >= 3.23.23. I suppose that should be documented somewhere... and checked internally before we try to use it ;)

[Little Hamster]

He's running 3.22.32. By the way, the SQL query works for me (mysql 4.0.20), but I'm not sure the result is correct (I got no rows returned). What does the SELECT e.id, e.author, a.username, a.email, .. mean? I thought you need to refer to them with full table names, like s9y_entries.id, s9y_entries.author, etc.

[tadpole]

Look at the FROM clause--we alias the entries table to e, entrycat to ec, etc.

I don't see why that query (create index) would return a result.

[Regualr Joe]

So I'm screwed if i don't upgrade mySQL?

If so I would respectfully suggest that a project admin DOCUMent the fact that a specific version is required. Teh requirments pages does not specify a mySQL version. It would have saved a lot of wasted time. This is all to typical (and the classic mistake) of open source projects, if I may be so bold.

Any other suggestions? or is it upgrade or nothing?

Regular Joe

[tadpole]

I wouldn't exactly call not being able to search "screwed", but whatever. I'll add a check to the search code so other people don't have as much difficulty as you finding the problem, but unless the problem is in a critical query I don't think we'll be adding that version as a requirement. I'll also add the option of using google as a fallback if the check fails.

If you don't want to upgrade, just use the HTML nugget plugin to add a google search box.

[CapriSkye]

Try running

Code: Select all

CREATE FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended);

when i run that query it says #1214 - The used table type doesn't support FULLTEXT indexes

im running version 0.8-alpha3, and MySQL 4.1.7. anyone know how to make this work? thanks

[garvinhicking]

Did you set your table type to something different than MyISAM? That's usually default in MySQL...

You can see the table type in tools like phpMyAdmin.

Regards,
Garvin.

[CapriSkye]

my default type was set to InnoDB, i've changed to myisam, and ran create fulltext index query, the search function seems to work, except im not getting any result even though the result is there. weird...

[Guest]

i had the same problem:

the search also found 1 entry, but didn´t show it

i tried the command which you described above:

CREATE FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended);


... and now: it works!


thanx a lot for this thread!

[SurfDude]

i had the same problem:

the search also found 1 entry, but didn´t show it

i tried the command which you described above:

CREATE FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended);


... and now: it works!


thanx a lot for this thread!

Also I had the same problem and running the command fixs the problem.

[david]

it's ok for you guys.. you probably know what you are doing. To get the search function working this is what i did. It might help someone as stupid as me
I'm running s9y v.0.7.1 mysql UbuntuLinux all on localhost.

#msyql -u root ----> log in to mysql.. may need password
mysql>\u serendipity ----> change to serendipity d/base
msyql>CREATE FULLTEXT INDEX entry_idx on serendipity_entries (title,body,extended);
-----> run that pesky command
mysql>\q -----> get the hell out of dodge

then my search worked fine.

Ok for you guys to say "run" a command.. but run it where? it isn't obvious if you haven't used mysql before