Database charset is using latin1, while limesurvey uses utf8

Mehr
5 Jahre 11 Monate her - 5 Jahre 11 Monate her #101026 von hotsun
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!
Letzte Änderung: 5 Jahre 11 Monate her von hotsun.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

LimeSurvey Partners
Mehr
5 Jahre 11 Monate her - 5 Jahre 11 Monate her #101030 von c_schmitz
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.

Best regards

Carsten Schmitz
LimeSurvey project leader
Letzte Änderung: 5 Jahre 11 Monate her von c_schmitz.

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Mehr
5 Jahre 11 Monate her #101038 von hotsun
It doesn't work. All Chinese are still question marks "???".
Did the data format broken when inserting the data in this case? :(

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Mehr
5 Jahre 11 Monate her #101055 von c_schmitz
Yes, that's most likely :-(. I am really sorry.
Was this a new install or an old installation updated to a newer version?

Best regards

Carsten Schmitz
LimeSurvey project leader

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Mehr
5 Jahre 11 Monate her #101338 von hotsun
it was a new installation. We lost lots of user data sadly...

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Mehr
5 Jahre 11 Monate her #101339 von c_schmitz
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.

Best regards

Carsten Schmitz
LimeSurvey project leader

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Mehr
1 Monat 2 Wochen her #188419 von melanje555
Hi,

I was able to overcome different charset issue using Python and PYODBC.
I had entries in English, Russian and Korean.

I am pretty sure you can change it to MySQL connection.
Hope this helps somebody)

#
import pandas as pd, numpy as np
import pyodbc
conn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
"Server=NXNXNXNXNX;"
"Database=LIMESURVEY;"
"Trusted_Connection=yes;"
"unicode_results=True;"


)



conn.setdecoding(pyodbc.SQL_CHAR, 'utf-8')

SQL="""SELECT [submitdate]
,[lastpage]
,[startlanguage]
,[token]

,[21692X233X4470]

,[21692X263X4963]
,[21692X263X4964]

FROM [LIMESURVEY].[dbo].[lime_survey_21692]


df = pd.read_sql(SQL, conn)
conn.close()

Bitte Anmelden oder Registrieren um der Konversation beizutreten.

Jetzt loslegen!

Melden Sie sich jetzt an, und erstellen Sie in wenigen Minuten Ihre erste Umfrage.

Account einrichten

Abonnieren Sie unseren Newsletter

Abonnieren Sie unseren Newsletter für alle Neuigkeiten rund um LimeSurvey
captcha