Oh my blob!

I never used blobs before with MySQL, and so I had to learn painful that there are several types of blobs available in MySQL: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB.

And that is the way I had to learn it: In my app the user can upload files, and these files are stored in the database. Everything worked fine with small files. But for some reason it didn’t work with larger files. If I tried to download the uploaded files, the download was discontinued after a short time period. “Probably a timeout problem”, I thought. And so I searched in this area for the cause of the problem. Without result. By accident I landed on the page “Data Type Storage Requirements”, and I got the “Aha” effect. I used the wrong data type: “BLOB” supports only data up to 64kb…

Another learning from this episode is that the cause of a problem is sometimes not there where you are looking for…

10 Comments

  1. Posted March 31, 2006 at 6:21 pm | Permalink

    Well, I’ve never worked with Blobs before, but I don’t know why you would use them to store actual *files* in a database?

    I’ve heard of people doing it before but I always thought that a database is a bottleneck for this kind of operation. Because very often the database connection is established through a tcp/ip connection which is way slower then accessing the hard drive directly. So I try to store my files locally whenever possible.

    Any reasons why you want them in the db?

  2. Posted March 31, 2006 at 10:42 pm | Permalink

    I agree with Felix and am wondering about your reasons behind this decision. But still, thank you for the information in case I ever need to store files in the database.

  3. Posted April 1, 2006 at 7:42 am | Permalink

    I must admit that I usually store stuff to harddrive, but storing to db it does make scaling a hell of a lot easier – no need to map network drives or sync files to other servers – just replicate the db and you are away…

  4. Posted April 1, 2006 at 9:33 am | Permalink

    The reason I store files in the database is simple: it is easier. I do not have to keep file system and database in sync. That makes it easier to test the application.

    Yes, I think it is slower to access the files via DB (I haven’t done any benchmarks). If it becomes an issue, I have to change it. But I am confident it is not necessary.

  5. elswidi
    Posted August 8, 2006 at 10:58 pm | Permalink

    how can I display a picture stored as a blob in mysql using cakephp?

  6. Posted August 9, 2006 at 8:27 am | Permalink

    @elswidi: You can find an example in https://cakebaker.wordpress.com/2006/04/15/file-upload-with-cakephp/

  7. elswidi
    Posted August 9, 2006 at 8:41 pm | Permalink

    > @elswidi: You can find an example in >https://cakebaker.wordpress.com/2006/04/15/file-upload-with-cakephp/

    thanks alot cakebaker. The example is really great and straight forword

    your help is really appreciated :)

  8. elswidi
    Posted August 9, 2006 at 8:49 pm | Permalink

    kindly, would you please tell me where I can find complete reading materials about CakePHP rather than Code Snippets and CakeForge tutorials? I mean books, pdfs, chms … to read more about CakePHP APIs, methods, conventions ..etc

  9. elswidi
    Posted August 10, 2006 at 3:03 pm | Permalink

    thanks cakebaker :) very helpfull links to get started with CakePHP
    > http://manual.cakephp.org
    > http://api.cakephp.org
    > http://cakeforge.org/projects/cakedocs/


Post a Comment

Required fields are marked *
*
*

%d bloggers like this: