Cron / Perl problems

[oiwio]

I have a Perl script that runs every 15 minutes off of a shell off of a cron. It goes through every member in my database and increases their HP by 15%

So I made this script with help from the people in this forum, but in the past two or three weeks it has been either been skipping over people, or it hasnt been getting to the end of the table, and I dont know why its doing this

hpup.pl

#!/usr/bin/perl

# PERL MODULE WE WILL BE USING
my $homedir = (getpwuid($>))[7];
my $n_inc = scalar @INC;
for (my $i = 0; $i < $n_inc; $i++ ) {
     if (-d $homedir . '/perl' . $INC[$i]) {
         unshift(@INC,$homedir . '/perl' . $INC[$i]);
         $n_inc++;
         $i++;
     }
}
use DBI;
print "Content-type: text/html \n\n";

# MySQL CONFIG VARIABLES
$host = "localhost";
$database = "*******";
$tablename = "users";
$user = "*********";
$pw = "**********";

# PERL MYSQL CONNECT 
$connect = DBI->connect("DBI:mysql:database=$database;host=$host", $user, $pw, {RaiseError => 1});
$sth = $connect->prepare("SELECT * FROM users");
$sth->execute() or die $dbh->errstr;
while ($ref = $sth->fetchrow_hashref()) {
my $add = $ref->{'maxhp'} * .15;
my $hp = $ref->{'hp'} + $add;
$hp = int($hp);
if($hp > $ref->{'maxhp'}){
$hp = $ref->{'maxhp'};
}
my $myquery = $connect->prepare("UPDATE users SET hp = '$hp' WHERE username = '$ref->{'username'}'");
#myquery = "INSERT INTO $tablename (hp) VALUES ($hp)";

$myquery->execute();
  }

Any help would be greatly appreciated

 

[marshian]

If the script has not changed and worked before, I'm guessing the script just timed out (there's a max. execution time). This would certainly be the case for not getting to the end of the table. The part where you say it skips over people is a little harder, but are you sure it has skipped them, since you might not know the order the script actually processes them? I mean, if you see the table on phpMyAdmin, it is already sorted, so you can't actually base yourself on that order.

Summary: I think it's just the case the script can't get to the end of the table before the maximum execution time has been reached.

A solution to this would be to make 2 scripts instead of one:
one processing the first 50% of your table, the second one processing the last 50% (make sure nobody gets double).

- Marshian

 

[oiwio]

Is there any way to lengthen the execution time? And if not, for the first script, I understand that you just put a LIMIT 200 (Im guessing) but how would I put it in the second one?

 

[marshian]

You can't lengthen the execution time, and I was about to give you php code to change the internal pointer, but that probably won't work in PERL xD.
PHP has mysql_data_seek(), but I've got no idea about PERL...

Google says:
$query->dataseek($row_number);
Maybe this will work...

 

[woiwky]

Try updating all of them with a single query. This would probably be much quicker than looping through all the results in perl and updating each one. It's a little complex since you don't want it to go over maxhp, but it's possible.

UPDATE users SET hp = (hp + (hp * 0.15)) - ((hp + (hp * 0.15) - maxhp) * CAST(((hp + (hp * 0.15)) > maxhp) AS UNSIGNED))

The other option would be two queries -- one which increases all hps by 15% and a second to decrease any that went over the maximum. I *think* this one is faster though.

If for some reason records are still being skipped, try breaking it up like marshian suggested.

 

[oiwio]

Ok, I tried youre code and got this out of it


Bareword found where operator expected at /home/oiwio/public_html/hpup2.pl line 30, near ") AS"
(Missing operator before AS?)
syntax error at /home/oiwio/public_html/hpup2.pl line 30, near ") AS UNSIGNED"
Execution of /home/oiwio/public_html/hpup2.pl aborted due to compilation errors.

 

[marshian]

I 'think' this is what your code should look like:

#!/usr/bin/perl

# PERL MODULE WE WILL BE USING
my $homedir = (getpwuid($>))[7];
my $n_inc = scalar @INC;
for (my $i = 0; $i < $n_inc; $i++ ) {
     if (-d $homedir . '/perl' . $INC[$i]) {
         unshift(@INC,$homedir . '/perl' . $INC[$i]);
         $n_inc++;
         $i++;
     }
}
use DBI;
print "Content-type: text/html \n\n";

# MySQL CONFIG VARIABLES
$host = "localhost";
$database = "*******";
$tablename = "users";
$user = "*********";
$pw = "**********";

# PERL MYSQL CONNECT 
$connect = DBI->connect("DBI:mysql:database=$database;host=$host", $user, $pw, {RaiseError => 1});
$sth = $connect->prepare("UPDATE users SET hp = (hp + (hp * 0.15)) - ((hp + (hp * 0.15) - maxhp) * CAST(((hp + (hp * 0.15)) > maxhp) AS UNSIGNED))");
$sth->execute() or die $dbh->errstr;

 

[woiwky]

Ok, I tried youre code and got this out of it


Bareword found where operator expected at /home/oiwio/public_html/hpup2.pl line 30, near ") AS"
(Missing operator before AS?)
syntax error at /home/oiwio/public_html/hpup2.pl line 30, near ") AS UNSIGNED"
Execution of /home/oiwio/public_html/hpup2.pl aborted due to compilation errors.

Are you sure you used that code as a mysql query? Maybe you missed a quotation mark or something. That error sounds like it's trying to read it as perl code instead of just a string. I can assure you that the query is valid mysql. Try using the code marshian provided.

 

[oiwio]

Ok, that was my mistake, I put it into the mysql execution line just like Marshian has it, and it works.

Only problem now is that its only increasing it by 6%. But that is probably just a mathematical error, no real problem

Thanks to both of you for your help

 

[TechAsh]

I'll close this now as it's been solved, if you need any more help you may re-open the thread.

*Closed*