Re^4: Mysql and Images

I don't use the DB for performance reasons - i use it because it's easier to handle (well, at least should be ;) ).
The images will change pretty often. If you consider file system vs. database performance, you also have to keep fragmentation in mind. Although modern *nix-filesystems tend to be resistant against fragmentation issues, a _very_ large fluctuation can still cause serious performance loss (as you can see with Gentoo Linux's portage tree).

But as said before - its just easy to handle. You don't have to care about potential security headaches with uploading binary files into your cgi-dir, permissions etc. Just 'insert' and 'select', and there you (should) go. And i still have to get the content-type, either by extracting it from the image (bad idea) or by getting it from the Database.

Some more Information about my problem: System is a current Gentoo-Linux with perl 5.8.6, mysql-4.1.14, DBD::Mysql 2.9007, DBI 1.46. When using the console-client 'mysql', everything works as expected. Same with phpMyAdmin.
The following code also works:

 my $sql = "select type from bilder where id =?";
    my $sth = $dbh->prepare($sql);
    $sth->execute( $q->param('id') );
    my $error = 0;
    my $type = $sth->fetchrow || ( $error = 1 );
    $sql = "select data from bilder where id =?";
    $sth = $dbh->prepare($sql);
    $sth->execute( $q->param('id') );
    my $data = $sth->fetchrow();
    binmode(STDOUT);
    ...
[download]

Comment on Re^4: Mysql and Images Download Code

Replies are listed 'Best First'.
Re^5: Mysql and Images by gloryhack (Deacon) on Dec 19, 2005 at 20:38 UTC
Hmmm... I've never noticed fragmentation being a source of unreasonable latency in an HTTP environment on a *n(i\|u)x machine, but I'll take your word for it. Moving right along: I would never think to put images into my CGI directory -- they'd live out in the public HTML directory tree somewhere, and if they were coming from untrusted sources that directory would be in a partition mounted with the noexec option. The CGI application, if it has to, should be able to reach into that directory just fine, it being under HTTP document root. I'd still think very seriously about `$q -> redirect($image_url);` though. Back to the original problem: Without more information it's impossible to be certain, but if it were mine to fix I'd start by looking at whether or not there's a source of Unicode data in the system, based upon that log line you got when you stuffed a warn into the code.	[reply] [d/l]
Re^6: Mysql and Images by Braindead_One (Monk) on Dec 22, 2005 at 07:52 UTC
Sorry for the late reply ;) Ther was indeed some kind of unicode-problem. My Mysql version had Unicode enabled which turned out to be a bad idea. I noticed this when i tried to insert some non-ascii-chars like ä ö ü into the Database. I now recompiled without unicode-support and can now insert those chars without problems, but the select of binary and text-data in 1 query still goes wrong :/. Since this project hast to be finished before saturday i'll go with 2 selects for now. Thank you for your suggestions!	[reply]