Databases
StackOverflow2013
Postsdbo
POExported blob data different than DB data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExported blob data different than DB data
    text
    copied!<p>I'm attempting to export our very large MySQL database (1.6GB - mostly BLOB) and import into a new server. I have worked through most of the problems and finally completed the import without any errors. Using MySQL Query Browser I ran a query on a table with BLOBs of images and saved one to disk (using the save icon in query browser). When I tried to open the file I received an "invalid image format" error. Uh oh.</p> <p>Using query browser I inspected the value on the source database and the new, recently imported database. The values are different, I think. It could just be encoding issues or something. Here is what I see:</p> <p>Source (valid data) server:</p> <pre><code>FF D8 FF E0 00 10 4A 46 49 46 00 01 01 01 00 60 00 60 00 00 FF DB 00 43 00 08 06 06 07 06 05 08 and so on... </code></pre> <p>New server:</p> <pre><code>C3 BF C3 98 C3 BF C3 A0 00 10 4A 46 49 46 00 01 01 01 00 60 00 60 00 00 C3 BF C3 9B 00 43 00 08 and so on... </code></pre> <p>In this example it appears to my newbie eye that there are 3 bytes of extra data in the front of the data on the "new" server.</p> <p>I then checked out the sql dump file using <a href="http://www.sweetscape.com/010editor/" rel="nofollow">010 editor</a>. I found the line for this particular example and here is what I see:</p> <pre><code>FF D8 FF E0 5C 30 10 4A 46 49 46 5C 30 01 01 01 5C 30 60 5C 30 60 5C 30 5C 30 FF DB 5C 30 43 5C 30 08 06 06 07 06 05 08 and so on... </code></pre> <p>Now I'm way over my head. I see the pattern, I do notice that the HEX pair 5C 30 appears to be the same as 00 but I don't understand WHY. At this point I have a source server that is about to be wiped and a new one that I'm afraid has corrupt data imported into it. I'm hoping this is some kind of encoding issue that can be solved by setting a global variable in MySQL but I really don't know.</p> <p>I should also mention that when I save the files from the source (working) server and the new (corrupt) server the files size is about 40% larger for the corrupt file.</p> <p>I checked the character set variables on both servers:</p> <pre><code>SHOW VARIABLES LIKE '%char%' </code></pre> <p>source server:</p> <pre><code>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 </code></pre> <p>new server:</p> <pre><code>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 </code></pre> <p>They are the same.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload