in reply to Re^3: How to Limit MySql Execution Time?
in thread How to Limit MySql Execution Time?

Yes, I did.

But that document is for running MySql from Shell, a command tool operation. I'm running MySql from script. Have no idea how to convert the MySql commands into scripting environment.

You've shown a script how to do it: 
my $sql = ...;
my $cmd = qq[mysql --quick --raw -u $user -p$pass -be $sql]
my @results;
eval{
    alarm 5;
    my $pid = open SQL, "$cmd |" or die $!;
    push @results, <SQL>;
    alarm 0;
};
if( $@ ) {

}

[download]

I don't understand some of your commands and how to implement them into my script.

You bought up this solution of using MySql command tool to limit MySql execution time in reply to my query. You have obviously seen my script. It will help if you can edit my script to show me how to implement the solution that you suggested.

Thanks so much.

Replies are listed 'Best First'.
Re^5: How to Limit MySql Execution Time?
by BrowserUk (Patriarch) on Dec 22, 2010 at 03:53 UTC
    But that document is for running MySql from Shell, a command tool operation. I'm running MySql from script.

    Yes. The crux of my suggestion was that instead of using DBI to perform your DB queries, you use the mysql command line tool to perform them.

    The posted pseudo code was suggesting that you pass the sql query as a command line argument to the mysql executable as a -e command line parameter, and then read back the results via piped open.

    As the DB interaction would be taking place in a separate process, you could then kill that separate process if it takes longer than your required time. As a parent process can always kill a child process, this methods guarantees timeliness. And as the code that would be interrupted is in a separate process that is then thrown away, it avoids any dangers of potential corruption.

    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
      OK..Fine and Dandy.

      But you've not responded to my request of editing my script to show me how to do them.

      Here's my script again, kindly edit them: 
      #!/usr/bin/perl 

require 'myconfigure.cgi';
$timeout = 5;
use DBI;
$keywords = "Whether rocking natural-curls, a-short";

print "Content-type: text/html\n\n";
sub do_search {

    $time1 = time;

    $dbh=DBI->connect("dbi:mysql:$database:localhost","$username","$pa
+ssword");

    $keywords_quoted = $dbh ->quote ($keywords);
    $query="SELECT count(*) FROM $websites_table WHERE MATCH(title) AG
+AINST ($keywords_quoted)";
    $sth=$dbh->prepare(qq{$query});
    $sth->execute();
    $total_count = $sth->fetchrow_array ();
    $sth->finish;
    $dbh->disconnect ||die("Couldn't disconnect to database!\n");
    $time2 = time;
    $time_ran = $time2 - $time1;

}

eval {        
    local $SIG{ALRM} = sub { die "alarm\n" }; 
    alarm $timeout;        
    &do_search;        
    alarm 0;    
}; 

if($@) {        
    $time2 = time;
    $time_ran = $time2 - $time1;

    print "$@, Time Ran: $time_ran, Total Count: $total_count\n";
    exit;

}

print "Time Ran: $time_ran, Total Count: $total_count\n";
exit;

      [download]
[reply]
[d/l]
        OK..Fine and Dandy. But you've not responded to my request of editing my script to show me how to do them. Here's my script again, kindly edit them:

        Contact me via the email address on my home node and we can discuss my rates.

        Alternatively, make some effort yourself and then post the code that shows that, along with a description of the problems you are having and maybe someone here will see fit to expend some of their time to assist you.

        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        "Science is about questioning the status quo. Questioning authority".
        In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]

In Section Seekers of Perl Wisdom