Search not working

[Ken]

Hi
I moved a website with Sweetcron to a new hosting doing this:

1) Installing Lifepress
2) Copying Database
3) Setting some things (webcron, ecc.)

Now I have a problem: when I use search function I have an error.
Searching ABC:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'AGAINST('ABC') AS score FROM (`items`) LEFT OUTER JOIN `feeds`
ON `feeds`.`feed_' at line 1

SELECT *, MATCH(item_title, `item_content)` AGAINST('ABC') AS score
FROM (`items`) LEFT OUTER JOIN `feeds` ON `feeds`.`feed_id` =
`items`.`item_feed_id` WHERE `item_status` = "publish" AND
MATCH(item_title, item_content) AGAINST('ABC') ORDER BY `score` desc

How do I fix?
Thank you in advance,
Ken

[Carsten]

On 19 Jun., 00:58, Ken <blazike...@gmail.com> wrote:
> How do I fix?
Hm, this looks like you're using a version of MySQL that isn't
supported with Sweetcron or Lifepress. The MATCH...AGAINST is
available since version
MySQL 3.23.23, but I don't know if you have to turn indexing on in the
configuration... Take a look at http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html,
maybe that will help you start the fix.

[Ken]

On 21 Giu, 07:23, Carsten <carsten.ri...@gmail.com> wrote:
> On 19 Jun., 00:58, Ken <blazike...@gmail.com> wrote:> How do I fix?
>
> Hm, this looks like you're using a version of MySQL that isn't
> supported with Sweetcron or Lifepress. The MATCH...AGAINST is
> available since version
> MySQL 3.23.23, but I don't know if you have to turn indexing on in the

> configuration... Take a look athttp://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html,

> maybe that will help you start the fix.

Thank you but exactly what do I have to fix? What file?

[Carsten]

On Jun 21, 4:49 am, Ken <blazike...@gmail.com> wrote:
> Thank you but exactly what do I have to fix? What file?

Ah, sorry. I thought you were already digging in the code to bring up
the SQL statement.

First I have to say, that if you're using a MySQL server without the
full text abilities needed for MATCH...AGAINST, there is no fix for it
- you have to configure the MySQL server right.

But let's look at the code, assuming that there really is a fix to the
statement that will work for you.

A quick code search on the Lifepress project at
http://www.google.com/codesearch?q=AGAINST+package:http://lifepress\.googlecode\.com&origq=AGAINST&btnG=Search+Trunk
brings only 3 results. The first and large are files from the Code
Igniter library, the 2nd might be the one we're looking for.

I think line 166 is the one causing your trouble:
http://www.google.com/codesearch/p?hl=en#iMcFnzoy-nI/system/application/models/item_model.php&q=AGAINST%20package:http://lifepress%5C.googlecode%5C.com&sa=N&cd=2&ct=rc&l=166

This line is inside the function that returns items after a search.
Maybe you need to rewrite the statement to avoid the MATCH...AGAINST
and use a more straightforward "SELECT * WHERE item_title LIKE '%$this-
>db->escape($query)%'". That won't give you a good matching on parts
of the words and I guess it will be much slower than using the
MATCH...AGAINST that works on a full text index that is build by the
server itself.

Hope that helps.

[Ken]

Well, from PHPMyAdmin I can set indexes as FULLTEXT.
I'll try rewriting the line.
Thank you :)

On 22 Giu, 06:00, Carsten <carsten.ri...@gmail.com> wrote:
> On Jun 21, 4:49 am, Ken <blazike...@gmail.com> wrote:> Thank you but exactly what do I have to fix? What file?
>
> Ah, sorry. I thought you were already digging in the code to bring up
> the SQL statement.
>
> First I have to say, that if you're using a MySQL server without the
> full text abilities needed for MATCH...AGAINST, there is no fix for it
> - you have to configure the MySQL server right.
>
> But let's look at the code, assuming that there really is a fix to the
> statement that will work for you.
>

> A quick code search on the Lifepress project athttp://www.google.com/codesearch?q=AGAINST+package:http://lifepress\.googlecode\.com&origq=AGAINST&btnG=Search+Trunk

> brings only 3 results. The first and large are files from the Code
> Igniter library, the 2nd might be the one we're looking for.
>

> I think line 166 is the one causing your trouble:http://www.google.com/codesearch/p?hl=en#iMcFnzoy-nI/system/applicati...

[Ken]

Ah, I forgot.
From cPanel:
MySQL version 5.0.90-community

> > Hope that helps.- Nascondi testo citato
>
> - Mostra testo citato -

[Carsten]

On Jun 22, 1:08 pm, Ken <blazike...@gmail.com> wrote:
> MySQL version 5.0.90-community
Hm, that should be more than enough to support the MATCH...AGAINST I
think. I don't know about the configuration settings yet, but I'd say
the problem lays on the MySQL side... otherwise more users would have
issues with the search.

Maybe you can drop the indexes and rebuild them somehow? I suspect
that while moving the database to the new host the indexes go
corrupted or at least confused so they won't match the real database
entries anymore. I know that phpBB3 has a feature to drop and rebuild
the indexes, but I think it's a basic MySQL function.

[Ken]

I tried deleting indexes and recreating them but the problem remains.
I also tried modifying the line 166 of item_model.php but I really
don't know how to modify it.

[Carsten]

On the MySQL manual pages there is a hint:

Full-text indexes can be used only with MyISAM tables, and can be
created only for CHAR, VARCHAR, or TEXT columns.

Can you check the type of your tables?

And there is a tool to check the index: http://dev.mysql.com/doc/refman/5.0/en/myisam-ftdump.html

Still I believe that this is a problem on the database side, not
inside the Lifepress code.

[Carsten]

One more question: Did you go through the setup process or did you
just copy the database over to the new host? I'd recommend going
through the setup and later inserting the data into the created
database... maybe that helps with the MySQL setup if everything else
fails.

[Ken]

I went through the setup process.
I just tried reinstalling lifepress with a new database and without
importing data and it gives me the error.

[Ken]

"item_content" is longtext, is this the problem?

On 24 Giu, 06:23, Carsten <carsten.ri...@gmail.com> wrote: