Tiny PNGs bloat in size when stored as blobs
I have a PHP script trying to store images in a MySQL table as blob. The script works fine and does its job as expected. However, when trying to view the saved image from the table, I noticed that only the top half is being stored; the rest are just cut off! This is what I mean:
Also, I noticed that the image size fills up for no apparent reason while storing it as a blob. The image in question (left) is originally 46kB, but when loaded from a table (right) it shows 36kB (I know BLOBs can take up to 64KB, so this shouldn't be a problem). This happens with smaller images too.
Even funnier is the fact that the file size doesn't look consistent in PHPMyAdmin. Take a look:
This is in tabular view:
This is the insert:
What's happening? Am I doing something wrong? How does a 46KB file turn into 36 in one view and 90 in the other?
As for my max_allowed_packet size , it currently defaults to 4194304, which is much higher than what I'm trying to keep.
UPDATE Here's a PHP snippet inserting an image into the db:
foreach($files as $fname) {
if($fname != '.' && $fname != '..') {
rename($oldfolder.$fname, $newfolder.$fname);
$imgname = array_shift(explode(".",$fname)); //Individual image file name, without extension
echo "\n".$imgname;
$thumbph = fopen($newfolder.$fname, 'rb');
$sql = 'UPDATE distilled_contacts SET THUMB = ? WHERE PHONE = ?';
$connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);
$query = $connect->prepare($sql);
$query->bindParam(1, $thumbph, PDO::PARAM_LOB);
$query->bindParam(2, $imgname);
$query->execute();
$query = NULL;
$sql = NULL;
$connect = NULL;
}
}
The dbconn () function looks like this:
function dbconn($strHost, $strDB, $strUser, $strPass) {
try {
$conn = new PDO("mysql:host=".$strHost.";dbname=".$strDB.";charset=utf8", $strUser, $strPass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_PERSISTENT, true);
return $conn;
}
catch(PDOException $e) {
die("Could not connect to the database $dbname :" . $e->getMessage());
return false;
}
}
UPDATE: In Ryan's query, here's the table structure:
ID int(10) unsigned, auto-increment
PHONE varchar(20), utf8_unicode_ci
POPULARNAME varchar(60), utf8_unicode_ci
PREFERREDNAME varchar(60), utf8_unicode_ci
LATITUDE float(8,6)
LONGITUDE float(9,6)
LASTSEEN datetime
THUMB blob
The default collation for the database utf8_unicode_ci
which I cannot change as I need to support non-European languages.
PHP version: 5.5.20, MySQL version: 5.6.24
source to share
As requested "method" for providing "loading" and "display" of image files:
When "things" get tangled - I believe in "going back to first principles" to provide the "basics".
I have scripts that "work" here with your data. I would like you to ensure that these scripts work as expected.
I have loaded your image into a "blob" with the correct size and rendered it correctly.
It's not “pretty” - they need to work and are easy to test.
Scripts: Upload Image:
<?php // Q30212477_uploading-image.php
session_start();
DEFINE ('BIGGEST_FILE', 64 * 1024);
/*
Table: distilled_contacts
Field Type Collation Null Key Default Extra Privileges Comment
----------- ----------- --------------- ------ ------ ------- -------------- -------------------- ---------
id int(11) (NULL) NO PRI (NULL) auto_increment select,insert,update
PHONE varchar(20) utf8_general_ci NO (NULL) select,insert,update
POPULARNAME varchar(60) utf8_general_ci NO (NULL) select,insert,update
LATITUDE float (NULL) NO (NULL) select,insert,update
LONGITUDE float (NULL) NO (NULL) select,insert,update
THUMB mediumblob (NULL) NO (NULL) select,insert,update
*/
?>
<?php if (!empty($_FILES)): // process the form... ?>
<?php // process the input files...
// start file processing...
/* debug */ // var_dump($_FILES); // show what we got as files...
// database connection...
$dsn = 'mysql:host=localhost;dbname=testmysql';
$username = 'test';
$password = 'test';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$connection = new PDO($dsn, $username, $password, $options);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if (is_uploaded_file($_FILES['thumb']['tmp_name'])) {
$imgThumb = fopen($_FILES['thumb']['tmp_name'], 'rb');
$stmt = $connection->prepare("INSERT INTO distilled_contacts (`PHONE`, `POPULARNAME`,
`LATITUDE`, `LONGITUDE`,
`THUMB`)
VALUES (?, ?, ?, ?, ?)");
$stmt->bindValue(1, $_POST['phone'], PDO::PARAM_STR);
$stmt->bindValue(2, $_POST['popularname'], PDO::PARAM_STR);
$stmt->bindValue(3, $_POST['latitude'], PDO::PARAM_STR);
$stmt->bindValue(4, $_POST['longitude'], PDO::PARAM_STR);
$stmt->bindParam(5, $imgThumb, PDO::PARAM_LOB);
$connection->beginTransaction();
$stmt->execute();
$connection->commit();
fclose($imgThumb);
}
else
echo "Error uploading image";
unset($connection);
?>
<?php endif; // processed the form? ?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Upload images</title>
</head>
<body>
<form action="" method="POST" enctype="multipart/form-data">
<input type="hidden" name="MAX_FILE_SIZE" value="<?php echo BIGGEST_FILE ?>">
<label for="phone">Phone Number</label>
<input type="text" name="phone" id="phone" value="1-800-TESTFILE"><br>
<label for="popularname">Popular Name</label>
<input type="text" name="popularname" id="popularname" value="Jane Doe"><br>
<label for="latitude">Latitude</label>
<input type="text" name="latitude" id="latitude" value="29.9792"><br>
<label for="latitude">Longitude</label>
<input type="text" name="longitude" id="longitude" value="31.1344"><br>
<label for="">Thumb image</label>
<input type="file" name="thumb" id="thumb" value="test_image_Q30212477.png"><br>
<input type="submit" value="Upload file" />
</form>
</body>
</html>
<?php if (empty($_FILES)) {
exit; // leave this script...
} ?>
And: Display Image:
<?php // index.php
// https://stackoverflow.com/questions/30212477/tiny-pngs-bloating-up-in-size-when-stored-as-blob#30212477
// !!! Change this id for your record!!!
$ID = '1';
// database connection...
$dsn = 'mysql:host=localhost;dbname=testmysql';
$username = 'test';
$password = 'test';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$connection = new PDO($dsn, $username, $password, $options);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// query
$stmt = $connection->prepare("SELECT `PHONE`, `POPULARNAME`,
`LATITUDE`, `LONGITUDE`,
`THUMB`
FROM
`distilled_contacts` dc
WHERE
dc.id = ?");
$result = array('PHONE' => "", 'POPULARNAME' => "",
'LATITUDE' => "", 'LONGITUDE' => "",
'THUMB' => null);
$stmt->bindColumn(1, $result['PHONE']);
$stmt->bindColumn(2, $result['POPULARNAME']);
$stmt->bindColumn(3, $result['LATITUDE']);
$stmt->bindColumn(4, $result['LONGITUDE']);
$stmt->bindColumn(5, $result['THUMB']);
// which row to return
$stmt->bindValue(1, $ID);
$allOK = $stmt->execute();
// this returns an arry of results
$allResults = $stmt->fetchAll(PDO::FETCH_ASSOC);
/* debug */ // var_dump($result);
$currentRow = current($allResults);
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Upload images</title>
</head>
<body>
<ul>
<li>Phone: <?= $currentRow['PHONE'] ?></li>
<li>Name: <?= $currentRow['POPULARNAME'] ?></li>
<li>Lat: <?= $currentRow['LATITUDE'] ?></li>
<li>Long: <?= $currentRow['LONGITUDE'] ?><li>
<li>
<div style="border: red solid 1px;">
<img src="<?= 'data:image/'. 'png' .';base64,'. base64_encode($currentRow['THUMB']); ?>">
</div>
</li>
</ul>
</body>
</html>
You need to change the variable $ID
for your image id.
source to share
You are using utf-8 in your connection string. Could this be the problem? You may need to remove this and use "SET NAMES utf8".
See this: Is BLOB converted using current / standard encoding in MySQL?
Also take a look at the comments here: http://php.net/manual/en/pdo.lobs.php
In particular:
I spend a lot of time trying to get this to work, but no matter what I PDO has corrupted my data.
I finally found myself using:
$ pdo-> exec ('SET CHARACTER SET utf8');
in the TRY part of my connection script.
This course does not work when you feed binary input to PDO using the lob parameter.
Update
Like this
function dbconn($strHost, $strDB, $strUser, $strPass) {
try {
$conn = new PDO("mysql:host=".$strHost.";dbname=".$strDB, $strUser, $strPass);
$conn->exec('SET CHARACTER SET utf8');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_PERSISTENT, true);
return $conn;
}
catch(PDOException $e) {
die("Could not connect to the database $dbname :" . $e->getMessage());
return false;
}
}
source to share