Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#310 closed Bug (fixed)

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

Reported by: matt Owned by:
Priority: major Milestone: RobotRock
Component: Core Keywords:
Cc: Sensitive:

Description

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.

Change History (6)

comment:1 Changed 6 years ago by matt (mattab)

  • Milestone changed from Future features to DigitalVibes

comment:2 Changed 6 years ago by matt (mattab)

  • Milestone changed from DigitalVibes to RobotRock

comment:3 Changed 5 years ago by matt (mattab)

  • Resolution set to fixed
  • Status changed from new to closed

(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

comment:4 Changed 5 years ago by matt (mattab)

(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

comment:5 Changed 5 years ago by matt (mattab)

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

comment:6 Changed 5 years ago by alivenk

Note: See TracTickets for help on using tickets.