| 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

These are the vars if I run SHOW VARIABLES;

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..

Ben

In an attempt to determine what is actually happening and if the backup was truly corrupt....

If I run

cat corruptback.sql  

The output will screw up my console

If I ran 

cat dbbackfrom2weeksago.sql

It outputs normally to console indicating to me the encoding is ok

NOW if I Restore the 2week old backup

now I also then ran 

mysqldbcompare which fails on the documents table with the error

Traceback (most recent call last):
  File "G:\ade\build\sb_0-19921351-1470074463.97\Python-2.7.6-windows-x86-64bit\lib\site-packages\cx_Freeze\initscripts\Console.py", line 27, in <module>
  File "scripts\mysqldbcompare.py", line 413, in <module>
  File ".\mysql\utilities\command\dbcompare.py", line 479, in database_compare
  File ".\mysql\utilities\command\dbcompare.py", line 315, in _check_data_consistency
  File ".\mysql\utilities\common\dbcompare.py", line 1526, in check_consistency
  File ".\mysql\utilities\common\dbcompare.py", line 1290, in _generate_data_diff_output
  File ".\mysql\utilities\common\dbcompare.py", line 1211, in _get_formatted_rows
  File ".\mysql\utilities\common\format.py", line 302, in print_list
  File ".\mysql\utilities\common\format.py", line 197, in format_tabular_list
  File "G:\ade\build\sb_0-16184688-1439901496.7\Python-2.7.6-windows-x86-64bit\lib\encodings\utf_8.py", line 16, in decode
UnicodeDecodeError: 'utf8' codec can't decode byte 0x9c in position 1: invalid start byte

Basically saying it cant decode the table with a UTF8 codec.

HOWEVER I then ran 

SELECT odoc.document_id,
FROM openvpms.documents as odoc 
JOIN test.documents as tdoc 
ON odoc.document_id = tdoc.document_id 
WHERE md5(odoc.contents)=md5(tdoc.contents);

The result was that all 4731 rows in test(uncorruptrestore) matched the suspectrestore.

I also ran compares using length() and char_length()

If I skip the data checks the msyqldbcompare runs without error.

Tim my bottom line question is there anyway that the BLOB fields could contain NON UTF8 chars in a normal openvpms database?

Or could that only happen as a result of extract/dump problems.

So just in summary

a good backup option is

mysqldump -uopenvpms -popenvpms --opt --hex-blob --result-file=openvpms.backup.sql openvpms

then to reimport at a mysql prompt

CREATE Database newSchema DEFAULT CHARACTER SET UTF8;

use newSchema;

source opnevpms.backup.sql;

then I ran seperately from mysqlutilities

mysqldbcompare --server1=root:password@10.1.3.100:3306 --server2=root:password@10.1.3.100:3306 openvpms:newschema --changes-for=server1 --run-all-tests --skip-data-check > output.log

to receive a report like this (please note I skipped data checking as this utility cannot handle BINARY data)

Checking databases openvpms on server1 and test on server2
#
#                                                   Defn    Row     Data   
# Type      Object Name                             Diff    Count   Check  
# ------------------------------------------------------------------------- 
# TABLE     act_details                             pass    pass    SKIP    
# TABLE     act_relationship_details                pass    pass    SKIP    
# TABLE     act_relationships                       pass    pass    SKIP    
# TABLE     action_type_descriptors                 pass    pass    SKIP    
# TABLE     acts                                    pass    pass    SKIP    
# TABLE     archetype_descriptors                   pass    pass    SKIP    
# TABLE     assertion_descriptors                   pass    pass    SKIP    
# TABLE     assertion_type_descriptors              pass    pass    SKIP    
# TABLE     audit_records                           pass    pass    SKIP    
# TABLE     contact_classifications                 pass    pass    SKIP    
# TABLE     contact_details                         pass    pass    SKIP    
# TABLE     contacts                                pass    pass    SKIP    
# TABLE     document_acts                           pass    pass    SKIP    
# TABLE     document_details                        pass    pass    SKIP    
# TABLE     documents                               pass    pass    SKIP    
# TABLE     entities                                pass    pass    SKIP    
# TABLE     entity_classifications                  pass    pass    SKIP    
# TABLE     entity_details                          pass    pass    SKIP    
# TABLE     entity_identities                       pass    pass    SKIP    
# TABLE     entity_identity_details                 pass    pass    SKIP    
# TABLE     entity_link_details                     pass    pass    SKIP    
# TABLE     entity_links                            pass    pass    SKIP    
# TABLE     entity_relationship_details             pass    pass    SKIP    
# TABLE     entity_relationships                    pass    pass    SKIP    
# TABLE     etl_log                                 pass    pass    SKIP    
# TABLE     financial_acts                          pass    pass    SKIP    
# TABLE     granted_authorities                     pass    pass    SKIP    
# TABLE     lookup_details                          pass    pass    SKIP    
# TABLE     lookup_relationship_details             pass    pass    SKIP    
# TABLE     lookup_relationships                    pass    pass    SKIP    
# TABLE     lookups                                 pass    pass    SKIP    
# TABLE     node_descriptors                        pass    pass    SKIP    
# TABLE     participation_details                   pass    pass    SKIP    
# TABLE     participations                          pass    pass    SKIP    
# TABLE     parties                                 pass    pass    SKIP    
# TABLE     product_price_classifications           pass    pass    SKIP    
# TABLE     product_price_details                   pass    pass    SKIP    
# TABLE     product_prices                          pass    pass    SKIP    
# TABLE     products                                pass    pass    SKIP    
# TABLE     roles_authorities                       pass    pass    SKIP    
# TABLE     security_roles                          pass    pass    SKIP    
# TABLE     user_roles                              pass    pass    SKIP    
# TABLE     users                                   pass    pass    SKIP   

# Databases are consistent given skip options specified.
#
# ...done