Good evening. My apologies if this has been answered elsewhere, but I could not find it on Google or in the forums. I wish one could search subject lines only; if I'm blind and I've missed that feature, please let me know.
I'm using CONCAT() to assemble a BLOB. Occasionally, about 20% of the time, if I check the length of the object after the concatenation, it is not equal to its previous length plus the chunk I just attached. In other words, I could have a 10,000-byte BLOB, concatenate another 10,000-byte BLOB, and end up with a 19,999-byte BLOB. I've verified that my chunks are the correct size, so I'm pretty confident that the problem is within MySQL. The problem occurs consistenly when dealing with the same file, and happens with every medium-to-large file I've tested.
I'm using a PHP front end, and wrapping my chunks with mysqli_real_escape_string(). Is it possible that certain values are not being correctly escaped? This is the relevant code:
// Divide the file into manageable chunks
$size = filesize($path);
$numChunks = 1 + (int)($size / MAX_CHUNK_SIZE);
$content = array();
for ($chunkNum = 1; $chunkNum <= $numChunks; $chunkNum++)
{
$chunk = file_get_contents($path, FILE_BINARY, null, ($chunkNum-1) * MAX_CHUNK_SIZE, MAX_CHUNK_SIZE);
$content[$chunkNum] = self::cleanForSQL($chunk);
}
self::cleanForSQL() just calls mysqli_real_escape_string(). The first chunk is used to create the object, and then, a few lines later:
// Append any additional chunks
for ($chunkNum = 2; $chunkNum <= $numChunks; $chunkNum++)
{
$cleanContent = $content[$chunkNum];
$sql = "UPDATE Media SET Content = CONCAT(Content, BINARY'$cleanContent') WHERE Media = $mediaID";
self::$db->query_action($sql);
logToFile("After concat, len = " . numToText(self::$db->query_scalar("SELECT LENGTH(Content) FROM Media WHERE Media = $mediaID"))); // Reports 19,999 rather than 20,000
}
I appreciate any ideas!
Jonathan