Uploading large files with mySQL

--001636c5ad51a9b891046e952e6e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hello,
I'm developing a web application that requires to store large files in a
MySQL database. The files can range up to 2Gb.

In my understanding the upper limit for the SQL queries (thus insert
queries) is 1GB. I'm trying, for the time being, to upload files of sizes
around 65MB, but I still get some errors - including the "MySQL server has
gone away".

I followed the discussion in other post, and I properly set all the system
variables (max_allowed_packet_size, wait_timeout) to the maximum value, but
the error still occurs.

Would you suggest looking at something in particular?

For the time being, just to try this, I'm inserting the file through the
MySQL Query Browser - just not to add other possible sources of problems on
top of that.

Thanks!

Dan

--001636c5ad51a9b891046e952e6e--

Re: Uploading large files with mySQL

--0016364efbe40e9ec1046e959f96
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Redesign. Do NOT store files in a database. The kind of database that's good
a storing files, is more commonly known as "filesystem".


On Mon, Jul 13, 2009 at 2:08 PM, Daniele Development-ML <
daniele.dml@googlemail.com> wrote:

> Hello,
> I'm developing a web application that requires to store large files in a
> MySQL database. The files can range up to 2Gb.
>
> In my understanding the upper limit for the SQL queries (thus insert
> queries) is 1GB. I'm trying, for the time being, to upload files of sizes
> around 65MB, but I still get some errors - including the "MySQL server has
> gone away".
>
> I followed the discussion in other post, and I properly set all the system
> variables (max_allowed_packet_size, wait_timeout) to the maximum value, but
> the error still occurs.
>
> Would you suggest looking at something in particular?
>
> For the time being, just to try this, I'm inserting the file through the
> MySQL Query Browser - just not to add other possible sources of problems on
> top of that.
>
> Thanks!
>
> Dan
>



--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

--0016364efbe40e9ec1046e959f96--

Re: Uploading large files with mySQL

At 07:08 AM 7/13/2009, you wrote:
>Hello,
>I'm developing a web application that requires to store large files in a
>MySQL database. The files can range up to 2Gb.
>
>In my understanding the upper limit for the SQL queries (thus insert
>queries) is 1GB. I'm trying, for the time being, to upload files of sizes
>around 65MB, but I still get some errors - including the "MySQL server has
>gone away".
>
>I followed the discussion in other post, and I properly set all the system
>variables (max_allowed_packet_size, wait_timeout) to the maximum value, but
>the error still occurs.
>
>Would you suggest looking at something in particular?

You are storing 1-2gb "files?" in a database??? Why? What are you trying to
achieve?

As Johan said, SQL is not meant to do this and I see no reason why you
would want to. It is like trying to shove an elephant into a phone booth
and then be disappointed he can't do tricks.

You are going to have to rethink the problem you are trying to solve. If
you want to tell us what the problem is, maybe the people on this group can
help.

Mike


>For the time being, just to try this, I'm inserting the file through the
>MySQL Query Browser - just not to add other possible sources of problems on
>top of that.
>
>Thanks!
>
>Dan


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Uploading large files with mySQL

Hi Dan,

The problem with BLOB is that traditionally MySQL, along with a lot of othe=
r
databases, is not designed to handle them very well. The BLOB data is passe=
d
between the client and server as if it where the same as any other data.
This results in large memory use by both the client and server as the BLOB
is buffered on both sides. The standard solution to this was to store the
BLOBs some where in a file system and then place some form of reference to
the BLOB in the database that could then be used by the client to get the
actual data. The problem of how to set such a system up and maintain the
externally stored data was left up to the individual application designer.

The good news is that I am working on a generic solution to this problem
called the PrimeBase Media Stream engine (PBMS) that is intended to handle
exactly what you want to do. PBMS is a specialized storage engine that work=
s
with other storage engines to store BLOB data. The actual BLOB data is
streamed to and from the PBMS engine itself and is not passed through the
MySQL server and client interface. What is stored in the actual BLOB column=
s
in the normal storage engine tables is a BLOB reference that can be used to
get the real BLOB data from the PBMS engine. The PBMS engine handles the
storage of the BLOB data which may be stored locally or could be stored
remotely in Amazon S3 storage for example.

For more information please have a look at our web site:
http://www.blobstreaming.org or check out my BLOG
http://bpbdev.blogspot.com.

Good luck on your project.

Barry


On 7/13/09 5:08 AM, "Daniele Development-ML"
wrote:

> Hello,
> I'm developing a web application that requires to store large files in a
> MySQL database. The files can range up to 2Gb.
>=20
> In my understanding the upper limit for the SQL queries (thus insert
> queries) is 1GB. I'm trying, for the time being, to upload files of sizes
> around 65MB, but I still get some errors - including the "MySQL server ha=
s
> gone away".
>=20
> I followed the discussion in other post, and I properly set all the syste=
m
> variables (max_allowed_packet_size, wait_timeout) to the maximum value, b=
ut
> the error still occurs.
>=20
> Would you suggest looking at something in particular?
>=20
> For the time being, just to try this, I'm inserting the file through the
> MySQL Query Browser - just not to add other possible sources of problems =
on
> top of that.
>=20
> Thanks!
>=20
> Dan

------------------------------------------------------------ -------------
Barry Leslie

SNAP Innovation Softwareentwicklung GmbH
Senior Software Engineer

Tel: (001) 250 595 4228
Fax: (001) 250 595 4233
Email: Barry.Leslie@PrimeBase.com
Web: www.PrimeBase.com

SNAP Innovation Softwareentwicklung GmbH, D-22765 Hamburg,
Max-Brauer-Allee 50, Germany
Amtsgericht Hamburg HRB 61066, Geschäftsführer: Ulrich Zimmer, Paul
McCullagh
------------------------------------------------------------ -------------




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg