|
If you were logged in you would be able to see more operations.
|
 Google issue summary
|
|
|
|
Time Tracking:
|
|
Original Estimate:
|
2 days
|
|
|
Remaining Estimate:
|
2 days
|
|
|
Time Spent:
|
Not Specified
|
|
|
|
| Fix Version Priority: |
Should Have
|
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;
$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
|
|
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;
$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
|
Show » |
|
Bug
Open
Major
Zend_Db and LONGBLOBs
Reference on MySQL text/blob datatypes:
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