9

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!

dda
  • 6,030
  • 2
  • 25
  • 34
anjan
  • 3,147
  • 6
  • 26
  • 31
  • While using mysql_error() i got the error for a 2.83M file - "MySQL server has gone away", is there any way i can change the packet size on my shared hosting site? like adding a variable declaration to some files? – anjan Jan 29 '09 at 17:46

7 Answers7

15

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.

Community
  • 1
  • 1
Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
8

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.

roborourke
  • 12,147
  • 4
  • 26
  • 37
  • 1
    Well i finally had to admit that putting larger files in database is a large trouble for my current project, So i uploaded the file in the directory and saves the path in db. But still curious about the Database storing. – anjan Jan 30 '09 at 06:21
  • 1
    Same story here, we had to change to the file system. (DB maintenance became to slow/painfull) – Jacco Jan 30 '09 at 10:24
  • 2
    This is generally true for mysql because blobs are not real blob but are limited to 4GB (for longblob). Real DBMS (Oracle, MSSQL, Firebird) do not have this limitation and also do not slow down at all. Handling backup and restore is much easier when everything is in a single database. Anyway, if you're stuck with MySQL, you can use mysqli_stmt_send_long_data to stream the large file in chunks. – Milan Babuškov Dec 20 '09 at 20:47
  • 5
    I experimented with filesystem storage. I find that keeping the DB and the FS synchronized is an additional concern... what if someone deletes a file on the FS? What if a DB record is deleted but the associated file remains on the FS? It's complicated to make DB operations atomic when something outside the DB is involved. – Rolf Sep 17 '11 at 14:17
  • 1
    Storing files on the filesystem instead of the DB is not "better practice". It's merely a different approach. There are many valid reasons why you'd store files in a DB instead, portability for starters. It's incredible how far spread this myth of "don't store files in the DB" is, including the assumption that this results in a faster system. – Rid Iculous Aug 22 '14 at 02:46
  • @RidIculous to be fair I used the AFAIK qualifier :) We're talking MySQL here, the typical setup which most people will be running isn't optimised for storing files. Probably where the myth comes from. I'm certainly not a DBA and not sure how you stream/link an image online from the database either hence I've found this approach easier to understand and work with – roborourke Aug 22 '14 at 10:21
4

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
4

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.

DreamWerx
  • 2,888
  • 1
  • 18
  • 13
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.

Ionuț G. Stan
  • 176,118
  • 18
  • 189
  • 202
0

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

Well I have the same problem. And data cannot be entered in the mysql database chunck by chunck in a "io mode" 

loop for : 
   read $data from file,
   write $data to blob
end loop
close file
close blob

A solution seems to create a table with multi-part blobs like create table data_details ( id int pk auto_increment, chunck_number int not null, dataPart blob ); ???

Pang
  • 9,564
  • 146
  • 81
  • 122
manueldahmen
  • 71
  • 2
  • 6
0

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

Greg
  • 316,276
  • 54
  • 369
  • 333