How can i insert large files in mysql db using php?

Question

Hi!

I want to upload a large file of size 10M max to my mysql database. Using .htaccess i changed the PHP's own file upload limit to "10485760" = 10M, i am able to upload files upto 10M size without any problem.

But i can not insert the file in database if it is more that 1M 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 with more than 1M size is not being added to database, though i can use print_r($_FILES) to examine that the file uploaded correctly. Any help will be appreciated and i will need it within next 6 hours. So, please help!

best regards, Anjan

Answer 1

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 2

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

Answer 3

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 4

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 5

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 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.