http://qs321.pair.com?node_id=243576

Flame has asked for the wisdom of the Perl Monks concerning the following question:

I'm running a mod_perl program on Apache (where else?), and it uses Apache::DBI and DBD::mysql to connect with my database. In this database I have a table of "quotes", and I use the following code to attempt to select a random one.

my $sth = $state->{'DBH'}->prepare('SELECT quote,author FROM quotes OR
+DER BY RAND() LIMIT 1');
$sth->execute;
my $row = $sth->fetchrow_arrayref;
$sth->finish;

[download]

It seems to consistantly select 1 quote for each thread that apache is running, and those are the only quotes I can get out of it through normal use. Simply going in and executing that statement from a terminal yields different ones almost all the time. When there are 80 to choose from, and 3 threads (non-production server), it pushes probability that I could consistantly get only the same three quotes. Does Apache::DBI, DBI, DBD::mysql or MySQL itself cache responses?




My code doesn't have bugs, it just develops random features.

Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

Replies are listed 'Best First'.
Re: Apache::DBI, DBI, DBD::MySql and MySQL cache?
by blm (Hermit) on Mar 17, 2003 at 04:23 UTC

    What version of mysql are you using?

    Starting in version 4.0.1 there is a "query cache"(1). You can see whether your mysql has this cache with the following query. (2)

    SHOW VARIABLES LIKE 'have_query_cache';

    According to the Mysql manual page mysql keeps in this cache a table of queries and the corresponding results. When an identical query is received the results out of the cache are returned. This is what one would expect. There is a big note: 

    
NOTE: The query cache does not return stale data. When data
is modified, any relevant entries in the query cache are
flushed.

    I am not sure but I would assume that since the table is not modified and the query is not modified the result would come from the cache. You can turn off the cache using options in the select query as noted (here).

[reply]
[d/l]

      Unfortunately, I'm pretty out of date, I'm not sure where I found it anymore, but I seem to remember (was busy trying to get anything working back then) using an RPM, anyway, my current version is only 3.23.55, and it rejects a statement such as "SELECT SQL_NO_CACHE quote,author FROM quotes ORDER BY RAND() LIMIT 1" as SQL_NO_CACHE is not a valid column. I suppose I could try having it return NOW() as well?




      My code doesn't have bugs, it just develops random features.

      Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

[reply]
Re: Apache::DBI, DBI, DBD::MySql and MySQL cache?
by Anonymous Monk on Mar 17, 2003 at 08:27 UTC
[reply]
      Unfortunately those ideas were already covered. Looks like my only option is to SELECT COUNT and then let perl select a random ID from the database. (/me frowns over the wasted transit time)


      My code doesn't have bugs, it just develops random features.

      Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

[reply]
Re: Apache::DBI, DBI, DBD::MySql and MySQL cache?
by Flame (Deacon) on Mar 17, 2003 at 03:54 UTC
    Actually, the longer I wait the more 'new ones' appear, but it still seems to favor those first three, one of which is first in the list.


    My code doesn't have bugs, it just develops random features.

    Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

[reply]
Re: Apache::DBI, DBI, DBD::MySql and MySQL cache?
by Hero Zzyzzx (Curate) on Mar 17, 2003 at 14:35 UTC

    Are you sure you're not having any problems with mis-used package globals in your script? This, to me, sounds more like a mod_perl error and less like a MySQL error. I've never run into this type of behavior due to MySQL, but I have run into it when I was first learning mod_perl and using package globals.

    -Any sufficiently advanced technology is
    indistinguishable from doubletalk.

[reply]
      That's what I was thinking too. Let's see some more code!
[reply]
        Ok, code, Flame's scratchpad. This is the "school project" (Code is too long to be posted here) The sub you are looking for is called quotes, it's near the end of the program. (Any packages not used there are loaded by mod_perl ahead of time.)


        My code doesn't have bugs, it just develops random features.

        Flame ~ Lead Programmer: GMS (DOWN) | GMS (DOWN)

[reply]

Back to Seekers of Perl Wisdom