CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
Pages: [1]

Author Topic: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '  (Read 7488 times)

fen

  • I post frequently
  • ***
  • Posts: 216
  • Karma: 13
    • CivicActions
  • CiviCRM version: 3.3-4.3
  • CMS version: Drupal 6/7
  • MySQL version: 5.1/5.5
  • PHP version: 5.3/5.4
v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
April 29, 2007, 04:38:57 pm
I'm getting this error when attempting to upgrade from v1.6 to v1.7:

ERROR 1271 (HY000) at line 282: Illegal mix of collations for operation ' IN '

The line in question is kinda gnarley:

Code: [Select]
UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type IN ('Treffen', 'Cita', 'Tapaaminen', 'Réunion', 'Reunion',
'\Találkozó', 'Riunione', 'Bijeenkomst', 'Spotkanie', 'Reunião', 'Ã\216ntâlnire', 'Möte(n)', 'ลà¸\232à¸\201ิà¸\210à¸\201รรม');

I'm using PHP 5.1.2, Drupal 4.7.6 and MySQL 4.1, and the database and tables are all utf8_unicode_ci

The v1.6 DB began life as v1.5 rev 6825, was upgraded and spent most of its life as  v1.6 rev 8437 and, after initially failing the upgrade to v1.7, was upgraded successfully to v1.6 rev 8703.  Another upgrade (to v1.7) attempt was made from the v1.6 rev 8703 code, but the same error reported above was returned.

Any ideas/help are greatly appreciated.

Thanks,
=Fen

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
April 29, 2007, 05:40:50 pm

Fen:

check:

http://www.nabble.com/Re%3A--civicrm-mail--Sql-upgrade-problem-tf3609106s15986.html#a10084672

From the post:

If you need this error resolved ASAP, you can just comment/delete this
query (it’s only relevant for people who run non-English versions of
CiviCRM).

I'm not sure of the specifics of the error and/or fix. Piotr is on vacation and  will fix this when he gets back

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Piotr Szotkowski

  • I live on this forum
  • *****
  • Posts: 1497
  • Karma: 57
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
May 07, 2007, 03:24:10 am
Quote from: fen on April 29, 2007, 04:38:57 pm
I'm getting this error when attempting to upgrade from v1.6 to v1.7:

ERROR 1271 (HY000) at line 282: Illegal mix of collations for operation ' IN '

The line in question is kinda gnarley:

Code: [Select]
UPDATE civicrm_activity_history SET activity_type = 'Meeting'    WHERE activity_type IN ('Treffen', 'Cita', 'Tapaaminen', 'Réunion', 'Reunion',
'\Találkozó', 'Riunione', 'Bijeenkomst', 'Spotkanie', 'Reunião', 'Ã\216ntâlnire', 'Möte(n)', 'ลà¸\232à¸\201ิà¸\210à¸\201รรม');

I'm using PHP 5.1.2, Drupal 4.7.6 and MySQL 4.1, and the database and tables are all utf8_unicode_ci

Hm, this shouldn’t happen with an Unicode-collated table. Can you send the pre-upgrade database dump my way (shot@civicrm.org) so I can see whether I can find out what’s wrong?

And yes, you can simply drop the queries from the upgrade script if you are using an English site.
If you found the above helpful, please consider helping us in return – you can even steer CiviCRM’s future and help us extend CiviCRM in ways useful to you.

emilyf

  • Ask me questions
  • ****
  • Posts: 696
  • Karma: 54
  • CiviCRM version: 2.x - 4.x
  • CMS version: Drupal 5, 6, 7
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
May 07, 2007, 02:28:05 pm
I am also getting this same error on my upgrade, so I would be interested in the reasoning behind it. If you'd like me to send a copy of my dump, let me know and I can send it your way (I didn't do any skip opts on the backup so it's going to be long!). Thanks, Emily

Piotr Szotkowski

  • I live on this forum
  • *****
  • Posts: 1497
  • Karma: 57
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
May 07, 2007, 11:52:59 pm
Quote from: emilyf on May 07, 2007, 02:28:05 pm
I am also getting this same error on my upgrade, so I would be interested in the reasoning behind it.

The reasoning is that, most probably, either the database tables are not Unicode or MySQL does not treat the upgrade file as Unicode one. I’m thinking on a workaround for this.

Quote from: emilyf on May 07, 2007, 02:28:05 pm
If you'd like me to send a copy of my dump, let me know and I can send it your way (I didn't do any skip opts on the backup so it's going to be long!).

Please do send it to me, to shot@civicrm.org
If you found the above helpful, please consider helping us in return – you can even steer CiviCRM’s future and help us extend CiviCRM in ways useful to you.

Piotr Szotkowski

  • I live on this forum
  • *****
  • Posts: 1497
  • Karma: 57
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
May 08, 2007, 12:08:03 am
Emily, Fen: I edited the upgrade script and dropped the UTF-8 characters from it; please test whether it fixes the problems on your end: http://svn.civicrm.org/branches/v1.7/sql/civicrm_upgradedb_v1.6_v1.7_41.mysql
If you found the above helpful, please consider helping us in return – you can even steer CiviCRM’s future and help us extend CiviCRM in ways useful to you.

emilyf

  • Ask me questions
  • ****
  • Posts: 696
  • Karma: 54
  • CiviCRM version: 2.x - 4.x
  • CMS version: Drupal 5, 6, 7
Re: v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '
May 21, 2007, 08:58:28 am
Piotor,

Thanks, I will check this out on as soon as I can get to it. Will post back. Emily
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • v1.6 -> v1.7 problem: Illegal mix of collations for operation ' IN '

This forum was archived on 2017-11-26.