search events in backend - collation issue

[hifi]

Sorry that I haven't been able to test earliar.

Seems that search with ASCII works, but when tried to search with "kevät", got following error:

Code: Select all

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' SQL=SELECT count(*) FROM (jos_jevents_vevent AS ev ) LEFT JOIN jos_jevents_rrule as rr ON rr.eventid = ev.ev_id LEFT JOIN jos_jevents_vevdetail as detail ON ev.detail_id=detail.evdet_id WHERE LOWER(detail.summary) LIKE '%kev�t%'
Warning: Invalid argument supplied for foreach() in C:\wamp\www\CB_testi\administrator\components\com_jevents\controllers\icalevent.php on line 132
DB function failed with error number 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' SQL=SELECT ev.*, ev.state as evstate, detail.*, g.name AS _groupname , rr.rr_id, rr.freq,rr.rinterval ,MAX(rpt.endrepeat) as endrepeat ,MIN(rpt.startrepeat) as startrepeat FROM (jos_jevents_vevent as ev ) LEFT JOIN jos_jevents_vevdetail as detail ON ev.detail_id=detail.evdet_id LEFT JOIN jos_jevents_rrule as rr ON rr.eventid = ev.ev_id LEFT JOIN jos_jevents_repetition as rpt ON rpt.eventid = ev.ev_id LEFT JOIN jos_groups AS g ON g.id = ev.access WHERE LOWER(detail.summary) LIKE '%kev�t%' GROUP BY rpt.eventid LIMIT 0, 10

[Geraint]

I'm not seeing this error - in fact I can search for japanses kanji with no problems. I suspect your database may have tables with different collations setup.

Can you run the query :

"show table status"

on your database in phpmyadmin or something similar and check for inconsistent values in the Collation column

In Joomla 1.5 the database collation should be all UTF8 e.g. utf8_general_ci

Geraint

[hifi]

For jevents tables collation is "latin1_swedish_ci". In my Joomla dev DB there are 3 different collations: latin1_swedish_ci, utf8_bin and utf8_general_ci

Didn't that installation of jevents created those tables? So collation is from there (or did it try to create tables with default collation?

[hifi]

I did change the default collation, but now when searching it changes "kevät" to "kev�t" in search field.

Not sure if this is related to event conversion or this, I get following error when opened any event in frontend:
Notice: Array to string conversion in C:\wamp\www\CB_testi\libraries\joomla\html\parameter.php on line 83

[Geraint]

For jevents tables collation is "latin1_swedish_ci". In my Joomla dev DB there are 3 different collations: latin1_swedish_ci, utf8_bin and utf8_general_ci

Didn't that installation of jevents created those tables? So collation is from there (or did it try to create tables with default collation?

They should all be utf8_bin or utf8_general_ci - the jevents db setup doesn't specify a collation which suggests your default database connection is not set as UTF8 for Joomla. Strange.

Is this a migrated installation or a fresh install of Joomla?

[hifi]

It's backup made with Joomlapack from production site. Maybe there's /was something wrong with my WAMP config.

I'll try next weekend to delete and then reinstall the jevents (and maybe Joomla), and check if that helps.

[Geraint]

Can you check the collations on the production site? Are all the tables there utf8?

Geraint

[hifi]

In production I have all three types of collations too in DB (utf8_bin, utf8_general_ci and latin1_swedish_ci)

I'm not very familiar with setting up DB, but I think these lines tell something:
character set database latin1
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci

[Geraint]

character set database latin1
collation database latin1_swedish_ci

I think these should both be utf-8.

how did you create this Joomla live installation and with what distribution?

[Geraint]

I've just committed a change to administrator/components/com_jevents/controller/config.php that checks if the database supports utf8 and then explicitly specifies the correct charset.

Can you try this file, delete your ical tables and recreate them?

Thanks