Re: Blobs getting truncated?

I use the database because I have to issues: versioning and lack of access to a filesystem; the server is a special machine where I can use the MySQL database (with a generous 10+ gigabytes), but nothing else. Plus the app is part of an online image collaboration site, so the users want to be able to store every change to each image. The system is project-based, so the images themselves will be few (about 100+), but the size may go up to megabytes (the users have a custom C++ app the gets the files using MySQL++). Thus the need to be able to upload and download the images.

As an aside, I actually have always felt kind of the opposite...putting the files on the filesystem, while easier, leads to potentially more problems (permissions, disconnected disk, synchronization between the db and the fielsystem, etc). With everything in the database, I know the images are there, being backed up, and subject only to SQL for being deleted or modified.

Thanks for the info,

Ron

On 7/6/07, Warren Young <mysqlpp@etr-usa.com> wrote:
>
> Ron Olson wrote:
> >
> > any image over 64k gets truncated;
>
> You're just running into the limit of MySQL's BLOB data type. To get
> beyond 64 KB, you must use either MEDIUMBLOB or LONGBLOB. Also, beware
> that the default maximum packet size for a transfer to the database is 1
> MB, which you will hit before you exceed the limits on MEDIUMBLOB. You
> can increase the packet size limit, but I've read that you start hitting
> different limits at 16 MB which are harder to work around.
>
> Personally, I can't find any reasonable justification for storing
> anything that's either very large, or highly variable in size in a
> relational database. Typical databases -- like MySQL -- are designed to
> work best with fixed-length records composed of relatively short field
> sizes. For everything else, we have filesystems. If I were you, I'd
> use the database only to maintain a lookup table between some unique ID
> and the location of the file on disk.
>
> The only reason I've kept the JPEGs-in-the-database examples is because
> it's such a generic idea that anyone can grasp it easily. I don't want
> to teach people about, say, the arcanities of binary representations of
> IR remote control codes in order to understand the examples. (That's my
> own need for BLOBs.) I want a user to be able to look at the example
> and get past the actual data type being used quickly, and JPEGs serve
> that need well. I wouldn't actually store JPEGs in a database in a real
> system.
>
> On an unrelated note, the current cgi_jpeg example is a little
> optimistic in terms of how it expects to be able to deal with the data
> it pulls. I've changed it to be more reliable, though less efficient:
>
> Index: examples/cgi_jpeg.cpp
> ===================================================================
> --- examples/cgi_jpeg.cpp (revision 1644)
> +++ examples/cgi_jpeg.cpp (working copy)
> @@ -83,9 +83,10 @@
> ResUse res = query.use();
> if (res) {
> images img = res.fetch_row();
> + string img_data(img.data, img.data.length());
> cout << "Content-type: image/jpeg" << endl;
> - cout << "Content-length: " << img.data.length() << "\n\n";
> - cout << img.data;
> + cout << "Content-length: " << img_data.length() << "\n\n";
> + cout << img_data;
> }
> else {
> cout << "Content-type: text/plain" << endl << endl;
>
Received on Sat Jul 7 15:45:09 2007