| Key: |
ZF-1498
|
| Type: |
Bug
|
| Status: |
In Progress
|
| Priority: |
N/A
|
| Assignee: |
Unassigned
|
| Reporter: |
Andrew L. Ayers
|
| Votes: |
4
|
| Watchers: |
3
|
|
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
|
|
|
|
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 » |
Sort Order:
|
Zend_Db and LONGBLOBs
Reference on MySQL text/blob datatypes:
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