When database collation is not the default, mysql error "Illegal mix of collations"

[matt]

I am using us_ascii as the default character set.

I get the following error on the piwik.php logging script:

Uncaught exception: 'Error query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '=''
More information
In /Users/netmon/Sites/piwik/modules/LogStats/Db.php on line 201
Backtrace:
#0 /Users/netmon/Sites/piwik/modules/LogStats/Db.php(160): Piwik_LogStats_Db->query('?SELECT idactio...', Array)
#1 /Users/netmon/Sites/piwik/modules/LogStats/Action.php(224): Piwik_LogStats_Db->fetch('?SELECT idactio...', Array)
#2 /Users/netmon/Sites/piwik/modules/LogStats/Action.php(95): Piwik_LogStats_Action->loadActionId()
#3 /Users/netmon/Sites/piwik/modules/LogStats/Visit.php(485): Piwik_LogStats_Action->getActionId()
#4 /Users/netmon/Sites/piwik/modules/LogStats/Visit.php(343): Piwik_LogStats_Visit->handleNewVisit()
#5 /Users/netmon/Sites/piwik/modules/LogStats.php(203): Piwik_LogStats_Visit->handle()
#6 /Users/netmon/Sites/piwik/modules/LogStats/Generator.php(661): Piwik_LogStats->main()
#7 /Users/netmon/Sites/piwik/modules/LogStats/Generator.php(369): Piwik_LogStats_Generator->saveVisit()
#8 /Users/netmon/Sites/piwik/misc/generateVisits.php(82): Piwik_LogStats_Generator->generate(12, 15)
#9 {main}

You can get help from http://piwik.org (give us the full error message + your PHP and Mysql version)

Possible solution: Create the piwik tables with a specific character set for all the application, scripts etc.

[matt]

(In [1011]) - fixed #310 When database collation is not the default, mysql error "Illegal mix of collations" now all tables are created with utf8 charset

[matt]

(In [1014]) - cleaning up the search engine parsing code, adding tests, recording UTF8 keywords in the DB rather than encoded (as tables are now utf8, refs #310) - adding tests in url.test.php and fixed double encoding in some edge cases - fixed #589 Piwik fails to properly decode and store some chinese keywords (eg. from baidu.com) - fixed #435 Exotic encoded keywords should be stored as utf-8 in the DB - refs #575 hopefully fixed, will give it a few days of tests on piwik.org

[matt]

(In [1020]) - refs #310 adding updates so that all existing tables and all fields are converted to utf8 for consistent piwik tables