SQL error #1271 - Illegal mix of collations for operation 'match'

[sn00ze]

Hi,
I have turned Extended search all = YES

Now the SQL query is AND -ing the search terms...as required, but I get this error:

#1271 - Illegal mix of collations for operation 'match'

the SQL that's the problem is:
FROM `new_entry`
WHERE ( ( (`new_entry`.`category` = '10' OR `new_entry`.`category` LIKE '[\"10\"%' OR `new_entry`.`category` LIKE '%\"10\"%' OR `new_entry`.`category` LIKE '%\"10\"]') AND MATCH(`new_entry`.`business_name`, `new_entry`.`category`, `new_entry`.`suburb_city`, `new_entry`.`meals_served`, `new_entry`.`type_of_cuisine`, `new_entry`.`other1`) against ('Belconnen*' IN BOOLEAN MODE)) ) LIMIT 0, 5

is this a BUG?

PS: i'm using fabrik 3.1rc2

 

[troester]

Which element plugins are used for the "search all" elements?
This error may occure if an element (database column) isn't text (eg varchar, text,...) but date or int (->dbjoin elements, field as integer/decimal) etc.

 

[sn00ze]

Thanks for the input troester. All fields in the MATCH are either text or varchar(255)

business_name - varchar(255)
category - text
suburb_city - varchar(255)
meals_served - text
type_of_cuisine - varchar(255)
other1 - varchar(255)

I noticed in the DB that some rows use Collation - latin1_swedish_ci, and some utf8_general_ci
could this be a problem?

 

[sn00ze]

Solved.
Changed Collation of all fields in MATCH to be 'latin1_swedish_ci' and
SQL error #1271 gone.

Don't know why there was a mix of collations in the TABLE created by fabrik?

 

[troester]

Did you set list's "Advanced" option "Collation"?
I assume this will change table collation (+ collation of new collumns) but keep collation of existing columns.

If not:
can you remember which columns (element plugins) had the "wrong" collation?

 

[sn00ze]

Troester, yes the lists ->Advanced -> collation was set to utf8_general_ci

looking at the table, it seems that most columns used latin1_swedish_ci
it is only a few that used utf8_general_ci

Looks to be plugin independant. I have some 'Field' plugins that are utf8_general_ci, and some latin1_swedish_ci