The question is probably a little vague so let me elucidate:
- System OS is Ubuntu Linux 14.10 LTS
- The Servers default encoding is Latin1
- The database default encoding is/was UTF8
- The data was dumped by piping
mysqldump > out.sql
On re-importation back into the same engine into a database created with:
CREATE DATABASE test DEFAULT CHARACTER SET utf8;
it was re-imported using a simple
As a result a number of :
Warning (Code 1300): Invalid utf8 character string: '9CD4BB'
... warnings during importation.
I have tried using
-verbose on the MySQL client, but it produces a large quantity of unnecessary information (
out.sql is around 845MB)
| character_set_client | utf8 | character_set_connection | utf8 | character_set_database | latin1 | character_set_filesystem | binary | character_set_results | utf8 | character_set_server | latin1 | character_set_system | utf8 | character_sets_dir | /usr/share/mysql/charsets/ | collation_connection | utf8_general_ci | collation_database | latin1_swedish_ci | collation_server | latin1_swedish_ci
Is it possible to generate a smaller file containing the SQL statements that generated the warnings?
My assumption here is that during the data dump the data was converted to latin1 and saved as
out.sql. There are characters in latin1 that cannot be represented under utf8. It is possible to convert them back, or at least a
grepthat can show me a small amount of the offending text - remembering that I think grep returns the entire line (and sql exports have no hard coded line endings)?
Given that I have lost the original data - any ideas on overall recovery strategy?