Zend_Db And LONGBLOBs - Problems...?

cr0sh · #1 (**permalink**) 05-10-2007, 05:25 PM

Hello all, new user of these forums here...

I am using Zend Framework 0.9.2 in a project for my employer. I am connected to a MySQL DB using the Zend Framework and MYSQLI:

Code:

$this->db_dbase = Zend_Db::factory('MYSQLI', $params);

I have found that in a particular table, which has a field of type LONGBLOB, that when there is data in this field, Zend behaves in a strange manner. I am selecting a record from this table in the following manner:

Code:

    $sub_table = new subscribers();
    $where = array("subscriber_id = '9'");
    $values = $sub_table->fetchRow($where);

Depending on which record I select (ie, by changing the record ID pointed to by the where clause), I get different results:

On one record, the LONGBLOB field is empty, and no errors occur.
On a different record, there is data in the LONGBLOB field, but depending on how I view/refresh the page containing the above selection code segment (and depending on what page I view before it!), I sometimes get an error (in the Zend Loader class), and sometimes I don't - however, the error is repeatable.
On yet another record, where there is data in the LONGBLOB field, when I refresh the page things go really haywire - to the point of SEGFAULTing on the server - throwing all sorts of strange errors, in a seemingly random fashion.

I would love to post the data contained in the LONGBLOB fields, but each of the two instances that fail the data is over 1K in length (one is near 2K, the other, 4K) - I don't know if it is the data in the field, or the length of the data, or something else. The field was originally populated with large amounts of $_SESSION variable data - it doesn't seem to be encoded or anything (I would have to dig through some old code to find out exactly how it was stored - from the look of the data, it just looks like a straight dump into the field).

I plan on just eliminating the data from the field, or dropping the field entirely from the table, which should fix the issue for me. I wanted to post this in case anybody else what experiencing similar issues. I am not sure if this issue affects the current version of the Zend Framework (0.9.3) or not, as I have yet to load it into the system and test.

I hope this helps someone out there...

cr0sh · #2 (**permalink**) 06-05-2007, 06:16 PM

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

SpotSec · #3 (**permalink**) 06-06-2007, 12:23 AM

hmm, I'm clueless... perhaps posting on the mailing list will have more intelligent responses than me.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode