Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

mysql, locked databases, Apache::DBI and mod_perl

by Flame (Deacon)
on May 19, 2003 at 02:07 UTC ( [id://259040]=perlquestion: print w/replies, xml ) Need Help??

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

Ok, as can be seen here, I've been having a problem with a program that I recently introduced table locking into (and have since removed thinking it may be the source of the problem, so far it doesn't look like it.) Now it seems to work only part of the time, and it fails and suceeds, aparantly, at random. I will execute it once with a certain set of operations to complete and it executes flawlessly, I execute it again with the same tasks to perform and it MIGHT work, or it might not. I've been able to hit refresh 20-30 times sometime and have it fail on 31, but it can fail anywhere from 1 to infinity as far as I can see.

I'm at a loss to even guess what the problem might be, beyond the fact that it seems to stem from the database for some reason, but the database remains accessable, I can still run identical queries from the mysql shell interface and have them function, and if I hit refresh a few dozen times without the commands, it eventually brings me back... usually, however it seems to do so without my session data (I use Apache::Session::Mysql), which furthers my suspicions about the database.

I'v been working with this database for the past several months without a problem, and aside from the brief time when I experemented with locking, nothing in it's interactions should have changed in the areas it seems to fail in... then again I have yet to find an area where it does reliably continue to work.

I have attempted, as can be seen in my previous post, which, while it did gain some reputation, failed to produce any useful assistance, to use the Apache::DB module to allow me to use a debugger on it, but I can't seem to get that working (see error messages in the referenced node.) I'm at a complete loss and under some severe time constraints to get my project complete, and I find myself unable to even debug it. Does anyone have ANY advice whatsoever?

While waiting, I'll try inserting warn statements to try to trace the problem line(s)... somehow I doubt I'll have much luck.



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

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

Replies are listed 'Best First'.
Re: mysql, locked databases, Apache::DBI and mod_perl
by perrin (Chancellor) on May 19, 2003 at 04:46 UTC
    Debuggers are nice, but they aren't always the solution. What usually works for me is to take stuff out until the bug goes away. Then you know where it is and you can make a tiny example and post it here.

    Locking can be very dangerous with Apache::DBI. If you lock tables, make sure you add cleanup handlers that unlock them. Otherwise, your code could die in the middle and leave a table locked.

    Something that often helps with "random" bugs is to run in -X mode so that you hit the same process each time. Also, as I mentioned in your other post, try using TCP instead of local sockets. I know it's voodoo, but more people run it that way so it has been better debugged.

      Hmm, I'll try -X and see if it helps. As for the locked tables, that code was commented out and the system was restarted so the locks should no longer be affecting the situation, however while on the subject, if I have one database handle for my database in my program, if I try to create a new one under Apache::DBI to the same database, will I have two handles to the same connection, or two connections?



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

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

        Assuming the connection parameters are identical, you will just get the exact same handle back.
      Ok, I've managed to narrow it down using a series of warn() statements represented by &pdebugger; in the following code, in combination with -X. From the top of the file, to where the problem occurs, and a little more:
      #!/usr/bin/perl use DBI; use Date::Calc qw(Days_in_Month Day_of_Week Today Decode_Day_of_Week T +his_Year check_date Day_of_Year Delta_Days Month_to_Text); use CGI::Util qw(escape); use HTML::Entities qw(encode_entities); use Data::Dumper; use Digest::MD5 'md5_base64'; use Data::Page; #To simplify paging logic use HTML::CalendarMonthSimple; use Apache::Request; use Apache::Cookie; use Tie::IxHash; use Apache::Session::MySQL; #use Date::Format; use Parse::RecDescent; #use IO::File; use strict; #This can be declared here because it never changes our %months; tie(%months,'Tie::IxHash'); %months = qw(9 September 10 October 11 November 12 December 1 January +2 February 3 March 4 April 5 May 6 June); #Filters for fields used in each mode TO BE REDONE our @newfields = ('type','title',qr/^new\d+(?:day|month)$/,qr/^[se]t\d ++(?:-\d+)?$/,qr/^newtype\d+(?:-\d+)?$/,'newspec',qr/^keepday\d+$/,qr/ +^desc\d+(?:-\d+)?$/,'expire',qr/^apm[se]\d+(?:-\d+)?$/,qr/codeblock\d ++/,'cmd','fields',qr/^newexp(?:day|month$)/); our @browsefields = ('cid'); our $timeparse = qr/^0?([12]?\d):(\d+):00$/; #Initialize the state, this will be passed to all subs my $state = {}; &pdebugger; #Scope all processing that sets variables { #Get the Apache::Request object my $r = new Apache::Request(shift); my $cookie = new Apache::Cookie($r, -name => 'MY_SESSION_ID', -path => '/', ); my %cookies = $cookie->parse; my $DBH = DBI->connect('dbi:mysql:schedule;host=localhost','root',' +',{RaiseError => 1}); &pdebugger; { #Used to demonstrate that mysql itself can still communicate my $sth = $DBH->prepare("SELECT * from users"); $sth->execute; warn(Dumper($sth->fetchall_arrayref)); } #Session code adapted from: # Apache::Session docs # HTML::Mason samples # code available at http://www.masonhq.com/user/adpacifico/Apache +SessionMason.html my %session; &pdebugger; eval { tie %session, 'Apache::Session::MySQL', ($cookies{'MY_SESSION_ID'} ? $cookies{'MY_SESSION_ID'}->value +() : undef), { Handle => $DBH, LockHandle => $DBH }; }; &pdebugger; # If we could not re-establish an existing, $@ should contain # 'Object does not exist in the data store'. If the eval # failed for a different reason, that might be important if ($@) { if ($@ =~ m#^Object does not exist in the data store#) { #this will create a new session entry tie %session, 'Apache::Session::MySQL', undef, { Handle => $DBH, LockHandle => $DBH }; undef $cookies{'MY_SESSION_ID'}; } else { #place message in server log warn $@; } } &pdebugger; $cookie->value( $session{'_session_id'} ); $cookie->bake; #Add to header queue &pdebugger; $r->send_http_header('text/html'); &pdebugger; # Timestamp the session hash to ensure Apache::Session writes # out the data store The reason for this is that # Apache::Session only does a shallow check for changes in # %session. If %session contains references to objects whose # attributes have changed, those changes won't be recorded. So # adding a 'timestamp' key with a value that changes every # request ensures that all data structures are stored to disk. $session{'timestamp'}=localtime; #End session establishing code #End localization block, place everything needed into the state #Prepare to release tables later #$state->{'ulock'} = $DBH->prepare('UNLOCK TABLES'); $state->{'r'} = $r; $state->{'session'} = \%session; $state->{'DBH'} = $DBH; } &pdebugger;

      The first time through, everything runs fine, however, on the second attempt, things go wrong. I've traced it down to the first session declarations, repeated here:

      #Session code adapted from: # Apache::Session docs # HTML::Mason samples # code available at http://www.masonhq.com/user/adpacifico/Apache +SessionMason.html my %session; &pdebugger; eval { tie %session, 'Apache::Session::MySQL', ($cookies{'MY_SESSION_ID'} ? $cookies{'MY_SESSION_ID'}->value +() : undef), { Handle => $DBH, LockHandle => $DBH }; }; &pdebugger;

      The first &pdebugger; in this section is on line 64, the last one to print on the second run (reliably gets this far and no farther). I've concluded the problem must lie in Apache::Session somewhere, but I am at a loss to even guess where aside from the unique id generating methods or their locking mechanism. I'm not quite sure what I should attempt at this point, should I edit the actual files and see if I can find the problem, or is the problem in my use of Apache::Session? Well... at least I'm gaining some ground.



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

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

        Can you give more explanation of "things go wrong"? What happens, exactly? Does it hang? Do you get error messages?
Re: mysql, locked databases, Apache::DBI and mod_perl
by The Mad Hatter (Priest) on May 19, 2003 at 02:19 UTC
    Bah, I don't know why you're complaining about this, like you said, it just developed a random feature. ;-)

    In any case, this sounds like it might be a trap with mod_perl. Take a look at the beginning of this, which talks about general situations (and solutions and debugging techniques) where stuff works and doesn't work apparently at random.

      Hmm, I've seen it before, but it does remind me of one other thing I changed... I had a variable that was 'my' previously, and I changed it to 'our' in the hopes that a sub I couldn't conveniently pass it to would be able to get it... I'll change that back and see what happens.

      This is when you wish you had CVS or one of those other systems...



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

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

        This is when you wish you had CVS or one of those other systems...

        Stop Wishing. Install (or learn to use) CVS (or the equivalent) ASAP. Without some type of source-code control, you're working without a safety net. The ability to ask "what changed?" questions--and get authoratative answers--can be a huge win when you find yourself in a situation like the one you're in now.

Solution: mysql, locked databases, Apache::DBI and mod_perl
by Flame (Deacon) on May 21, 2003 at 23:25 UTC

    Well I found the source of my problem, and now I want to go bang my head against a wall. It seems PodMaster was closer than I thought in his private messages to me. On the assumption he doesn't mind, I'll quote him here:


    PodMaster says reading your various troubles with DBI under mod_perl I can't help but get the screaming feeling that you really haven't learned scoping basics -- I hope i'm wrong, but it doesn't look that way (and them problems aren't generic enough to be resolve, much less properly diagnosed without proper disclosure)


    perrin also mentioned the possibility here.

    Both were pretty close, I had overlooked one thing. I had declared $state with my as could be seen in the code snippet I posted here. Until now, it hasn't made much of a difference where I declared it because each sub declared it's own $state, preventing $state from being cloned into it... I had recently added 2 new subs one of which did not need $state, and so it was not declared there... thus it was cloned in. In order to fix it, I modified the scope of $state, eliminating the chance that could happen again, and changed a few other variables while I was at it.

    Thank you to those who offered advice and assistance.



    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://259040]
Approved by cciulla
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2024-03-19 07:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found