View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0004943 | mantisbt | sql | public | 2004-12-07 12:30 | 2017-01-18 10:08 |
Reporter | clindemann | Assigned To | |||
Priority | normal | Severity | minor | Reproducibility | always |
Status | acknowledged | Resolution | open | ||
Product Version | 0.19.1 | ||||
Summary | 0004943: 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. | ||||
Tags | No tags attached. | ||||
Attached Files | 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><form method="post" action="uploadprocess.php" enctype="multipart/form-data"> <BR><input type="file" name="file1" size="20"> <BR><input type="submit" name="submit" value="submit"> <BR></form> <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><? <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>&nbsp;&nbsp;die("Unable to connect to storage server!"); <BR>} <BR><BR>if (!mysql_select_db($Storage_DB, $linkid)) { <BR> 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> // Try! <BR> $SrcPathFile = $HTTP_POST_FILES["$ONFILE"]["tmp_name"]; <BR> $SrcFileType = $HTTP_POST_FILES["$ONFILE"]["type"]; <BR> $DstFileName = $HTTP_POST_FILES["$ONFILE"]["name"]; <BR><BR> clearstatcache(); <BR> $time = filemtime($SrcPathFile); <BR> $storedate = date("Y-m-d H:i:s", $time); <BR><BR> // File Processing <BR> if (file_exists($SrcPathFile)) { <BR><BR> // Insert into file table <BR> $SQL = "insert into file (datatype, name, size, filedate) values ('"; <BR> $SQL .= $SrcFileType . "', '" . $DstFileName . "', " . filesize($SrcPathFile); <BR> $SQL .= ", '" . $storedate . "')"; <BR><BR> if (!$RES = mysql_query($SQL, $linkid)) { <BR> die("Failure on insert to file table!"); <BR> } <BR><BR> $fileid = mysql_insert_id($linkid); <BR><BR> // Insert into the filedata table <BR> $fp = fopen($SrcPathFile, "rb"); <BR> while (!feof($fp)) { <BR><BR> // Make the data mysql insert safe <BR> $binarydata = addslashes(fread($fp, 65535)); <BR><BR> $SQL = "insert into filedata (masterid, filedata) values ("; <BR> $SQL .= $fileid . ", '" . $binarydata . "')"; <BR><BR> if (!mysql_query($SQL, $linkid)) { <BR> die("Failure to insert binary <SPAN style="COLOR: #ffa34f"><B>inode</B></SPAN> data row!"); <BR> } <BR> } <BR><BR> fclose($fp); <BR> } <BR><BR> $STARTFILE ++; <BR> $ONFILE = "file" . $STARTFILE; <BR>} <BR><BR>echo "Upload Complete"; <BR>?> <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><? <BR>// Download script.. streams data from a mysql database, thru the webserver to a client browser <BR><BR>if (isset($_GET["id"])) { <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> die("Unable to connect to storage server!"); <BR> } <BR><BR> if (!mysql_select_db($Storage_DB, $linkid)) { <BR> die("Unable to connect to storage database!"); <BR> } <BR><BR> $nodelist = array(); <BR><BR> // Pull file meta-data <BR> $SQL = "select * from file where id = " . $_GET["id"]; <BR> if (!$RES = mysql_query($SQL, $linkid)) { <BR> die("Failure to retrive file metadata"); <BR> } <BR><BR> if (mysql_num_rows($RES) != 1) { <BR> die("Not a valid file id!"); <BR> } <BR><BR> $FileObj = mysql_fetch_object($RES); <BR><BR> // Pull the list of file inodes <BR> $SQL = "SELECT id FROM filedata WHERE masterid = " . $_GET["id"] . " order by id"; <BR><BR> if (!$RES = mysql_query($SQL, $linkid)) { <BR> die("Failure to retrive list of file inodes"); <BR> } <BR><BR> while ($CUR = mysql_fetch_object($RES)) { <BR> $nodelist[] = $CUR->id; <BR> } <BR><BR> // Send down the header to the client <BR> Header ( "Content-Type: $FileObj->datatype" ); <BR> Header ( "Content-Length: " . $FileObj->size ); <BR> Header ( "Content-Disposition: attachment; filename=$FileObj->name" ); <BR><BR> // Loop thru and stream the nodes 1 by 1 <BR><BR> for ($Z = 0 ; $Z < count($nodelist) ; $Z++) { <BR> $SQL = "select filedata from filedata where id = " . $nodelist[$Z]; <BR><BR> if (!$RESX = mysql_query($SQL, $linkid)) { <BR> die("Failure to retrive file node data"); <BR> } <BR><BR> $DataObj = mysql_fetch_object($RESX); <BR> echo $DataObj->filedata; <BR> } <BR>} <BR>?> <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> | ||||
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? |
|
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. |
|