[RESOLVED] how should I deal with a slowed-down sql server ??

VisionsOfCody

I've got a site on shared hosting with a mysql server

what's the best way of being absolutely 100% sure that data gets inserted ?

If, for some reason the server query times-out because of heavy server load or something, is there a way of re-sending the query until I'm sure it's been inserted ?

the reason I'm asking is that occasionally a product order doesn't get inserted - I'm currently reviewing and re-working all the code to try to make it more fail-safe but I'd also like to implement some code to rule out any possibility of it coming from the server.

I thought maybe of getting the code to send an email if an INSERT query fails - could this be a bad idea for any reason ?

thanks

MarkR

If you want performance, don't use a shared server.

On a shared server you're entirely at the mercy of the idiots you're sharing it with; they will write lousy applications which do too many queries, omit vital indexes and get heavy traffic.

If you want to make an application work, use dedicated hosting. Really.

Mark

VisionsOfCody

I was kind of afraid someone might say that

but how about the question about getting notification if an INSERT fails for whatever reason - how would i go about doing that ?

thanks

MarkR

You should be operating with error_reporting(E_ALL) in both development AND production (contrary to what the documentation may suggest; ignoring errors is never a good idea).

Write an error handler (for your entire application) which intercepts any unmasked error and logs the circumstances of it. It should also show the user an error message and stop the script.

You may also want to send a mail inside the error handler, but this should be done with caution (for example, rate-limit these mails by recording how many have been sent in a file) to ensure that you don't end up spamming yourself in the event that something (e.g. an evil/lame web robot) causes a large number of errors.

Mark

Roger_Ramjet

mysql_query will return TRUE if an insert query worked and FALSE if there was an error, so you should be checking that as a matter of course. mysql_affected_rows can also be used to check what happened ie how many rows were inserted or updated etc.


$result = mysql_query($insert_query);
if (!$result) {
  // error handling routine cos return was false meaning error
}

$r = mysql_affected_rows;

if ($r == 0) {
   // error handler cos the query did not insert any rows
} else {
   echo $r . " records inserted.";
}

You should be checking how many rows were affected on any insert/update/delete query anyway and reacting to any anomolies. That is just basic database technique regardless of the platform: eg someone puts the wrong parameter in and you update/delete every row, you need to know about that right away. At the least it is good practice to let the user know what happened so they can react to any unexpected behaviour.

VisionsOfCody

Thanks MarkR and Roger Ramjet

there are some good points there and it makes me realize that I have seriously neglected the error-handling side of things

although, to be fair, until now I've only really done php to serve pages and this is the first time i've had to deal with taking product orders from clients (albeit on a very small scale)

so - thanks for that info

cheers

Sxooter

Note you may also want to use a repeating attempt to insert the same data on error with a timeout.

<?php
$max_tries = 4;
$sleep=10;
include dbconnect.inc;
$count=0;
$dropout=false;
while ($count<$max_tries || $dropout == false){
    $res = mysql_query($insert_query);
    if (!$res) {
        $count++;
        sleep($sleep);
        $error=true;
        $dropout=true;
    } else {
        $dropout = true;
        $error=false;
    }
}
if ($error) {
    print "someone set us up the bomb";
}

etc...

That's just a code fragment, could use some work I'm sure, but you get the idea.