Welcome, Guest
Username: Password: Remember me

TOPIC: Database charset is using latin1, while limesurvey uses utf8

Database charset is using latin1, while limesurvey uses utf8 11 months 1 week ago #101026

  • hotsun
  • hotsun's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Karma: 0
I didn't notice that I need to set database charset to utf8 for limesurvey, and all users' Chinese input became "???" on the database. I tried to convert the data but either of them work:
select CONVERT(CONVERT(CONVERT(576941X16X538SQ002 USING utf8) USING binary) using latin1) from `lime_survey_576941_bak` WHERE id=42;
select CONVERT(CONVERT(CONVERT(576941X16X538SQ002 USING latin1) USING binary) using utf8) from `lime_survey_576941_bak` WHERE id=42;

This one didn't work either:
mysql> alter table lime_survey_576941_bak2 modify column 576941X16X538SQ002 text CHARACTER SET binary;
Query OK, 6825 rows affected (0.08 sec)
Records: 6825 Duplicates: 0 Warnings: 0

mysql> alter table lime_survey_576941_bak2 DEFAULT CHARSET=utf8,modify column 576941X16X538SQ002 text CHARACTER SET utf8;
Query OK, 6825 rows affected, 5 warnings (0.08 sec)
Records: 6825 Duplicates: 0 Warnings: 5

Is there any way to get back the original users' input Chinese data?
Thanks in million!
Last Edit: 11 months 1 week ago by hotsun.
The administrator has disabled public write access.

Database charset is using latin1, while limesurvey uses utf8 11 months 1 week ago #101030

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
Rather try eporting the data using a tool like phpMyAdmin, then user a proper text editor to convert the charset so you can see and check the result immediately, then import again.
In the worst case scenario the upper bit range of the characters got lost. Best case scenario is that you manage to get it converted.
Support us, too. Donate to the LimeSurvey project and help keep us going!
Last Edit: 11 months 1 week ago by c_schmitz.
The administrator has disabled public write access.

Database charset is using latin1, while limesurvey uses utf8 11 months 6 days ago #101038

  • hotsun
  • hotsun's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Karma: 0
It doesn't work. All Chinese are still question marks "???".
Did the data format broken when inserting the data in this case? :(
The administrator has disabled public write access.

Database charset is using latin1, while limesurvey uses utf8 11 months 6 days ago #101055

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
Yes, that's most likely :-(. I am really sorry.
Was this a new install or an old installation updated to a newer version?
Support us, too. Donate to the LimeSurvey project and help keep us going!
The administrator has disabled public write access.

Database charset is using latin1, while limesurvey uses utf8 10 months 4 weeks ago #101338

  • hotsun
  • hotsun's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Karma: 0
it was a new installation. We lost lots of user data sadly...
The administrator has disabled public write access.

Database charset is using latin1, while limesurvey uses utf8 10 months 3 weeks ago #101339

  • c_schmitz
  • c_schmitz's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 789
  • Thank you received: 113
  • Karma: 92
Somebody must have changed the database charset because usually at installation the default charset of the database it set to UTF-8.
Maybe the database was moved or restored.
Support us, too. Donate to the LimeSurvey project and help keep us going!
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.103 seconds
Donation Image