View Issue Details

IDProjectCategoryView StatusLast Update
0004943mantisbtsqlpublic2017-01-18 10:08
Reporterclindemann Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status acknowledgedResolutionopen 
Product Version0.19.1 
Summary0004943: Large attachments can not be stored in database
Description

The size of the attachments that can be stored in a MySQL Database is restricted by the packet size of the query.

This can be tweaked a little by changing some of the MySQL and PHP parameters, but this would not be practical when storing large logfiles of 10meg>

Additional Information

A suggested fix would be to rewrite the database attachments code, not to try and store the whole blob in 1 record, but to spread it over several records.

An excellent article on how to do exactly this, can be found at:

"Uploading binary files to mySQL" http://php.dreamwerx.net/forums/viewtopic.php?t=6&highlight=inode

I have also attached the article.

TagsNo tags attached.

Relationships

related to 0011153 closedgrangeway Truncated download 
child of 0004181 closed Features in Mantis 1.1 release 

Activities

2004-12-07 12:30

 

Uploading_binary_files_to_mySQL.html (13,891 bytes)   
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head><link href="http://php.dreamwerx.net/forums/viewtopic.php?t=6&highlight=inode" rel="alternate" title="Uploading binary files to mySQL">
	<title>Uploading binary files to mySQL</title>
</head>

<body><SPAN class=postbody><FONT size=2>Welcome to my 1st 
article on PHP4.COM... Hopefully more to come.. If you have suggests about 
<BR>what you'd like to have an article/how-to on, be sure to drop me an email. 
<BR><BR>Before writing this article I did a quick google search for others who 
had dabbled in this <BR>area and only found 1 half-decent article. It was on 
phpbuilder.com written by a fellow <BR>named Florian. Now it worked ok, but was 
written with PHP3 in mind and I'm fairly certain <BR>designed to handle small 
files, which is not the case in the real world. I'll be pointing out <BR>some of 
the advantages of doing it the way I went. Also be sure now that everyone is in 
the <BR>world of PHP4, be sure to disable global var tracking!! <BR><BR>So 
what's with storing binary files in mySQL databases? Some might say it's a 
terrible <BR>idea, some might say it's a good idea... It all depends on the 
application. Storing <BR>files on disk is much simpler but itself has some 
limitations. Let's say you need to store <BR>20GB of data and grow by 
10GB/month.. Pretty soon that can easily fill up your webserver <BR>disk space.. 
And how do you add more? Drop in another disk, eventually you'll not be <BR>able 
to hookup any more disks, not to mention the downtime installing the new 
drive(s). <BR>How about NFS/SMB network shares? That's not a bad idea either but 
not without it's problems. <BR><BR>I've used mySQL binary storage in a few 
different applications now with good results. The <BR>biggest advantage is 
easily scalability. If tomorrow I needed to add 50GB of storage onto <BR>the 
system, you just grab yourself another Pentium II+ computer, drop a disk in it, 
install <BR>a base Linux OS on it and MySQL. Then in the case of my 
applications, there is a master database <BR>that controls all the location of 
the files. You just tell it that there is another storage <BR>server available, 
it's IP address, login, password, etc. And now it's available for use. This 
<BR>causes no downtime of any kind. Virtually unlimited scalability, you keep 
adding storage servers <BR>as demand for storage increases and if the webserver 
becomes overloaded handing the number of <BR>requests, you simply setup another 
mirrored webserver in a load-balanced environment and they <BR>both handle 
requests, cross connecting to the correct storage server to fulfill the frontend 
<BR>requests. <BR><BR>Now onto database design theory.. In most other examples, 
people took the easy way out. They went <BR>with a basic table design of: 
<BR><BR>CREATE TABLE binary_data ( <BR>id INT(4) NOT NULL AUTO_INCREMENT PRIMARY 
KEY, <BR>description CHAR(50), <BR>bin_data LONGBLOB, <BR>filename CHAR(50), 
<BR>filesize CHAR(50), <BR>filetype CHAR(50) <BR>); <BR><BR>---------- <BR>Now 
this example stores the file metadata and binary data all in 1 table.. A bad 
idea in my opinion. <BR>Also they use the column type of LONGBLOB.. This works 
ok for small files.. But as soon as you get into <BR>files larger than 1MB 
you're in trouble. mySQL by default has configured certain run-time variables 
<BR>quite low for this type of application use. Such variables as 
max_allowed_packet... You can boost <BR>these variables to higher runtime 
values.. But with my example you don't need to... <BR><BR>Another problem with 
the table definition above is that all the data for the file is stored in 1 
row.. <BR>So using a basic select you'll have to pull all the data from the 
mysql database to the webserver <BR>before sending it to the client.. With small 
files this doesn't matter, but say you had a 100MB file <BR>in the database, 
that means PHP on the webserver side will have to store 100MB of data in memory 
while <BR>it's being downloaded.. This is a bad thing as it can quickly eat up 
server memory on a busy site. <BR>Now there are ways around this such as looping 
thru and sub selecting pieces of the binary data <BR>from mysql.. But I prefer 
to stay away from this situation completely. <BR><BR>Let's begin with my example 
layout.. Please note the table design/code presented here are snippets <BR>from 
various application classes .. you should implement this code/design in classes 
that handle <BR>this type of operation. <BR><BR>Firstly lets start with my basic 
table layouts for the 2 required tables: <BR><BR>CREATE DATABASE storage1; 
<BR>use storage1; <BR><BR>CREATE TABLE file ( <BR>id mediumint(8) unsigned NOT 
NULL auto_increment, <BR>datatype varchar(60) NOT NULL default 
'application/octet-stream', <BR>name varchar(120) NOT NULL default '', <BR>size 
bigint(20) unsigned NOT NULL default '1024', <BR>filedate datetime NOT NULL 
default '0000-00-00 00:00:00', <BR>PRIMARY KEY (id) ) TYPE=MyISAM <BR><BR>CREATE 
TABLE filedata ( <BR>id mediumint(8) unsigned NOT NULL auto_increment, 
<BR>masterid mediumint(8) unsigned NOT NULL default '0', <BR>filedata blob NOT 
NULL, <BR>PRIMARY KEY (id), <BR>KEY master_idx (masterid) ) TYPE=MyISAM 
<BR><BR>---------- <BR><BR>So as you can see there are 2 tables... 1 stores the 
meta-data for the file (name, size, etc) And <BR>the other stores all the binary 
data in BLOB columns (64K) chunks.. These chunks could also be compared <BR>to 
inodes which makeup filesystems. The advantage to using a smaller column size is 
that you can <BR>request the rows 1 by 1 from the webserver and stream them out 
to the client, using low memory overhead. <BR>It will result in a persistent 
connection to the database being up for sometime (depending on filesize <BR>and 
client download speed), but with mysql being to handle 100 connections by 
default, I have yet to <BR>top out a storage server. The other nice thing about 
using 2 tables, is if say your just going to be <BR>listing the files in it.. 
You now only need to deal with a very small table for the file's meta-data 
<BR>not scan a very large file containing meta-data and binary text which would 
take much more database <BR>execution time. <BR><BR>Start with this example 
upload script (uploadpage.php): <BR><BR></FONT></SPAN>
<TABLE cellSpacing=1 cellPadding=3 width="90%" align=center border=0>
  
  <TR>
    <TD><SPAN class=genmed><B><FONT size=2>Code:</FONT></B></SPAN></TD></TR>
  <TR>
    <TD class=code><BR>&lt;form method="post" action="uploadprocess.php" 
      enctype="multipart/form-data"&gt; <BR>&lt;input type="file" name="file1" 
      size="20"&gt; <BR>&lt;input type="submit" name="submit" value="submit"&gt; 
      <BR>&lt;/form&gt; <BR></TD></TR></TABLE><SPAN class=postbody><BR><FONT 
size=2>---------- <BR><BR>Then with a basic processor script 
(uploadprocess.php): <BR></FONT></SPAN>
<TABLE cellSpacing=1 cellPadding=3 width="90%" align=center border=0>
  
  <TR>
    <TD><SPAN class=genmed><B><FONT size=2>Code:</FONT></B></SPAN></TD></TR>
  <TR>
    <TD class=code><BR>&lt;? <BR>// Upload processor script <BR>// At this 
      point your script would determine what storage server to connect to <BR>// 
      I'm just going to hardcode it here <BR><BR>$Storage_IP = "172.21.5.100"; 
      <BR>$Storage_Port = 3306; <BR>$Storage_User = "root"; <BR>$Storage_Passwd 
      = "secret"; <BR>$Storage_DB = "storage1"; <BR><BR>$connectto = $Storage_IP 
      . ":" . $Storage_Port; <BR><BR>if (!$linkid = @mysql_connect($connectto, 
      $Storage_User, $Storage_Passwd)) { <BR>&amp;nbsp;&amp;nbsp;die("Unable to 
      connect to storage server!"); <BR>} <BR><BR>if 
      (!mysql_select_db($Storage_DB, $linkid)) { <BR>&nbsp; die("Unable to 
      connect to storage database!"); <BR>} <BR><BR>// Init values - these are 
      used incase you want to upload multiple files, you just <BR>// add them to 
      the source form as file1, file2, file3, etc. <BR>$STARTFILE = 1; 
      <BR>$ONFILE = "file" . $STARTFILE; <BR><BR>while 
      (isset($HTTP_POST_FILES["$ONFILE"])) { <BR><BR>&nbsp; // Try! <BR>&nbsp; 
      $SrcPathFile = $HTTP_POST_FILES["$ONFILE"]["tmp_name"]; <BR>&nbsp; 
      $SrcFileType = $HTTP_POST_FILES["$ONFILE"]["type"]; <BR>&nbsp; 
      $DstFileName = $HTTP_POST_FILES["$ONFILE"]["name"]; <BR><BR>&nbsp; 
      clearstatcache(); <BR>&nbsp; $time = filemtime($SrcPathFile); <BR>&nbsp; 
      $storedate = date("Y-m-d H:i:s", $time); <BR><BR>&nbsp; // File Processing 
      <BR>&nbsp; if (file_exists($SrcPathFile)) { <BR><BR>&nbsp; &nbsp; // 
      Insert into file table <BR>&nbsp; &nbsp; $SQL&nbsp; = "insert into file 
      (datatype, name, size, filedate) values ('"; <BR>&nbsp; &nbsp; $SQL .= 
      $SrcFileType . "', '" . $DstFileName . "', " . filesize($SrcPathFile); 
      <BR>&nbsp; &nbsp; $SQL .= ", '" . $storedate . "')"; <BR><BR>&nbsp; &nbsp; 
      if (!$RES = mysql_query($SQL, $linkid)) { <BR>&nbsp; &nbsp; &nbsp; 
      die("Failure on insert to file table!"); <BR>&nbsp; &nbsp; } 
      <BR><BR>&nbsp; &nbsp; $fileid = mysql_insert_id($linkid); <BR><BR>&nbsp; 
      &nbsp; // Insert into the filedata table <BR>&nbsp; &nbsp; $fp = 
      fopen($SrcPathFile, "rb"); <BR>&nbsp; &nbsp; while (!feof($fp)) { 
      <BR><BR>&nbsp; &nbsp; &nbsp; // Make the data mysql insert safe <BR>&nbsp; 
      &nbsp; &nbsp; $binarydata = addslashes(fread($fp, 65535)); <BR><BR>&nbsp; 
      &nbsp; &nbsp; $SQL = "insert into filedata (masterid, filedata) values ("; 
      <BR>&nbsp; &nbsp; &nbsp; $SQL .= $fileid . ", '" . $binarydata . "')"; 
      <BR><BR>&nbsp; &nbsp; &nbsp; if (!mysql_query($SQL, $linkid)) { <BR>&nbsp; 
      &nbsp; &nbsp; &nbsp; die("Failure to insert binary <SPAN 
      style="COLOR: #ffa34f"><B>inode</B></SPAN> data row!"); <BR>&nbsp; &nbsp; 
      &nbsp; } <BR>&nbsp; &nbsp; } <BR><BR>&nbsp; &nbsp; fclose($fp); <BR>&nbsp; 
      } <BR><BR>&nbsp; $STARTFILE ++; <BR>&nbsp; $ONFILE = "file" . $STARTFILE; 
      <BR>} <BR><BR>echo "Upload Complete"; <BR>?&gt; 
<BR></TD></TR></TABLE><SPAN class=postbody><BR><FONT size=2>---------- 
<BR><BR>That's the basic jist of it... Please note.. This script is not an exact 
cut-paste from production <BR>code... So before leaving a note that it doesn't 
work.. be sure to throughly debug it.. Or better <BR>yet, just use the 
concept/example code and write your own code (perhaps better) .. ;) <BR><BR>Now 
if you want to retrieve and stream this data down to the end user you can take a 
look at this very <BR>simple example script (download.php) called like 
download.php?id=1 : <BR></FONT></SPAN>
<TABLE cellSpacing=1 cellPadding=3 width="90%" align=center border=0>
  
  <TR>
    <TD><SPAN class=genmed><B><FONT size=2>Code:</FONT></B></SPAN></TD></TR>
  <TR>
    <TD class=code><BR>&lt;? <BR>// Download script.. streams data from a 
      mysql database, thru the webserver to a client browser <BR><BR>if 
      (isset($_GET["id"])) { <BR><BR>&nbsp; $Storage_IP = "172.21.5.100"; 
      <BR>&nbsp; $Storage_Port = 3306; <BR>&nbsp; $Storage_User = "root"; 
      <BR>&nbsp; $Storage_Passwd = "secret"; <BR>&nbsp; $Storage_DB = 
      "storage1"; <BR><BR>&nbsp; $connectto = $Storage_IP . ":" . $Storage_Port; 
      <BR><BR>&nbsp; if (!$linkid = @mysql_connect($connectto, $Storage_User, 
      $Storage_Passwd)) { <BR>&nbsp; &nbsp; die("Unable to connect to storage 
      server!"); <BR>&nbsp; } <BR><BR>&nbsp; if (!mysql_select_db($Storage_DB, 
      $linkid)) { <BR>&nbsp; &nbsp; die("Unable to connect to storage 
      database!"); <BR>&nbsp; } <BR><BR>&nbsp; $nodelist = array(); 
      <BR><BR>&nbsp; // Pull file meta-data <BR>&nbsp; $SQL = "select * from 
      file where id = " . $_GET["id"]; <BR>&nbsp; if (!$RES = mysql_query($SQL, 
      $linkid)) { <BR>&nbsp; &nbsp; die("Failure to retrive file metadata"); 
      <BR>&nbsp; } <BR><BR>&nbsp; if (mysql_num_rows($RES) != 1) { <BR>&nbsp; 
      &nbsp; die("Not a valid file id!"); <BR>&nbsp; } <BR><BR>&nbsp; $FileObj = 
      mysql_fetch_object($RES); <BR><BR>&nbsp; // Pull the list of file inodes 
      <BR>&nbsp; $SQL = "SELECT id FROM filedata WHERE masterid = " . 
      $_GET["id"] . " order by id"; <BR><BR>&nbsp; if (!$RES = mysql_query($SQL, 
      $linkid)) { <BR>&nbsp; &nbsp; die("Failure to retrive list of file 
      inodes"); <BR>&nbsp; } <BR><BR>&nbsp; while ($CUR = 
      mysql_fetch_object($RES)) { <BR>&nbsp; &nbsp; $nodelist[] = $CUR-&gt;id; 
      <BR>&nbsp; } <BR><BR>&nbsp; // Send down the header to the client 
      <BR>&nbsp; Header ( "Content-Type: $FileObj-&gt;datatype" ); <BR>&nbsp; 
      Header ( "Content-Length: " . $FileObj-&gt;size ); <BR>&nbsp; Header ( 
      "Content-Disposition: attachment; filename=$FileObj-&gt;name" ); 
      <BR><BR>&nbsp; // Loop thru and stream the nodes 1 by 1 <BR><BR>&nbsp; for 
      ($Z = 0 ; $Z &lt; count($nodelist) ; $Z++) { <BR>&nbsp; &nbsp; $SQL = 
      "select filedata from filedata where id = " . $nodelist[$Z]; 
      <BR><BR>&nbsp; &nbsp; if (!$RESX = mysql_query($SQL, $linkid)) { 
      <BR>&nbsp; &nbsp; &nbsp; die("Failure to retrive file node data"); 
      <BR>&nbsp; &nbsp; } <BR><BR>&nbsp; &nbsp; $DataObj = 
      mysql_fetch_object($RESX); <BR>&nbsp; &nbsp; echo $DataObj-&gt;filedata; 
      <BR>&nbsp; } <BR>} <BR>?&gt; <BR></TD></TR></TABLE><SPAN 
class=postbody><BR><FONT size=2>---------- <BR><BR>I've just tested these 
scripts to be working correctly... they work well with streaming images.. Feel 
<BR>free to post any questions about them and I'll do my best to answer (as well 
as anyone else online). <BR><BR>Latz. B0nFire. 
(b0nfire@php4.com)</FONT></SPAN>



</body>
</html>
grangeway

grangeway

2005-03-03 13:51

reporter   ~0009466

This sounds like a mysql issue to me - for other databases, as far as i'm aware, you won't hit this as an issue, or am i wrong?

clindemann

clindemann

2005-03-03 14:35

reporter   ~0009475

I think definatly there will be similar issues with other DB engines.

Just try to add a several hundred meg Blob in one operation to any DB, and you will run into all sorts of trouble.

Issue History

Date Modified Username Field Change
2004-12-07 12:30 clindemann New Issue
2004-12-07 12:30 clindemann File Added: Uploading_binary_files_to_mySQL.html
2004-12-07 17:00 jlatour Status new => acknowledged
2004-12-07 17:01 jlatour Relationship added child of 0004181
2005-03-03 13:51 grangeway Note Added: 0009466
2005-03-03 14:35 clindemann Note Added: 0009475
2010-01-04 04:35 dhx Relationship added related to 0011153
2017-01-18 10:08 atrol Severity major => minor