How can I insert large files in MySQL db using PHP

Question

I want to upload a large file of maximum size 10MB to my MySQL database. Using .htaccess I changed PHP's own file upload limit to "10485760" = 10MB. I am able to upload files up to 10MB without any problem.

But I can not insert the file in the database if it is more that 1 MB in size.

I am using file_get_contents to read all file data and pass it to the insert query as a string to be inserted into a LONGBLOB field.

But files bigger than 1 MB are not added to the database, although I can use print_r($_FILES) to make sure that the file is uploaded correctly. Any help will be appreciated and I will need it within the next 6 hours. So, please help!

Answer 1

As far as I know it's generally quicker and better practice not to store the file in the db as it will get massive very quickly and slow it down. It's best to make a way of storing the file in a directory and then just store the location of the file in the db.

We do it for images/pdfs/mpegs etc in the CMS we have at work by creating a folder for the file named from the url-safe filename and storing the folder name in the db. It's easy just to write out the url of it in the presentation layer then.

Answer 2

Some PHP extensions for MySQL have issues with LONGBLOB and LONGTEXT data types. The extensions may not support blob streaming (posting the blob one segment at a time), so they have to post the entire object in one go.

So if PHP's memory limit or MySQL's packet size limit restrict the size of an object you can post to the database, you may need to change some configuration on either PHP or MySQL to allow this.

You didn't say which PHP extension you're using (there are at least three for MySQL), and you didn't show any of the code you're using to post the blob to the database.

Answer 3

You could use MySQL's LOAD_FILE function to store the file, but you still have to obey the max_allowed_packet value and the fact that the file must be on the same server as the MySQL instance.

Answer 4

You don't say what error you're getting (use mysql_error() to find out), but I suspect you may be hitting the maximum packet size.

If this is the case, you'd need to change your MySQL configuration max_allowed_packet

Answer 5

You will want to check the MySQL configuration value "max_allowed_packet", which might be set too small, preventing the INSERT (which is large itself) from happening.

Run the following from a mysql command prompt:

mysql> show variables like 'max_allowed_packet';

Make sure its large enough. For more information on this config option see

MySQL max_allowed_packet

This also impacts mysql_escape_string() and mysql_real_escape_string() in PHP limiting the size of the string creation.

Answer 6

The best answer is to use an implementation that is better and also works around that issue. You can read an article here. Store 10MB, 1000MB, doesn't matter. The implementation chunks/cuts the file into many smaller pieces and stores them in multiple rows.. This helps with load and fetching so memory doesn't also become an issue.