storing blob into mysql through apache and php

This idea sounds easy and stuff. I agree. I assume you already got it working fine. But perhaps one day you may notice something is not working well, especially when you are dealing with bigger files. Below are a few things you want to look out for.

Change your /etc/php.ini

upload_max_filesize = 2M
post_max_size = 8M
memory_limit = 128M

The default upload file size is 2M, which could be too small. If your upload file is larger than this, you will receive an empty upload file at your server side. So, increase this. While if you are using ajax to post-send, increase the post_max_size limit. Another thing to watch out for is memory_limit. If you somehow make copies of big blobs in your code, increasing PHP’s memory limit is also a good idea.

After these changes, restart your apache server by: sudo apachectl restart

You can double check by calling the phpinfo() function. Your new settings should show up.

Next thing is in mysql. If you have been using addslashes to insert, that will eventually fail when you hit your mysql query max length limit. I am not sure how long that is but I hit that a few times which got me all confused until I checked my logs. So, possibly change your insert or update statement to something like the following.

UPDATE my_table SET my_blob = LOAD_FILE( $filename ) WHERE id = '$id';

Now, after you made all these changes you may still be loading null into your db. If so, check this. Go grab your uploaded file at the server and check the file size. It better be the right size. In code, I mean

if (isset($_FILES["my_file"])){
	$file_obj = $_FILES["my_file"];
	$file_size = $file_obj["size"];
	print $file_size; // hopefully it's greater than 0

	// below is for later, skip it for now
	$file_tmp_name = $file_obj["tmp_name"];
	$file_content = file_get_contents($file_tmp_name);

Once you pass that, there may be a chance your mysql client doesn’t have enough permission to read your uploaded file. Below is some code to create a new temp file and let your mysql client read the file.

// basically make a duplicate of your upload file
$tmpfname = tempnam("/tmp", "somePrefix");
$handle = fopen($tmpfname, "w");
fwrite($handle, $file_content);
chmod($tmpfname, 0666);  // this is so that the mysql process can read this file

// HERE, call your mysql LOAD_FILE statement. 

unlink($tmpfname); // clean up

If you are still loading null into your db. Then it’s also possible that your current mysql user doesn’t have enough permission to load local files.

GRANT FILE ON *.* TO root@localhost
show grants

Assuming you are using root. I know, it’s bad. Shut up. Just an example.

And if you are still loading null, then it’s also possible that your mysql client has a low max_load_file size limit. Do this. Open a terminal, get a mysql prompt by: mysql -u root -p

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

That should allow enough data to go through the LOAD_FILE command.

I am good after all these. Hopefully your issues are solved by now. 😉

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s