MYSQL Error

More
4 years 6 months ago #93371 by jonespr1
jonespr1 created the topic: MYSQL Error
Hi

Following an upgrade of PHP by my host I was forced to upgrade from v1.85+ to v2.00+.

The upgrade seemed to go well, however as soon as I choose a survey I now get this error, so can't access any of my existing surveys.
---
Internal Server Error

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

An internal error occurred while the Web server was processing your request. Please contact the webmaster to report this problem.

Thank you.
---

Any ideas on how to fix?

Thanks

Robert

Please Log in to join the conversation.

More
4 years 6 months ago - 4 years 6 months ago #93375 by DenisChenu
DenisChenu replied the topic: MYSQL Error
Hello,

Did you have access to your DB via a PHP script like phpMyAdmin ?
If yes, have you ability to copy/create another DB (because ALLWAYS make a copy before doing update ;).
If yes: you can update all table:
ALTER TABLE `dbname`.`tablename` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand (or search sondages pro).
An error happen ? Before make a new topic : remind the Debug mode .
Last Edit: 4 years 6 months ago by DenisChenu. Reason: utf8_unicode_ci because default installer use utf8_unicode_ci

Please Log in to join the conversation.

More
4 years 6 months ago #93397 by jonespr1
jonespr1 replied the topic: MYSQL Error
Hi

Thanks for responding, I tried a script which used this approach and it didn't work unfortunately, but had some external support and now fixed.

Thanks

Robert

Please Log in to join the conversation.

More
4 years 1 month ago - 4 years 1 month ago #99120 by nolte
nolte replied the topic: MYSQL Error
Met the same problem upgrading LimeSurvey 1.89 to 2.00plus.
Created tables as well as tablefields had been created in the default collation of mysql: utf8_general_ci.
We fixed the problem by changing the collation of the database and tables by phpmyadmin.
To change the collation of the database "phpsurveyor" and some tables was not enough. We checked the fields of the tables and updated those with collation utf8_general_ci to utf8_unicode_ci.
The following tables were affected and fixed:
lime_defaultvalues
lime_expression_errors
lime_failed_login_attempts
lime_participant_attribute
lime_participant_attribute_names
lime_participant_attribute_names_lang
lime_participant_attribute_values
lime_participant_shares
lime_participants
lime_sessions
lime_survey_links
lime_survey_permissions
lime_survey_url_parameters

After that all surveys worked fine again.
Last Edit: 4 years 1 month ago by nolte.

Please Log in to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now