Increasing upload size in PHP and MySQL

Wednesday, May 30, 2007, 2:05 PM
Web Design by John (Article #156)

Around these parts, we're fond of using binary blobs stored in MySQL to serve a single piece of content to multiple websites. Recently, I got done doing database work on a website that required the upload of PDFs. Of course, PDF files get very big very fast.

You eventually run up again the upload limits in PHP and MySQL. Most MySQL installations have a default maximum upload size of 1Mb. Of course, a fair number of PDF files go over this limit.

For the sake of this tutorial, we're going to configure the system to allow you to upload to a maximum of 16Mb. That means making tweaks to the PHP.ini file and my.cnf file.

Advertisements

PHP.ini

I'm using a Fedora Core server from GoDaddy. My PHP.ini file was found in the /etc/ folder.

You need to change the following bits of code in your PHP.ini file:

upload_max_filesize = 16M

max_execution_time = 60
max_input_time = 120
memory_limit = 64M

The first thing that should jump out at you is that the memory limit is pretty high. Since this system only allows large, intensive actions on an admin side that never has more than one person on it, it is no big deal. For a large system that has multiple users this can be a bad idea unless you have the monster server to beat all monster servers.

The execution and input times are also raised, to account for the likely slowness of uploading such a large file. If you know your connections will ship the file faster, then you may want to leave those settings alone.

my.cnf

The my.cnf file allows you to add settings to your default MySQL installation.

We're adding just one bit of code to the file:

max_allowed_packet=16M

Now, a lot of instructions I've seen for changing this setting tell you to restart your MySQL service. Let me save you some trouble: restart the whole server.

Why?

Because odds are good that if you just restart the service you're going to create a new socket that will not interact properly with your PHP scripts.

When I did this, I restarted the service, and MySQL popped back up to life. I'm sitting there, peaceful, happy, serene. And then I see a swift die message from MySQL saying it couldn't connect to the socket. Grrrrr...

Once I rebooted the whole server, all was well.

MySQL blobs

When you're configuring your database tables, don't forget to make sure you're using the MEDIUMBLOB for your binary. Remember, we're talking Mb here. There should be no need to go all the way up to a LONGBLOB, but obviously that option is there if you need it.

Conclusion

I continue to have a great deal of success serving dynamic content out the MySQL database. It make life a lot easier, because I have a very simple and straight-up method for backing up all the data. A lot less FTPing.

However, as you expand this sort of system, you're going to run into hurdles like this. I'm also pretty sure that going much large in file size on a high availability system would reveal some of the flaws in this approach. Particularly, database connections will become a limiting factor.

However, for a fairly simple CMS system that doesn't require a lot of user upload capability, this approach provide a quick, easy and simple to replicate method for getting the job done.

Mail article to a friend

Tools

Check Google PageRank

Recent articles

Welcome!

Wonder where to start with your web design business?

This blog follows along with my efforts to build and grow a website design business, Pro Content and Design.

The goal of this blog is to fill in blanks that may be empty as you get your business rolling.

This blog, particularly the source code section, is not intended for beginners. If you are not comfortable with databases, Ajax, DOM objects and other advanced methods, I strongly suggest you go take a look over at W3 Schools before even reading -- let alone tinkering with -- any of the code here.

I hope this blog has some value to web designers as they attempt to get their businesses going.

Good luck, and happy reading.

Thank you,
John Crawford
Pro Content and Design