php mysql order of operations, seems that deletes happen all at once

Question

I have a script that uses a database table as a buffer.

It first pulls a list of objects from another table, then runs a loop that calls an API for each object. For each API answer I create an insert statement into the buffer table with the current iteration object's key and corresponding data and a delete statement for that object's key. Then I first run a delete to get that object's records out of the table and then insert the new and fresh data.

This happens about every 5 minutes and the script runs for about 2 minutes. Issue I'm running into that I get all the elements deleted from the table. I checked the data with R studio, it fluctuates about every 5 minutes.

Since it deletes on every object's call, if this delete caused the issue, the table would be empty all the time. If everything runs sequentially.

Here is the function that runs a call for the single object:

function singleProp( $pId , $wispId , $nasId , $req){

  $res_j = apiCall( $wispId , $nasId );
  $count = get_value_by_tag($res_j , "COUNT" );

  $aps = array(); 

  $no_down = 0;

  $db_con = new db_connector();
  $db_con->connect();

  $stmt_d = $db_con->dblink->prepare( "DELETE FROM ap_down WHERE prop_id = ? ;" );
  $stmt_d->bind_param("i",$pId);
  $stmt_d->execute();

  $stmt_i = $db_con->dblink->prepare("INSERT INTO ap_down ( prop_id , ip , prop_type , description , time ) VALUES (?,?,?,?,now());");

  for( $i = 1 ; $i <= $count ; $i++ ){

    if( get_value_by_tag($res_j , "APSTATUS"     , $i ) == "DOWN" ){

      $stmt_i->bind_param("issd" , $pId , get_value_by_tag( $res_j , "ACCESSPOINTS" , $i ) , $req , get_value_by_tag( $res_j , "DESCRIPTION"  , $i ) );

      $stmt_i->execute();

      $no_down ++ ;

    }
  } 

}

Any ideas, suggestions, other data you guys might want to see?

EDIT: using prepared statement since it's safer and makes it easier to read. EDIT: Code indentation.... because important.

This looks terrifyingly insecure. Are you **sure** your user parameters are [properly escaped](http://bobby-tables.com/php)? It seems you're just jamming in raw values into your `mysqli` connection and aren't using the proper [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) method to add user data to your query. — tadman, Aug 15 '14 at 18:07
Yes, it's all internal. There is no "user data" per say. Someone has to get into a monitoring software's API and have it feed infected data to do injections. The real question is the data values I'm getting, thank you for noticing though. — Tim Zhukov-Khovanskiy, Aug 15 '14 at 18:22
It doesn't matter where the values come from, or how "trusted" they are, you should compose your queries with placeholders to avoid escaping issues and the serious bugs that come from having them. There's no excuse for reckless programming, especially if these become habits you apply elsewhere and get into real trouble because of them. As a bonus it makes your queries easier to read and understand since the data is applied separately from the query structure. — tadman, Aug 15 '14 at 18:31
Thank you for your answer, I will get a prepared statement for this. But in regards to the topic, do you have any idea why this would happen? — Tim Zhukov-Khovanskiy, Aug 15 '14 at 18:34

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

You may have an error on your INSERT statement that you're not catching. Unless you've enabled exception reporting you might be missing these.

You may find one of the following helps:

1. Use INSERT INTO with ON DUPLICATE KEY to replace the existing record rather than deleting it each time. This requires a unique index on prop_id but you should have one already based on how you're using the table.

2. Use REPLACE INTO to completely rewrite the record.

3. Create a transaction to encapsulate the DELETE and INSERT operations so that it's an all-or-none situation and you're not left with an incomplete update.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 15 '14 at 20:02

tadman

208,517
23
234
262

The insert statements are not having an issue, they do work separately. Replace into will not work since I do not know how many records are there per prop_id (not a pk). It's really not a record update, but rather a replacement of a record set with a given identifier (prop_id) with another set pulled from the API. – Tim Zhukov-Khovanskiy Aug 18 '14 at 13:50

score 0 · Accepted Answer · answered Aug 15 '14 at 20:31

Your problem seems to be one of concurrent access to the ap_down table. You should make sure that all operations are isolated the ones from the others.

Reading process:

LOCK TABLES ap_down READ;
SELECT ...;
UNLOCK TABLES;

Write process (your script):

LOCK TABLES ap_down WRITE;
DELETE ...;
INSERT ...;
UNLOCK TABLES;

You could do the write process a different way so as to only lock if inserting, and deleting immediately after locking:

// Do not lock -- yet.
$stmt_d = $db_con->dblink->prepare( "DELETE FROM ap_down WHERE prop_id = ? ;" );
$stmt_d->bind_param("i",$pId);
// Do not execute deletion -- yet.
$deleted = false;

$stmt_i = $db_con->dblink->prepare("INSERT INTO ap_down ( prop_id , ip , prop_type , description , time ) VALUES (?,?,?,?,now());");

for( $i = 1 ; $i <= $count ; $i++ ){
    if ( get_value_by_tag($res_j , 'APSTATUS', $i ) != 'DOWN' ){
        continue;
    }
    $db_con->dblink->query('LOCK TABLES ap_down WRITE;');
    if (!$deleted) {
        $deleted = true;
        $stmt_d->execute();
    }
    $stmt_i->bind_param('issd', $pId, get_value_by_tag( $res_j, 'ACCESSPOINTS', $i ), $req , get_value_by_tag( $res_j, 'DESCRIPTION', $i ) );
    $stmt_i->execute();
    $db_con->dblink->query('UNLOCK TABLES;');
    $no_down ++ ;
} 
if (!$deleted) {
    // Delete without locking.
    $deleted = true;
    $stmt_d->execute();
}

php mysql order of operations, seems that deletes happen all at once

2 Answers2