Now I have run into a new issue, one which others should be able to replicate, and it appears to only affect MySQL fields of type LONGBLOB. Smaller BLOB field types (ie, MEDIUMBLOB, BLOB, etc) do not appear to be affected.
Basically, I created a table to hold BLOBs for uploaded images. So, I have a table with an ID key field, a field to hold the mime type, and a field to hold the BLOB - plus other fields which aren't important or germane to this discussion.
So - when I initially set the field as a LONGBLOB type, I used the Zend Framework to write the data as so:
Code:
$table = new blobs();
$values = array("mime_type" => $mime_type, "blob_data" => $data);
$table->insert($values);
Basic and straightforward, right? This isn't the area of the problem, though - it is in reading the data back that things become "strange":
Code:
$table = new blobs();
$blob_data1 = $table->getAdapter()->fetchOne("SELECT blob_data FROM blobs WHERE blob_id = '$blob_id'");
The data this returns (in $blob_data) is not the same data that is stored. You can go into MySQL and look at the field, and see that the data is stored properly, but when you use the above method to read it, it isn't that same data. In fact, if you use the "regular" PHP MySQL access methods to read in the data:
Code:
$link = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
mysql_select_db(DB_DBNAME);
$result = mysql_query("SELECT blob_data FROM blobs WHERE blob_id = '$blob_id'");
$blob_data2 = mysql_result($result, 0);
The data is returned properly - doing both of these accesses in a row, then doing a character-by-character comparison clearly shows that each is returning a different result:
Code:
for ($i=0; $i < 5; $i++) {
print "<hr>";
print "COMPARE=".$i;
print "<p>";
print "SUBSTR1=".substr($blob_data1, $i, 1).", ".ord(substr($blob_data1, $i, 1));
print "<p>";
print "SUBSTR2=".substr($blob_data2, $i, 1).", ".ord(substr($blob_data2, $i, 1));
}
The "translation" or whatever is happenning doesn't appear uniform - in my data, for instance, I get the following output for the first five bytes:
Quote:
COMPARE=0
SUBSTR1=, 25
SUBSTR2=ÿ, 255
--------------------------------------------------------------------------------
COMPARE=1
SUBSTR1=, 0
SUBSTR2=Ø, 216
--------------------------------------------------------------------------------
COMPARE=2
SUBSTR1=, 0
SUBSTR2=ÿ, 255
--------------------------------------------------------------------------------
COMPARE=3
SUBSTR1=, 0
SUBSTR2=à, 224
--------------------------------------------------------------------------------
COMPARE=4
SUBSTR1=p, 112
SUBSTR2=, 0
|
Notice how ASCII 224
and ASCII 255
both translate to ASCII 0. I do not see this issue, however as I noted above, when the field is a MEDIUMBLOB or smaller field. Only when the field is a LONGBLOB type.
I don't know if this issue and the prior issue are related, but I find it interesting that they both have to do with the same field type. For my current development efforts, I can switch the field to a MEDIUMBLOB type, but it would be handy in the future to be able to use the LONGBLOB type, as I might have BLOBs which are larger than 16Mb in size that I would want to store in the DB.
I am wondering if I am running into an issue involving multi-byte ASCII, Unicode, and locale setting issues. Does anyone else think this may be the case, and if so, how I might correct it? Or, is this really a bug with the Zend Framework? Once again, I find it strange that it only seems to affect the LONGBLOB field type, and none of the other BLOB types, and only on reading the field, not writing it (ZF appears fine in this regard). I suppose it could be an issue with MySQL or how it is set up, but the inconsistency of what I am seeing doesn't seem to support it (although I am looking at it from my developer perspective, and not from a DB admin perspective - I am going to talk to my IT guys about this, too).
If anybody has any clues as to what is going on, I am all ears...