Recover original data saved as "???????" from mysql database

Question

While saving the information to the database field the settings were as follows:

character set: latin1
collation: latin1_swedish_ci

Once the users updated the information,i noticed that many information were saved as "?????????????? ??????????????? ???? ?????" (Question marks) to the database. English(language) information were saved without questions marks. All other languages were saved with question marks.

Now i have changed the character set to utf8 and all the languages are saved properly.

The problem is that i need to recover the information which is saved as question marks. eg:"?????????????? ??????????????? ???? ?????"

XAMPP Version: 5.6.19
Navicat version 11.0.17

Database server
Server version: 10.1.10-MariaDB - mariadb.org binary distribution

Web server
Apache/2.4.17 (Win32) OpenSSL/1.0.2d PHP/5.6.19 PHP version: 5.6.19

phpMyAdmin
Version information: 4.5.1

Please help me to get the original data.

Do you mean question marks in a square? Or normal question marks like you wrote? — A. Blub, Aug 11 '17 at 07:16
okay... sorry but there are no difference between ? and ?... And i dont think that any Post-Data is logged. Seems like you have lost this data... — A. Blub, Aug 11 '17 at 07:22

score 0 · Answer 1 · answered Aug 11 '17 at 07:21

0

MySQL replaced those characters with question marks when they were inserted into the database. The original data is lost forever -- there is no way to recover it.

answered Aug 11 '17 at 07:21

score 0 · Answer 2 · answered Aug 11 '17 at 11:14

0

Check your php/apache/codeigniter logs for database inserts/updates - maybe you will find there the original data which was inserted.

answered Aug 11 '17 at 11:14

Alexandru

14
2

score 0 · Answer 3 · answered Aug 11 '17 at 12:04

This happened because of the database collation. In this particular case it is latin1_swedish_ci.

Actually, the collation is a set of characters that can be used across the fields of the tables in a database. If you try to use a character that is not in the character set of this collation, MySQL replaces it with a question mark, because it does not know what this character is.

Having said that, there is not way to retrieve your data back. The only way is to scan through some logs of the application or MySQL (if any).

score 0 · Answer 4 · answered Jan 31 '20 at 10:49

0

you have to select UTF-8_general_ci Encoding in type of collation set.

not latin1_swedish_ci

answered Jan 31 '20 at 10:49

Devang Hire

174
1
5

score 0 · Answer 5 · answered Mar 20 '20 at 21:04

i think there is a way fetch data from the database via mysqli::query and also use mysqli::set_charset before selecting (maybe you need to try many times with different charset like utf-8,....) then you can have array of original data then change your table and database collation after all save the data.

Recover original data saved as "???????" from mysql database

5 Answers5