"stale data": mysql, ima-dbi, modperl, mason

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

Hi folks --

Yet again I am puzzled and turn to the generous and wise folks here for help. Can anyone offer suggestions?

I am not sure if my problem is perl, mod_perl, mason, mysql, ima::dbi .....

Here's my situation: I seem to be pulling "stale" data from a mysql table. I am running a query select max(ymdhms) from downloadevent.

If I run this though mason and Ima::DBI, I get "stale" data -- that is, rows added since the last apache restart don't get considered.

If I run the query thru Ima::DBI via a command line perl -M -e, I get the correct answer. If I run the query through a sql tool, I get the correct answer. If I run the query through mason and ima::dbi, I get the incorrect answer. If I restart apache, I then get the correct answer. (A clue, I think.)

More details:

The app uses activestate perl 5.8.2, mason 1.25, Apache 1.3.29 (Win32), and mod_perl 1.29_01-dev.

The IMA::DBI statement handle looks like this:

Sql::Stash->set_sql('last_downloadevent', q(select max (ymdhms) from r
+ktrack.downloadevent),'RKG',0);
[download]

which I display in Mason like this

...
<% $last_downloadevent %>
...
<%init>
use Sql::Stash;
my $stash = Sql::Stash->new; 
my $sth = $stash->sql_last_downloadevent; 
$sth->execute;
my ($last_downloadevent) = $sth->fetch;
$sth->finish;
...
</%init>
[download]

My problem is that the data seems to come back "stale" -- if newer rows (which have ymdhms greater than anything in the table) have been added to rktrack.downloadevent since the last time the Apache restarted, mason / ima::dbi doesn't see them.

So I figure something is getting cached, so foe debugging I opted to mangle to query temporarily to ensure it changes on each call:

Sql::Stash->set_sql('last_downloadevent', q(select concat(max(ymdhms) 
+,'-', now()) from rktrack.downloadevent where ?>0),'RKG',0);
[download]

Note how I've appended the time to field to ensure it changes.

When I display the mason page with this change, mangle field shows the stale downloadevent with the fresh (changing!) time.

This indicates to me that the problem is in the database, perhaps.

And if I run

select max(ymdhms) from rktrack.downloadevent
[download]

from the database query tool, I get the correct (the fresh) downloadevent. And if I run the Ima::DBI query from the command line,


perl -MSql::Stash -e "my $ss=Sql::Stash->new; my $sth=$ss->sql_last_do
+wnloadevent; $sth->execute; print $sth->fetch"
[download]

I get the correct (the fresh) downloadevent.

I added the "0" to the end of the set_sql to indicate no caching (yes?); I tried adding a dummy placeholder parameter to see if the problem was execute w/o bind values... I tried issuing "commits" from the sql tool; I tried closing the connection that did the insert (just to make sure the insert really hit the database -- it did.) .... no luck. The ima::dbi / mason query doesn't see new inserts into downloadevent until the apache restarts.

I think perhaps I am missing something basic about the database here....

Any suggestions, wise monks?

rkg

Comment on "stale data": mysql, ima-dbi, modperl, mason Select or Download Code

Replies are listed 'Best First'.
•Re: "stale data": mysql, ima-dbi, modperl, mason by merlyn (Sage) on Dec 26, 2003 at 18:45 UTC
My Mason skills are a bit rusty, but I seem to recall that an %init section is exactly what it says: run once. You need to execute the query outside an %init section in the main part of your page generation, otherwise the results are computed once and cached, as %init is meant to do. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply. update: Nevermind. I was rusty. I confused %once with %init, and just went off to verify that. So what you describe is a bit spooky, but I have nothing to offer at this point. Good luck. Now, if anyone has a Template Toolkit question instead... my batting average is a bit better there... {grin}	[reply]
Re^2: "stale data": mysql, ima-dbi, modperl, mason by monarch (Priest) on Mar 19, 2006 at 07:58 UTC
Oh my gosh, Randal, I thought it was just me you gave invalid advice to; but clearly you've been doing it for years. Your track record of providing plain wrong advice, then telling everyone not to question you because of your Perl history, is exactly why I would never purchase a book that you've written. Moreover, I would highly recommend any newbie to steer well clear of you for fear of almost guaranteed bad advice. Randal, please please please work on improving your Perl and general programming skills and stop publishing in the name of making the Perl world a better place. Come back when you've become a better man.	[reply]
Re^3: "stale data": mysql, ima-dbi, modperl, mason by merlyn (Sage) on Mar 19, 2006 at 16:56 UTC
Your track record of providing plain wrong advice, then telling everyone not to question you because of your Perl history, "Objection: assumes facts not in evidence!" "Sustained. Mr monarch, please refrain from introducing evidence by presumption. Continue." -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.
Some other things you can try: by Arunbear (Prior) on Dec 26, 2003 at 21:54 UTC
When invoking the mason page, add '?=' to the end of the url: 'http://myserver/mypage.mas?=' - this should tell your browser to make a fresh request each time Disable your browser's cache and see what happens Try running your query through a plain cgi script - mason doesn't cache unless you tell it to, and it doesn't look like you have, but this step will indicate if the caching is happening at the mason/mod_perl level UPDATE: Put this line in the init block of your top level autohandler `$r->no_cache(1);` this will ensure your page is not cached by browsers and proxy servers. (this is recipe 3.10 in the mod_perl cookbook)	[reply] [d/l]
Re: "stale data": mysql, ima-dbi, modperl, mason by rkg (Hermit) on Dec 26, 2003 at 22:59 UTC
Well, I found it. I am not sure why, but somewhere in the bowels of Ima::DBI a transaction was being held, I think. If I start my `<%init>` section with `my $dbh = $stash->db_RKG; $dbh->commit;` [download] then all is well. I am not sure why Ima::DBI was holding a transaction open ...... Thanks. I am relieved. How odd. rkg PS For those who might say, "Serves him right, for using an esoteric module like Ima::DBI", well, I opted to use Ima::DBI as Class::DBI is built on it, and I am a big fan of Class::DBI, and because I wanted to keep all my sql in one place, outside of my code. Perhaps I'll drop Tony Boden, the Ima::DBI maintainer, a note and see if he can explain this all to me....	[reply] [d/l] [select]
Re: Re: "stale data": mysql, ima-dbi, modperl, mason by perrin (Chancellor) on Dec 27, 2003 at 16:01 UTC
If the commit is actually what fixes it, then it has nothing to do with Ima::DBI. You would have the same issue with straight DBI calls.	[reply]
Re: "stale data": mysql, ima-dbi, modperl, mason by CountZero (Bishop) on Dec 26, 2003 at 21:34 UTC
<guessing mode="on"> Could it be that the combination of Mason and mod_perl make your `<%init>` sections run only the first time they are used (and thereafter are "cached" by mod_perl)? <guessing mode="off"> CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l]
Re: Re: "stale data": mysql, ima-dbi, modperl, mason by simonm (Vicar) on Dec 27, 2003 at 06:43 UTC
Could it be that the combination of Mason and mod_perl make your <%init> sections run only the first time they are used (and thereafter are "cached" by mod_perl)? Nope. From HTML::Mason::Devel: `This section contains initialization code that executes as soon as the component is called... Technically an <%init> block is equivalent to a <%perl> block at the beginning of the component.`	[reply]


No such thing as a small change
	PerlMonks