RE: storing files in mySQL
Why not rather have pointers to the files on a directory and store this in a db field ? Saves you a lot of DB space ? Ant -Original Message- From: Dang Nguyen [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 09:05 To: Mysql List (E-mail) Subject: storing files in mySQL Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: storing files in mySQL
This is how we did it with PHP Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB. You have to restart your database with some parameters, which will remove the limitations. /usr/local/bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M If you are using Unix, check out your init-tree and change the corresponding startup file. See this page for more detail... http://www.phpbuilder.com/columns/florian19991014.php3?page=2 Patrick Egan Egan Consulting 15 Elvina Gardens Toronto, Ontario M4P 1X7 T] 416 630 4982 F] 416 488 7187 C] 416 726 4832 Pager] 416 377 9031 E] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] W] www.eganconsulting.com http://www.eganconsulting.com -Original Message- From: Anton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 6:13 AM To: 'Mysql List (E-mail)' Subject: RE: storing files in mySQL Why not rather have pointers to the files on a directory and store this in a db field ? Saves you a lot of DB space ? Ant -Original Message- From: Dang Nguyen [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 09:05 To: Mysql List (E-mail) Subject: storing files in mySQL Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing files in mySQL
On Mon, 4 Mar 2002, Dang Nguyen wrote: I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. MS-Word and PDF files are essentially binary files (very much like images). I suggest leaving them as they are in a separate directory. These MS-Word and PDF files may be converted to plain ASCII using filters (mswordview, pdftotext). The resulting ASCII versions are then loaded into a table with one column of type TEXT along with a VARCHAR pointer to the filename. Create a fulltext index on the TEXT field. A fulltext search for some keywords will return a sorted list of filenames. These files can be returned to the client. This scenario works well for me on a collection of some 15'000 HTML documents (I used w3m as a filter to convert to ASCII). All conversions are done with a simple shell script calling the filters. Thomas Spahni - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing files in mySQL
On Tuesday 05 March 2002 10:05, Thomas Spahni wrote: There are some good reasons for wanting to store data directly in the database, sometimes. For instance I built an application recently that used blobs. The reason is simplicity and security. In a web application it is nice to just dump the files into a directory and point to them, but consider that if you are building NOTHING but a database server, you don't want to have to support and manage security for another protocol, like ftp or http. In addition in my case it would have made the client code much more complicated since the client could already deal with an ODBC data source. I would have had to code in an entire new piece of functionality to deal with the ftp or http protocol, then figure out how to engineer it so the client code would automatically go and get the data, etc. Plus now I can't just manage data security with SQL GRANT instructions. On top of that huge directories are very slow in most file systems. We had over 24 million records! That would mean that some elaborate scheme would have to be developed, like MD5 hash the filename and walk a series of directory levels down based on characters in the hash, etc. Since data is getting added and removed constantly there would then have to be a way to manage that tree to keep it from getting unbalanced etc. In the final analysis this would have been pretty close to the equivalent of building our own b-tree index! Given that database already have highly optimized indexes, it seemed more sensible to use the existing functionality of blobs and indexes. I think what all this points out is that MySQL developers might want to consider how to better support this type of application in the future. Currently HUGE tables of large blobs ARE very awkward. Our blob tables are terabytes in size, and the corruption of one record can force the tape restore from lower regions... Not to mention the horrors of backing the thing up, or any operation that mysql tries to perform by copying the table (of which there are several I believe). Maybe a special table handler module could be written that would cater to the needs of this type of application, plus a few special tools for doing repairs and such. Just an idea :o). On Mon, 4 Mar 2002, Dang Nguyen wrote: I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. MS-Word and PDF files are essentially binary files (very much like images). I suggest leaving them as they are in a separate directory. These MS-Word and PDF files may be converted to plain ASCII using filters (mswordview, pdftotext). The resulting ASCII versions are then loaded into a table with one column of type TEXT along with a VARCHAR pointer to the filename. Create a fulltext index on the TEXT field. A fulltext search for some keywords will return a sorted list of filenames. These files can be returned to the client. This scenario works well for me on a collection of some 15'000 HTML documents (I used w3m as a filter to convert to ASCII). All conversions are done with a simple shell script calling the filters. Thomas Spahni - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: storing files in mySQL
Thanks to everyone who responded to my query. Your responses have given me a bit to think about. Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: storing files in mySQL
...and so I'm tempted for the third time to bring up my issue which has to date resulted in no response: BLOB columns causing various, seemingly random out of memory errors on FreeBSD. Is this problem too low-level for the list? I see plenty of responses to questions about simple queries, even PHP code examples -- but it seems that more serious questions warrant no reply. If nobody can help me, could someone at least point me in the right direction? I've exhausted all my search options, from list archives to bug reports (turns out mysqlbug just sends an e-mail to this list, and I've provided most of that information in my e-mails anyway), and found no references to this issue. For consistency, I'll quote my second e-mail: I'm having an issue with BLOB columns (MEDIUMBLOB and LONGBLOB, more specifically) in a table that's intended to store files (with filename, content-type and length columns). Generally, the problem seems to be that MySQL thinks that it's out of memory when dealing with arbirarily large values, either during an attempted INSERT/UPDATE (using LOAD_FILE() or the contents of the file as a string) or during a mysqldump. The problem has manifested on 2 x86/FreeBSD development boxes running the ports installation of 3.23.46, as well as an OSX box running 3.23.47. The exact same operations succeed on our production server, a Sparc/Solaris box running 3.23.36. The my.cnf's are identical on all 3 machines: a copy of the my-large.cnf with a modified max_allowed_packet value of '16M' (which should suit a query that INSERT's a file at least 7MB in size, with escaped characters, etc.). The specific errors encountered are: - when attempting an INSERT INTO binaries (contents) VALUES ([str]), where [str] is the contents of a file over ~2MB, the INSERT fails with Out of memory: (xxx bytes needed). Subsequent attempts *sometimes* result in the MySQL server has gone away error. The same errors occur when replacing [str] with LOAD_FILE([filename]). - when attempting to dump a database containing values of LONGBLOB columns over ~1MB, mysqldump fails with the same Out of memory error above. - when attempting to feed a dump into a database using the mysql DB dump.sql method (or source'ing it from the SQL prompt), values in the same range cause mysql to die with the error MySQL server has gone away. I should also mention that the errors are pretty erratic: the same operation often yields different errors on each attempt. A recent mysqldump succeeded on one FreeBSD box, but the attempt to restore it on the other one failed (MySQL server has gone away). Several times, attempts to INSERT large values have resulted in corrupt tables, or NULL values without any error. Has anyone else experienced any of these symptoms? Our production server (which routinely handles files in excess of 5MB) hasn't exhibited this issue yet, and it's running a practically ancient version of MySQL. An installation of the same version (3.23.36) on one of the FreeBSD boxes showed no change. We've checked the physical memory and df on all 3 boxes that we've experienced the problems, and none of them even come close to being out of memory or disk space. We even checked safe_mysqld to see if it calls limit or ulimit... which it doesn't. I'm totally stumped. There are some good reasons for wanting to store data directly in the database, sometimes. I'd like to think so, too... but this problem is keeping us from storing anything larger than 2MB in a column that's supposed to be able to store 16MB worth of data. If we can't make this work, then we'll be forced to abandon MySQL, and I would really hate to do that! cheers, -- shawn allen | [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
storing files in mySQL
Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: storing files in mySQL
Hi, You should use the LOAD_FILE fonction to load your documents in the BLOB field. Ex: UPDATE table_name SET blob_column=LOAD_FILE(/some/where/picture); You could find better explaination of that fonction in the manual at: http://www.mysql.com/doc/S/t/String_functions.html Regards, Sébastien DIDIER Zarcrom SAS - Web Hosting www.zarcrom.fr -Message d'origine- De : Dang Nguyen [mailto:[EMAIL PROTECTED]] Envoyé : lundi 4 mars 2002 20:05 À : Mysql List (E-mail) Objet : storing files in mySQL Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
