ZF-1498: Zend_Db and LONGBLOBs

Description

As far as I can tell, this issue is related mainly to Zend_Db, and not Mysqli - however, since I am using Mysqli as my connection, I am including it as a point of reference.

The bug that I am encountering is as follows:

I have a MySQL table for BLOB storage. One of the fields is defined as a LONGBLOB. I can use Zend_Db to write data to this field, but when I go to read it, the data that is returned isn't the same as the data that is stored. I can use both Mysql and Mysqli to read the data, and it appears fine (I can also use the connection object via Zend_Db to access Mysqli directly, and that works OK as well). Reading this data using one of the external methods, and then performing a character-by-character comparison of the data seems to indicate that some form of translation is occurring. Whether this is a bug or by design (ie, there is a flag or parameter I need to set?) is unknown. What I do know is that if I choose to set the field data type to a smaller BLOB data type (ie, MEDIUMBLOB), the issue does not occur.

So, the "quickfix" is only use MEDIUMBLOB (or smaller) data type fields in a table, or use one of the "direct methods" of access if there is no issue with tying your application to a particular DB.

I should also note that I do not know if this issue affects LONGTEXT fields or not. I suspect it may, but it has not been tested for...

Sample testing code:

$id = 11;
// Note - I set the DB up elsewhere, but this code is pretty simple for the test
$table = new blobs();
$blob_data1 = $table->getAdapter()->fetchone("SELECT blob_data FROM blobs WHERE blob_id = $id");

print "ZENDDB BLOB_DATA LEN=".strlen($blob_data1);
print "
"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $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 = $id"); $blob_data2 = mysql_result($result, 0); mysql_close($link); print "MYSQL BLOB_DATA LEN=".strlen($blob_data2); print "
"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $link = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DBNAME); $result = mysqli_query($link, "SELECT blob_data FROM blobs WHERE blob_id = $id"); $data = mysqli_fetch_assoc($result); $blob_data3 = $data["blob_data"]; mysqli_close($link); print "MYSQLI BLOB_DATA LEN=".strlen($blob_data3); print "
"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $result = $table->getAdapter()->getConnection()->query("SELECT blob_data FROM blobs WHERE blob_id = $id"); $data = mysqli_fetch_assoc($result); $blob_data4 = $data["blob_data"]; print "ZENDDB BLOB_DATA4 LEN=".strlen($blob_data4); print "
"; for ($i=0; $i < 5; $i++) { print ""; print "POS=".$i; print "
"; print "SUBSTR_ZF=".substr($blob_data1, $i, 1).", ".ord(substr($blob_data1, $i, 1)); print "
"; print "SUBSTR_MYSQL=".substr($blob_data2, $i, 1).", ".ord(substr($blob_data2, $i, 1)); print "
"; print "SUBSTR_MYSQLI=".substr($blob_data3, $i, 1).", ".ord(substr($blob_data3, $i, 1)); print "
"; print "SUBSTR_MYSQLI2=".substr($blob_data4, $i, 1).", ".ord(substr($blob_data4, $i, 1)); }

Sample output (LONGBLOB):

ZENDDB BLOB_DATA LEN=177669
MYSQL BLOB_DATA LEN=177669
MYSQLI BLOB_DATA LEN=177669
ZENDDB BLOB_DATA4 LEN=177669
--------------------------------------------------------------------------------
POS=0
SUBSTR_ZF=), 41
SUBSTR_MYSQL=ÿ, 255
SUBSTR_MYSQLI=ÿ, 255
SUBSTR_MYSQLI2=ÿ, 255
--------------------------------------------------------------------------------
POS=1
SUBSTR_ZF=, 0
SUBSTR_MYSQL=Ø, 216
SUBSTR_MYSQLI=Ø, 216
SUBSTR_MYSQLI2=Ø, 216
--------------------------------------------------------------------------------
POS=2
SUBSTR_ZF=, 0
SUBSTR_MYSQL=ÿ, 255
SUBSTR_MYSQLI=ÿ, 255
SUBSTR_MYSQLI2=ÿ, 255
--------------------------------------------------------------------------------
POS=3
SUBSTR_ZF=, 0
SUBSTR_MYSQL=à, 224
SUBSTR_MYSQLI=à, 224
SUBSTR_MYSQLI2=à, 224
--------------------------------------------------------------------------------
POS=4
SUBSTR_ZF=¸, 184
SUBSTR_MYSQL=, 0
SUBSTR_MYSQLI=, 0
SUBSTR_MYSQLI2=, 0

Sample output (MEDIUMBLOB):

ZENDDB BLOB_DATA LEN=177669
MYSQL BLOB_DATA LEN=177669
MYSQLI BLOB_DATA LEN=177669
ZENDDB BLOB_DATA4 LEN=177669
--------------------------------------------------------------------------------
POS=0
SUBSTR_ZF=ÿ, 255
SUBSTR_MYSQL=ÿ, 255
SUBSTR_MYSQLI=ÿ, 255
SUBSTR_MYSQLI2=ÿ, 255
--------------------------------------------------------------------------------
POS=1
SUBSTR_ZF=Ø, 216
SUBSTR_MYSQL=Ø, 216
SUBSTR_MYSQLI=Ø, 216
SUBSTR_MYSQLI2=Ø, 216
--------------------------------------------------------------------------------
POS=2
SUBSTR_ZF=ÿ, 255
SUBSTR_MYSQL=ÿ, 255
SUBSTR_MYSQLI=ÿ, 255
SUBSTR_MYSQLI2=ÿ, 255
--------------------------------------------------------------------------------
POS=3
SUBSTR_ZF=à, 224
SUBSTR_MYSQL=à, 224
SUBSTR_MYSQLI=à, 224
SUBSTR_MYSQLI2=à, 224
--------------------------------------------------------------------------------
POS=4
SUBSTR_ZF=, 0
SUBSTR_MYSQL=, 0
SUBSTR_MYSQLI=, 0
SUBSTR_MYSQLI2=, 0

Comments

I just talked to a user who confirms that this issue does affect LONGTEXT as well as LONGBLOB.

The workaround for now is to use MEDIUMTEXT or MEDIUMBLOB, but regardless I'll try to fix this as soon as I can.

Keep in mind that in MySQL, the MEDIUMTEXT/MEDIUMBLOB types hold up to 16MB of data. LONGTEXT/LONGBLOB hold up to 4GB of data. In most apps, the capacity of the MEDIUM types should be adequate! :-)

Reference on MySQL text/blob datatypes: http://dev.mysql.com/doc/refman/…

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

";
var_dump($result);
echo "

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

And 1.5.1 also seems to have this issue. Although I don't get to see an error, I get a blank page. But when reducing the amount of data in the long fields, the query is run fine.

A quick fix is to not select the big fields when doing a quick lookup. But that's not a fix when you need that specific field or when you use e.g. the findDependentRowSet method, as that one takes all fields.

This issue has gone unaddressed for too long. I'm re-assigning to Ralph for re-evaluation and categorization.

I will evaluate this within 2 weeks

This is pretty old, I know the problem exists in the 1.5 series has it been fixed in 1.8?

Guys, this issue is almost two years old. Is it fixed or not?

Hi all,

I cannot reproduce this issue. Please see the attachment above, in there are the steps I took to try and reproduce this. Running this script in both PHP 5.3 and PHP 5.2 does not help. If anyone can help me reproduce it in script form, let me know.

-ralph

Marking unassigned.

I couldn't reproduce either. You can have the issue back.

If nobody is able to reproduce it, it makes sense to mark it as cannot-reproduce, right?

I was also unable to recreate this issue. My guess is that this was a bug at some point but over the past two years some change somewhere down the line inadvertently fixed the problem.

Reassigned to component maintainer