1

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 Source out.sql;

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)

Settings:

| 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
  1. Is it possible to generate a smaller file containing the SQL statements that generated the warnings?

  2. 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 grep that 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)?

  3. Given that I have lost the original data - any ideas on overall recovery strategy?

  • The only theory I have is that it's trying to represent a binary data blob as utf – Narrim Dec 20 '16 at 13:27
  • Also I was concerned that I caused the issue by piping mysqldump output – Narrim Dec 20 '16 at 13:28
  • I have confirmed it was indeed the method of backup that corrupted the data. I reviewed a backup from 2 weeks ago and it has non of the problems importing. It was also backed up by mysqlbackup but on windows based machine. – Narrim Dec 20 '16 at 23:45
  • So given I now have a healthy database from 2 weeks ago, but a functional but corrupted database from yesterday I want to remove the corrupt records from the recent data using the backup from 2 weeks ago. – Narrim Dec 20 '16 at 23:46
  • @Michael-sqlbot turns out the issue here is that BINARY data fields can not (almost ever) be UTF8 encoded. hence when you dump using mysqldump (which doesnt change the data or encode it) it will dump fine HOWEVER when you use source <data.sql> it will read the file with the encoding in this case UTF8 - it doesnt change the data - but reports if it finds data that doesnt match the encoding hence the warning. – Narrim Dec 21 '16 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.