Joomla Search Plugin produced error 500

[seagul30]

Hello,

I have Fabrikar 3.4.2 and also for the Joomla search plugin the newest version 3.4.2.
As soon as i active the fabrik search plugin my search results throw an error 500. When deactivated the fabrikar plugin then the search works.

I have setup everything and checked twice as in the wiki http://fabrikar.com/forums/index.php?wiki/search-plug-in/
I have the details->filter->elements set to text-fields
I have enabled the search for the list.
I have published the plugin.
I have Artio JoomSEF - if I disable JoomSEF the results are working.

Any ideas?

Thanks
Sigrid

 

[cheesegrits]

If you set J! error reporting to maximum, does it give you any informative error msgs?

-- hugh

 

[seagul30]

I had to change the template temporarily to get the error reporting.
Here it is:

500 Fabrik has generated an incorrect query for the list Unternehmen: <br /><br /><pre>Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation 'match' SQL=SELECT DISTINCT `fab_unternehmen`.`id` AS __pk_val0, `fab_referenzen`.`id` AS __pk_val1 FROM `fab_unternehmen` LEFT JOIN `fab_region` AS `fab_region` ON `fab_region`.`id` = `fab_unternehmen`.`un_region` LEFT JOIN `fab_referenzen` AS `fab_referenzen` ON `fab_referenzen`.`ref_un` = `fab_unternehmen`.`id` LEFT JOIN `fab_unternehmen` AS `fab_unternehmen_0` ON `fab_unternehmen_0`.`id` = `fab_referenzen`.`ref_un` LEFT JOIN `x42vc_content` AS `x42vc_content` ON `x42vc_content`.`id` = `fab_referenzen`.`ref_produkt` LEFT JOIN `fab_region` AS `fab_region_0` ON `fab_region_0`.`id` = `fab_referenzen`.`ref_region_id` WHERE ( MATCH(`fab_unternehmen`.`un_firmenname`, `fab_unternehmen`.`un_region`) AGAINST ('test*' IN BOOLEAN MODE) ) ORDER BY `fab_unternehmen`.`un_region` ASC, `fab_unternehmen`.`un_firmenname` ASC,`fab_unternehmen`.`un_firmenname` ASC</pre>

There is something wrong with the table collations? Is it?
How can I change this?

Thanks!
Sigrid

 

[cheesegrits]

The error tells you exactly what the problem is. Your tables have different collation. You need to use your database client of choice, and make sure they are all set to the same thing. Search the forums for "Illegal mix of collations" and you'll find threads discussing this.

This isn't a "Fabrik thing", it's a MySQL thing. You can't join / query on tables with different collations, as the data won't match.

-- hugh

 

[seagul30]

Hi,
my three fabrikar tables had all database engine type myISAM all other had InnoDB.

If I change the three fabrikar tables to InnoDB I get a new error code:

Fabrik has generated an incorrect query for the list Unternehmen: <br /><br /><pre>The used table type doesn't support FULLTEXT indexes SQL=SELECT DISTINCT `fab_unternehmen`.`id` AS __pk_val0, `fab_referenzen`.`id` AS __pk_val1 FROM `fab_unternehmen` LEFT JOIN `fab_region` AS `fab_region` ON `fab_region`.`id` = `fab_unternehmen`.`un_region` LEFT JOIN `fab_referenzen` AS `fab_referenzen` ON `fab_referenzen`.`ref_un` = `fab_unternehmen`.`id` LEFT JOIN `fab_unternehmen` AS `fab_unternehmen_0` ON `fab_unternehmen_0`.`id` = `fab_referenzen`.`ref_un` LEFT JOIN `xxxx_content` AS `xxxx_content` ON `xxxx_content`.`id` = `fab_referenzen`.`ref_produkt` LEFT JOIN `fab_region` AS `fab_region_0` ON `fab_region_0`.`id` = `fab_referenzen`.`ref_region_id` WHERE ( MATCH(`fab_unternehmen`.`un_firmenname`, `fab_unternehmen`.`un_region`) AGAINST ('dach*' IN BOOLEAN MODE) ) ORDER BY `fab_unternehmen`.`un_region` ASC, `fab_unternehmen`.`un_firmenname` ASC,`fab_unternehmen`.`un_firmenname` ASC</pre>

Hm, what if I change the whole database to myISAM or just the content-table?

Any other suggestions?

Thanks!!!

 

[cheesegrits]

Don't use InnoDB. Fabrik only supports MyISAM.

But that has nothing to do with the issue. You changed the storage engine. That has nothing to do with collation.

Google your error message "Illegal mix of collations" and you will find a wealth of answers about what collation and character sets are, and how to change your tables so they have the same settings.

You have a mix of utf8_general_ci and latin1_swedish_ci on your tables. You need to change them so they both have the same character set and collation. Which character set and collation you use is up to you, but we typically suggest UTF8, unless you have specific requirements for something different.

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE 'utf8_general_ci';

You should probably also check what your database is set to use by default when creating new tables.

Picking an article at random from a Google search, here's a good one ...

http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/

-- hugh

 

[seagul30]

hm, actually all my database tables as well as the database itself have collation "utf8_general_ci" (see attachment) - I checked all lines.
Still the error message is:

Fabrik has generated an incorrect query for the list Unternehmen: <br /><br /><pre>Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation 'match' SQL=SELECT DISTINCT `fab_unternehmen`.`id` AS __pk_val0, `fab_referenzen`.`id` AS __pk_val1 FROM `fab_unternehmen` LEFT JOIN `fab_region` AS `fab_region` ON `fab_region`.`id` = `fab_unternehmen`.`un_region` LEFT JOIN `fab_referenzen` AS `fab_referenzen` ON `fab_referenzen`.`ref_un` = `fab_unternehmen`.`id` LEFT JOIN `fab_unternehmen` AS `fab_unternehmen_0` ON `fab_unternehmen_0`.`id` = `fab_referenzen`.`ref_un` LEFT JOIN `xxxxx_content` AS `xxxxx_content` ON `xxxxx_content`.`id` = `fab_referenzen`.`ref_produkt` LEFT JOIN `fab_region` AS `fab_region_0` ON `fab_region_0`.`id` = `fab_referenzen`.`ref_region_id` WHERE ( MATCH(`fab_unternehmen`.`un_firmenname`, `fab_unternehmen`.`un_region`) AGAINST ('dach*' IN BOOLEAN MODE) ) ORDER BY `fab_unternehmen`.`un_region` ASC, `fab_unternehmen`.`un_firmenname` ASC,`fab_unternehmen`.`un_firmenname` ASC</pre>

What I did:
I check - like in the link from your post - with the following results
all my tables are utf8_general_ci, but
character_set_server --> latin
collation_server --> latin

My Joomla Installation (System information) says:
Db version 5.5.47-0+deb7u1-log
Db collation utf8_general_ci

However, I cannot change the server settings as I am on a shared host.

Any other idea?

 

[troester]

Which element types with which settings are your searched elements `un_firmenname` and `un_region`

 

[seagul30]

un_firmenname: type= field
un_region: type= databasejoin
settings see in attachments.

I studied the SQL Statement again.
I really only need the un_firmenname (companyname) and description (Beschreibung) for the content search - I don't need the joined table for the region for the joomla search result.
So, the plugin really only needs to lookup in one table

Can I change this anywhere?

 

[troester]

No idea why it's including region into the search although it's not selected as search element.
Did you change this element selection at some point?
Can you clear Joomla and browser cache?

Can you check in region element setting "List view settings/Filter" if it's set to "include in search all"?

 

[cheesegrits]

I'm not sure why both elements are being included in that search, but I think that's a separate issue. At least one of the tables in that query has a different collation. It could be any in the joins. When doing a 'match' search, MySQL looks at all the tables referenced in the query (the main 'from' and all the joins) and will throw that error if they do not all have the same character set and collation. If it's not one of your Fabrik data tables, it could be the #__content table.

Even though you are on a shared host, you should still be able to change the character set and collation of tables in your database. Your host support should be able to help you.

-- hugh

 

[troester]

I think I can replicate.

Although all my database tables and all table columns and the database itself are set (and I think always have been set) to utf8_general_ci I get the same error if a numeric column is included in the search elements and "Extended Search all" or J! search (which are searching "in boolean mode") is enabled for this list.
As far as I can see it's not an issue of the table collation but of the single columns.

It doesn't even help to set the MySQL-Server variables (which were set to latin1_swedish_ci) to utf8_general_ci on my local MySQL server (I don't know if I've found all of them).

Fabrik is excluding columns/elements like id and date-time automatically from "in boolean mode" search, but it isn't excluding other numeric columns like yesno or dbjoin labels which are numeric themselves.

See also http://fabrikar.com/forums/index.php?threads/how-can-we-fix-database-collation-issues.43225/

 

[cheesegrits]

Funnily enough I just came looking for this thread to say the same thing. I was researching this last night, and found threads on the MySQL forums and on Stack Overflow, about this very misleading error msg. It's apparently been fixed in the most recent version, and you get a much more informative error.

This is a tough one to fix, as we really have no way of knowing the underlying column type, without actually querying for the table structure, which can be a very expensive operation. Not so bad on virtual or dedicated servers, but because it has to access the schema table, which is shared by all databases, on shared hosts it can take many seconds when you have a thousand sites crammed on a box all sharing the same MySQL. We know what we think the type should be, but as soon as "alter fields" is turned off, or a type gets changed underneath us, or someone didn't press "Save" on the "update database format?" after changing an element type, we have no clue.

I'll put it on the list to look at next time I'm in that code, and we can at least do a better job of excluding elements we have good reason to believe are INT. But for now we'll just have to be aware that "illegal mix of collations" can mean "you are using an INT in boolean mode".

-- hugh

 

[seagul30]

Thanks for investigating but what should I tell my client as the search is not working?
Is there any chance I can tell J! or fabrikar to look only in the fields I definitely need?

Sigrid

 

[troester]

You must make sure that only "text" elements are included in the Joomla search in ALL lists with J! search enabled.

I really only need the un_firmenname (companyname) and description (Beschreibung) for the content search

Check the selected elements, your search doesn't seem to search for description but for region.

 

[seagul30]

You know what! If I add region to the search element - it actually works

I was trying to add and remove it again. And before removing it I tested by chance and it worked.