Unable to add large BLOBS (>10mb) to MySQL DB

[philmarsay]

I have got a real problem... I have a database system (MySQL) which has a front-end written in PHP.

One of the functions of the front-end is to allow file-upload into the MySQL database, as BLOBS.

I seem to have hit some unpassable limit of approximate 10mb (9mb works, 13 mb fails), of the files that I can get into the database.

I have upped the MAX_PACKET_SIZE in MySQL, and the wait timeout, I have upped the max execution, max input, and memory limits of PHP, all to no avail.

The field in the database is a LONGBLOB, so should be OK.

Can anyone pleeeease tell me that I am missing something obvious somewhere. I understand MySQL is supposed to be handle, theoretically BLOB sizes of up to 2 gig!

Here is the snippet of code that does the uploading... I know this code works, as it works with smaller files...(up to approx 9mb)

$attsql = "insert into attachments values(";
$attsql .= $current_id.",'".$filename."','".$description."','".$filetype."','".$filesize."','".$data."')";
mysql_connect("host", "user", "password");
mysql_select_db("database");
mysql_query($attsql);

If I output the $data to screen, it all seems to be there, but I think MySQL is just plain rejecting the query when the BLOB is big, as nothing is being inserted into the database. The PHP process runs, sends the query to MySQL, and, from that point, nothing happens. Thats how it seems to be.

I can't help thinking this is just a configuration of MySQL that I have not quite gotten right, but I sure can't find it!!

I am using PHP4 and MySQL 4.1

Please help, I'm losing sleep over it!!!!

[CFMaBiSmAd]

It is likely that the upload is failing due to one of the limits being reached. Do you have error checking in your code to insure that the upload is successful, before you even get to the point to trying to put it into your database?

Check - $_FILES['your_field_name']['error']

UPLOAD_ERR_OK
Value: 0; There is no error, the file uploaded with success.

UPLOAD_ERR_INI_SIZE
Value: 1; The uploaded file exceeds the upload_max_filesize directive in php.ini.

UPLOAD_ERR_FORM_SIZE
Value: 2; The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form.

UPLOAD_ERR_PARTIAL
Value: 3; The uploaded file was only partially uploaded.

UPLOAD_ERR_NO_FILE
Value: 4; No file was uploaded.

UPLOAD_ERR_NO_TMP_DIR
Value: 6; Missing a temporary folder. Introduced in PHP 4.3.10 and PHP 5.0.3.

UPLOAD_ERR_CANT_WRITE
Value: 7; Failed to write file to disk. Introduced in PHP 5.1.0.

UPLOAD_ERR_EXTENSION
Value: 8; File upload stopped by extension. Introduced in PHP 5.2.0.

[philmarsay]

thanks, its not the upload thats the problem. The current test I am doing is not even uploading the file - just picking it from the filesystem.

However, I think all this is in vain.

Whilst it would be nice to have all these things stored in the database, I am beginning to think it's not the way and I should just be storing them in the filesystem with a link stored in the system.

[CFMaBiSmAd]

So, if you are directly reading a file and storing it in the DB, putting a little error checking and reporting on your mysql function calls will probably give you some information as to where the problem is:

Change this -

PHP Code:

mysql_query($attsql); 


To this -

PHP Code:

mysql_query($attsql) or die('The INSERT query failed: ' . mysql_error()); 


[philmarsay]

thanks for the tip.

I had already tried that, and bizarrely it just returned nothing. Maybe I did something wrong, I dunno.

Never mind, I'm going to go down the route of storing the files in the file system. I can only foresee other problems down the line with big files stored in the database.

Is silly that in these days and times, this isn't a simple function, requirement even for database back-ends. Never mind!

Phil