Details
-
Type:
Bug
-
Status:
Resolved
-
Priority:
N/A
-
Resolution: Cannot Reproduce
-
Affects Version/s: 0.9.2
-
Fix Version/s: 1.11.4
-
Component/s: Zend_Db, Zend_Db_Adapter_Mysqli
-
Labels:None
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 "<br>"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $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 "<br>"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $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 "<br>"; /////////////////////////////////////////////////////////////////////////////////////////////////////////////// $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 "<br>"; for ($i=0; $i < 5; $i++) { print "<hr>"; print "POS=".$i; print "<br>"; print "SUBSTR_ZF=".substr($blob_data1, $i, 1).", ".ord(substr($blob_data1, $i, 1)); print "<br>"; print "SUBSTR_MYSQL=".substr($blob_data2, $i, 1).", ".ord(substr($blob_data2, $i, 1)); print "<br>"; print "SUBSTR_MYSQLI=".substr($blob_data3, $i, 1).", ".ord(substr($blob_data3, $i, 1)); print "<br>"; 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
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/5.0/en/string-type-overview.html