CODEWITHSUNDEEP
phpmysql
Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

Fast inserts results in data loss

I have a strange bug here related to mysql and php. I'm wondering if it could be a performance problem on our server's behalf too.

I got a class used to manage rebate promotional codes. The code is great, works fine and doesn exactly what it is supposed to do. The saveChanges() operation sends an INSERT or UPDATE depending on the state of the object and in the current context will only insert cause i'm trying to generate coupon codes.

The classe's saveChanges goes like this: (I know, i shouldn't be using old mysql, but i've got no choice due to architectural limitations of the software, so don't complain about that part please)

public function saveChanges($asNew = false){

    //Get the connection
    global $conn_panier;

    //Check if the rebate still exists
    if($this->isNew() || $asNew){

        //Check unicity if new
        if(reset(mysql_fetch_assoc(mysql_query('SELECT COUNT(*) FROM panier_rabais_codes WHERE code_coupon = "'.mysql_real_escape_string($this->getCouponCode(), $conn_panier).'"', $conn_panier))) > 0){
            throw new Activis_Catalog_Models_Exceptions_ValidationException('Coupon code "'.$this->getCouponCode().'" already exists in the system', $this, __METHOD__, $this->getCouponCode());
        }

        //Update the existing rebate
        mysql_query($q = 'INSERT INTO panier_rabais_codes 
                        (
                            `no_rabais`,
                            `code_coupon`,
                            `utilisation`,
                            `date_verrou`
                        )VALUES(
                            '.$this->getRebate()->getId().',
                            "'.mysql_real_escape_string(stripslashes($this->getCouponCode()), $conn_panier).'",
                            '.$this->getCodeUsage().',
                            "'.($this->getInvalidityDate() === NULL ? '0000-00-00 00:00:00' : date('Y-m-d G:i:s', strtotime($this->getInvalidityDate()))).'"
                        )', $conn_panier);
        return (mysql_affected_rows($conn_panier) >= 1);

    }else{

        //Update the existing rebate
        mysql_query('UPDATE panier_rabais_codes
                        SET
                            `utilisation` = '.$this->getCodeUsage().',
                            `date_verrou` = "'.($this->getInvalidityDate() === NULL ? '0000-00-00 00:00:00' : date('Y-m-d G:i:s', strtotime($this->getInvalidityDate()))).'"
                        WHERE 
                            no_rabais = '.$this->getRebate()->getId().' AND code_coupon = "'.mysql_real_escape_string($this->getCouponCode(), $conn_panier).'"', $conn_panier);
        return (mysql_affected_rows($conn_panier) >= 0);

    }

}

So as you can see, the code itself is pretty simple and clean and returns true if the insert succeeded, false if not.

The other portion of the code generates the codes using a random algorithm at goes like this:

while($codes_to_generate > 0){

    //Sleep to delay mysql choking on the input
    usleep(100);

    //Generate a random code
    $code = strtoupper('RC'.$rebate->getId().rand(254852, 975124));
    $code .= strtoupper(substr(md5($code), 0, 1));
    $rebateCode = new Activis_Catalog_Models_RebateCode($rebate);
    $rebateCode->setCouponCode($code);
    $rebateCode->setCodeUsage($_REQUEST['utilisation_generer']);
    try{
        if($rebateCode->saveChanges()){
            $codes_to_generate--;
            $generated_codes[] = $code;
        }
    }catch(Exception $ex){
    }

}

As you can see here, two things to note. The number of codes to generate and the array of generated codes only get filled if i get a return true from the saveChanges, so mysql HAS to report that the information was inserted for this part to happen.

Another tidbit is the first line of the while:

//Sleep to delay mysql choking on the input
usleep(100);

Wtf? Well this post is all about this. My code works flawlessly with small amounts of codes to generate. But if i ask mysql to save more than a few codes at once, i have to throttle myself using usleep or mysql drops some of these lines. It will report that there are affected rows but is not saving them.

Under 100 lines, i don't need throttling and then i need to usleep depending on the amount of lines to insert. It must be something simple but i don't know what. Here is a sum of the lines i tried to insert and the minimum usleep throttle i had to implement:

Thank you for your time

Upvotes: 0

Views: 124

Answers (1)

moechofe
moechofe

Reputation: 101

Are you sure that your codes are all inserted and not updated, because, update a non existing line does nothing.

Upvotes: 2

Related Questions