How can we fix database collation issues?

[ArjanM]

Hi,

when we have a standard Joomla installation which incorporates Fabrik and we transfer the site to a Ubuntu Server environment, we get database collection error when using Fabrik.

500 filter query error: generator Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation... (etc. etc.)

The Joomla website itself runs without any problem. How can we fix this problem in the most efficient way?

-- Arjan.

 

[troester]

Do you have lists with "Extended search all" or Joomla Search?
And "numeric" elements in "Elements"/Select in list's Filters setting?

See also http://fabrikar.com/forums/index.php?threads/joomla-search-plugin-produced-error-500.43168/

 

[cheesegrits]

Could you include the actual query, that would help figure out the issue.

As per the thread troester pointed out, this is usually when you are doing a "boolean mode" search, either in advanced search on a list, or with the Fabrik J! search plugin, and your tables/columns either have different character sets and/or collation, or you are including a non-text field (like INT) in the search.

-- hugh

 

[ArjanM]

Will check it out... Here's the full query:

500 filter query error: generator Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>=' SQL=SELECT DISTINCT(CONCAT_WS('', `generatoren_0`.generator_description, ' - ', (SELECT generator_stationed FROM generatoren_standplaatsen WHERE id = `generatoren_0`.generator_stationed))) AS `text`, `generatoren_data`.`generator` AS `value` FROM `generatoren` LEFT JOIN `generatoren_vermogens` AS `generatoren_vermogens` ON `generatoren_vermogens`.`id` = `generatoren`.`generator_power` LEFT JOIN `generatoren_standplaatsen` AS `generatoren_standplaatsen` ON `generatoren_standplaatsen`.`id` = `generatoren`.`generator_stationed` LEFT JOIN `generatoren_materiaal` AS `generatoren_materiaal` ON `generatoren_materiaal`.`id` = `generatoren`.`generator_material` LEFT JOIN `generatoren_synchronisatie` AS `generatoren_synchronisatie` ON `generatoren_synchronisatie`.`id` = `generatoren`.`generator_synchronisation` LEFT JOIN `generatoren_data` AS `generatoren_data` ON `generatoren_data`.`generator` = `generatoren`.`id` LEFT JOIN `generatoren` AS `generatoren_0` ON `generatoren_0`.`id` = `generatoren_data`.`generator` LEFT JOIN `uq2ib_users` AS `uq2ib_users` ON `uq2ib_users`.`id` = `generatoren_data`.`executor` LEFT JOIN `generatoren_materiaal` AS `generatoren_materiaal_0` ON `generatoren_materiaal_0`.`id` = `generatoren_data`.`material` LEFT JOIN `uq2ib_users` AS `uq2ib_users_0` ON `uq2ib_users_0`.`id` = `generatoren_data`.`applicant` WHERE `generatoren_data`.`generator` IN ('8','9','21','44','45','46','47') AND `generatoren_0`.id NOT IN (SELECT generator FROM generatoren_data WHERE '' >= generatoren_data.startdate AND '' <= generatoren_data.enddate) ORDER BY CONCAT_WS('', `generatoren_0`.generator_description, ' - ', (SELECT generator_stationed FROM generatoren_standplaatsen WHERE id = `generatoren_0`.generator_stationed))ASC LIMIT 0, 100

-- Arjan

 

[troester]

Where is " ...WHERE '' >= generatoren_data.startdate AND '' <= generatoren_data.enddate..." coming from, a prefilter?
Which DB column types are startdate and enddate?
What should the empty '' be?

 

[cheesegrits]

Yeah, that WHERE clause makes no sense.

-- hugh

 

[ArjanM]

 

[cheesegrits]

Right, but where is that filter coming from ...

 WHERE '' >= generatoren_data.startdate AND '' <= generatoren_data.enddate)

... and what should be those blank quotes?

-- hugh

 

[ArjanM]

I do not know, but it is the query that comes from the "Data where"-tab (belonging to the element 'generatoren_data___generator'). This is the query:

WHERE {thistable}.id NOT IN (SELECT generator FROM generatoren_data WHERE '{generatoren_data___startdate}' >= generatoren_data.startdate
AND '{generatoren_data___enddate}' <= generatoren_data.enddate)

It's purpose is to list only those generators that do not already have a reservation.

-- Arjan

 

[cheesegrits]

This is probably something you just need to add to that scope of work we have on Google Docs.

-- hugh

 

[ArjanM]

I've added it to the document.

-- Arjan