Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Apache::DBI, DBI, DBD::MySql and MySQL cache?

by Flame (Deacon)
on Mar 17, 2003 at 03:51 UTC ( [id://243576]=perlquestion: print w/replies, xml ) Need Help??

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;

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).

      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)

Re: Apache::DBI, DBI, DBD::MySql and MySQL cache?
by Anonymous Monk on Mar 17, 2003 at 08:27 UTC
      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)

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)

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.

      That's what I was thinking too. Let's see some more code!
        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)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://243576]
Approved by diotalevi
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (3)
As of 2024-06-22 06:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.