Illegal mix of collations

[Soma]

I got this error on a website a couple times, I've never seen anything like that ever.

Since the error isn't really helpful to reproduce it, I'm lost to why and where this did happen. I guess it's the search function as it happened severeal times in a row for couple hour.

Page: http://somedomain.fr/?/
User: guest

Error
Uncaught exception 'WireDatabaseException' with message 'Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' in /var/www/domains/processwire/latest/wire/core/Database.php:118
Stack trace:
#0 /var/www/domains/processwire/latest/wire/core/DatabaseQuery.php(84): Database->query(Object(DatabaseQuerySelect))
#1 /var/www/domains/processwire/latest/wire/core/PageFinder.php(145): DatabaseQuery->execute()
#2 /var/www/domains/processwire/latest/wire/core/Pages.php(144): PageFinder->find(Object(Selectors), Array)
#3 [internal function]: Pages->___find('/fr-fr/rapport_...', Array)
#4 /var/www/domains/processwire/latest/wire/core/Wire.php(271): call_user_func_array(Array, Array)
#5 /var/www/domains/processwire/latest/wire/core/Wire.php(229): Wire->runHooks('find', Array)
#6 [internal function]: Wire->__call('find', Array)
#7 /var/www/domains/processwire/latest/wire/core/Pages.php(213): Pages->find('/fr-fr/rapport_...', Array)
#8 /var/www/domains/processwire/latest/wire/ (line 118 of /var/www/domains/processwire/latest/wire/core/Database.php)  

[DaveP]

It's a complete guess, but all bar one (field_pass) of the tables I see in a local install are 'utf8_general_ci' collation. It looks from your post that the error relates to alternate language pages. Is a module creating the wrong collation on its tables?

[Soma]

Thanks Dave, no there's no language or modules with other collation. It would then also happen more often.

[teppo]

Not sure what could be causing this, but judging from that error it would seem that either one of your tables has ascii_general_ci collation. It could also be just one column, which is also possible with MySQL.. or it could be a result of a query forcing a collation, which would seem quite unlikely in PW.

Anyway, I'd start by checking if there's anything odd with the query it's trying to do, something related to pages->find() within /fr-fr/rapport_... etc. Could something on your site be sending non-UTF-8 characters to database or..? 

[Soma]

The only table in PW that is ascii_general_ci is the password table and the "name" fields in tables. There's nothing else evident.

The strange thing is there's no find code in all of the templates except the search. (Yes this is possible ).

$matches = $pages->get("/".$langpath ."/")->find("title|body|headline1|headline2%=$q, limit=50"); 

The path exists yes, but I can't make up what call is causing it because it looks like it failing on maybe some char in url or when someone did a search with strange characters?

The last stackstrace is Database 118 is when a result is empty.

Also the first line is what makes it hard to find at least what page it occoured. Or is that the homepage?

Page: http://somedomain.fr/?/
User: guest

[apeisa]

Put some logging right before that find, save time and $q at least. When you get that exception look what you have on for that time on your log. Might give us some clue.

[SiNNuT]

-What exactly is going into the $langpath variable? It's a long-shot but could the path given to $pages->get be somehow compromised?

-What database queries are getting performed? Any joining or other cross-table/column operation can give problems when the collations don't match..

I'm no expert in interpreting (PW) exceptions but http://somedomain.fr/?/ just seems strange to me.

Not sure if relevant to the problem but couldn't all tables and columns have the same collation?

[ryan]

-What database queries are getting performed? Any joining or other cross-table/column operation can give problems when the collations don't match..

We have always used ASCII on fields within tables that are for page names (or other names that can't ever contain anything UTF8). Though the tables themselves are always UTF8, even if an individual field within might be defined as ASCII collation. Can you see any potential problems with doing that? Maybe some MySQL version or setting doesn't like this?

I'm no expert in interpreting (PW) exceptions but http://somedomain.fr/?/ just seems strange to me.

The "/?/" means the error occurred before PW determined what the current $page was going to be. 

Not sure if relevant to the problem but couldn't all tables and columns have the same collation?

Everything is UTF8, except for "name" fields. They certainly could be UTF8 too, but figured it would be a better practice and more efficient to enforce ASCII on a field where only ASCII characters allowed. But it sounds like maybe there are issues with doing that I'm not aware of? Though if that were the case, I would also wonder why MySQL would let you change the character set on a field-by-field basis if it wasn't okay to do. This is the first "Illegal mix of collations" error I've heard of in PW.

[SiNNuT]

I'm a bit out of my depth here Ryan. Maybe there's something funky going on with some of the data in this particular site of Soma. If i look here http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_coercibility IMPLICIT would be the column value and COERCIBLE the literal string, so i thought maybe somehow a utf8 string is being passed as a page name. I don't know what the effects would be, but maybe Soma's error? Maybe i'm thinking too simple.

Some imported data maybe, or the stuff that goes into $langpath? Wonder if Soma has gotten any further insights into this error.

Some further light reading on this subject:

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

http://www.bluebox.net/about/blog/2009/07/mysql_encoding/

[Soma]

So, back with the result of my investigation.

I finally found the problem. As the data in the website is mostly imported from a file html export we get from another company. Turns out they had special chars in a file name (even after telling them for the third time). I didn't notice until now.

002.coñt_de_lendettement_financier_net.htm

The imported page is ok, as the name generated does replace ñ.

Now there a link in tables that would open a modal loading the body of this page, and the url from he exported html was:

002.coût_de_lendettement_financier_net.htm

The path to this page goes to a ajax.php bootstraped and there's a wire("pages")->get(path..) and calling it with the û it throws the error I posted.

[ryan]

Thanks for reporting back on this. Glad to hear you resolved it. 

[apeisa]

I'm getting exactly same error on one site. For me it already knows the page (/http404/).

I don't see the stack trace on error emails though: is there other way to enable those than setting debug mode on?

[ryan]

In order to get a stack trace you either have to have debug mode on or be logged in as a superuser.