[Resolved] Images/Pictures Stored in MySQL Database Getting Cut Off

jzimmerlin

I'm creating a web-based interface to manage images/photos for a promotional campaign at work. Here's what I'm trying so desperately to do....

Users log on, complete a form (name, e-mail, etc), and upload a picture.
The information and the picture are stored in a MySQL database. The picture/image is stored as a BLOB in the MySQL database.
Using a web-based administration interface, I'll be able to see all of the entries, view the pictures, and information.

HERE'S THE PROBLEM I'M HAVING:
It appears small pictures upload just fine, but larger images do not. They get cut off when I go to look at them. Here is an example of the problem I'm experiencing: http://mydomain.com/KSBY/Admin/Details.php?Number=74. The username/password you'll need to see this is KSBY and ksby respectively.

I don't understand why I'm having this problem. I've beefed up my file size upload limit in PHP.ini and rebooted my server to no avail. My PHP.ini file is below:

;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;

; Whether to allow HTTP file uploads.
file_uploads = On

; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
upload_tmp_dir = C:\Program Files\Apache Group\Apache\htdocs\KSBY\Upload ; temporary directory for HTTP uploaded files (will use system default if not specified)

; Maximum allowed size for uploaded files.
upload_max_filesize = 10M

Any ideas?

bradgrafelman

What type of field in MySQL are you storing this in?

jzimmerlin

I'm storing the image as a BLOB.

bradgrafelman

Ruuuuunnnn!!! It's the BLOB!!!

Alright, enough of that. 🙂

Hmm, how big is the picture (filesize) ? How small are the "small" pictures that upload fine? It doesn't seem like PHP is limiting the upload, as it wouldn't just cut it off; it simply wouldn't allow it.

Seems like it could be:

MySQL is killing the upload connection (taking too long, using too much memory, etc.).
MySQL is not storing the file properly.
MySQL is killing the download connection (same reasons as #1).
PHP is killing the download connection (basically, same reasons as #1).

Soo... could you paste some code? If at all possible, I'd like to see all of it; from upload to download. If that's a lot of code, post it as an attachment.

jzimmerlin

The small file is something ridiculous like 10 kilobytes. The "big" file is something like 100 kilobytes. Really, not a huge difference. I've attached the code.

bradgrafelman

Certainly not.

I've been poking around some MySQL system variables.. defaults are up around 8M heh. Obviously, MySQL var's are probably NOT getting in our way.

Lemme take a gander at your code... looks like there's enough of it to keep me busy a while.

EDIT: Do you keep a copy of the uploaded file(s) somewhere BEFORE they are sent to MySQL?

jzimmerlin

PHP creates a temporary file (which is deleted rather quickly), but it does not keep a permanent copy anywhere.
The temporary file is kept in C:\Program Files\Apache Group\Apache\htdocs\KSBY\Upload\

bradgrafelman

Just to humor me - can you comment out the line that unlink's the file, and then upload a file. Check out the temp file in the uploads directory - is it complete/uncorrupt?

EDIT: You know, I wonder if 'BLOB' is not enough. You mentioned you were upping the size towards 100K? Change the column type from 'BLOB' to 'MEDIUMBLOB' and upload a file. Let me know how it downloads.

jzimmerlin

Sure, I'll humour you. But, I'm not sure which line in which file you're talking about. Let me know which one it is and I'll do it.

bradgrafelman

EDIT: You know, I wonder if 'BLOB' is not enough. You mentioned you were upping the size towards 100K? Change the column type from 'BLOB' to 'MEDIUMBLOB' and upload a file. Let me know how it downloads.

Give that a shot; it just may solve our problems.

jzimmerlin

Wow, that helped. I can upload files in the range of 500+ kilobytes. Bigger files don't work. Would it help to change it to LongBlob?

bradgrafelman

Hrm?!

I must have missed a calculation.. I had guestimated MEDIUMBLOB's range up near 15M. Woopsie :x

Let's go all out with a SuperMegaEliteWhoa-dats-a-phat -Blob.

(Yes, up it to a LongBlob).

bradgrafelman

Also:

Posted by Kieran Leigh on July 12 2004 11:20am @ MySQL.com

addslashes will not work in all cases, the end result being minor file corruption (e.g. JPEGs still load, but image is distorted).

It's safer to use base64 encoding, e.g.
$data = base64_encode(fread(fopen($FILES['doc']['tmp_name'], "r"), $FILES['doc']['size']));

EDIT: Woops, sorry about the 2x post... that was NOT the edit button...

EDIT2: Upon further reading of base64, I've decided I don't like it. Let's try to use your current method 🙂

jzimmerlin

That helped. I can now upload the 500 kb file I could not upload before. I can also upload a 1000kb file, but nothing bigger. When I try and upload something larger, say 1,500 kb, everything looks fine. However, the entry does not even show up in the database. Any ideas?

bradgrafelman

Is MySQL set to log errors and warnings to a logfile?

jzimmerlin

I'm not sure (Sorry, I'm a bit of a novice.) Any idea where the log files are stored? I look in the MySQL directory, but didn't see any in particular that stood out.

bradgrafelman

Do you have AIM, MSN, Yahoo Messenger, etc. ?

jzimmerlin

Actually I found it... here's the last couple of lines:

"'%s' is deprecated, use '%s' instead",
#define ER_NON_UPDATABLE_TABLE 1288
"The target table %-.100s of the %s is not updatable",
#define ER_FEATURE_DISABLED 1289
"The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
#define ER_OPTION_PREVENTS_STATEMENT 1290
"The MySQL server is running with the %s option so it cannot execute this statement",
#define ER_DUPLICATED_VALUE_IN_TYPE 1291
"Column '%-.100s' has duplicated value '%-.64s' in %s"
#define ER_TRUNCATED_WRONG_VALUE 1292
"Truncated wrong %-.32s value: '%-.128s'"
#define ER_TOO_MUCH_AUTO_TIMESTAMP_COLS 1293
"Incorrect table definition; There can only be one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause"
#define ER_INVALID_ON_UPDATE 1294
"Invalid ON UPDATE clause for '%-.64s' field",
#define ER_UNSUPPORTED_PS 1295
"This command is not supported in the prepared statement protocol yet",

jzimmerlin

No, I don't have any instant messenger services running.

bradgrafelman

Aha! That's what it is! I should have known!! (I even finished reading an article about this!)

MySQL is trying to literally read the files, and it's getting some percentage signs in there. Well, % and _ are wildcards in MySQL. Not good if we're trying to insert a file.

You've escaped the slashed, but not these special characters.

In file Submit.php, circa line 25:

	$Content=addslashes($Content);

should be:

	$Content=mysql_real_escape_string($Content);

Try that out, and lemme know!