PHP/MySQL PDO - getting a lastInsertId but table is missing the inserted row

Question

Very strange behaviour occurring. I have an API in PHP that takes a transaction and performs an insert into a ITEMS table. The table has an auto incrementing ID field.

When I perform an INSERT I retrieve the lastInsertId which I use to perform a LogActivity entry.

There are several people using the APP. I noticed that in the LogActivity table, there are some entries with IDs but those IDs aren't in the ITEMS table.

How is it possible that I can retrieve the lastInsertId but that ID isn't there. It's like the INSERT was performed, but then backed out??

Is this a concurrency issue? The table is INNODB. I'm not using TRANSACTION or LOCK or TRY/CATCH.

Any ideas would be appreciated.

//products.php
class Product{

    // database connection and table name
    private $conn;
    private $table_products = "table_products";
    private $table_productitems = "table_productitems";
    private $table_location = "warehouse_locations";

    // object properties
    public $id;
    public $productname;
    public $description;
    public $quantity;
    public $productid;
    public $customerid;
    public $thisid;
    public $thisitemid;
    public $productqty;
    public $newproductid;
    public $updateitemid;
    public $updateqty;
    public $type;
    public $result;
    public $insertitemid;
    public $insertqty;
    public $insertqrcode;
    public $searchStr;
    public $csproductname;
    public $cssku;
    public $csproductid;

    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }

function addProductItemQRCode(){

    // query to insert record
    $query = "INSERT INTO table_productitems
              SET productid = :productid, quantity = :productqty, qrcode = :qrcode, modifydate = now()";

    // prepare query
    $stmt = $this->conn->prepare($query);

    // bind values
    $stmt->bindParam(':productid', $this->insertitemid);
    $stmt->bindParam(':productqty', $this->insertqty);
    $stmt->bindParam(':qrcode', $this->insertqrcode);

    // execute query
    if($stmt->execute()){
        $this->thisinsertid = $this->conn->lastInsertId();
    } else {
        $this->thisinsertid = '';
    }       

} 

// Activity function

function logActivity($customerid,$type,$result,$quantity,$userid){
    // query to insert record
    $query = "INSERT INTO api_activity 
                SET customerid=:customerid, type=:type, 
                    result=:result, quantity=:quantity, userid=:userid";

    // prepare query
    $stmt = $this->conn->prepare($query);

    // bind values
    $stmt->bindParam(':customerid', $customerid);
    $stmt->bindParam(':type', $type);
    $stmt->bindParam(':result', $result);
    $stmt->bindParam(':quantity', $quantity);
    $stmt->bindParam(':userid', $userid);

    // execute query
    if($stmt->execute()){
        return true;
    }
    return false;
}

// Addproductitems.php

// instantiate productitem insert object
$productiteminsert = new Product($db);
$productiteminsert->customerid = $post_customerid;
$productiteminsert->insertitemid = $productid->pid;
$productiteminsert->insertqty = $post_quantity;
$productiteminsert->insertqrcode = $post_qrcode;

// query products
$productiteminsert->addProductItemQRCode();

if($productiteminsert->thisinsertid!=null){

    $newitem = $productiteminsert->thisinsertid;

    // log the activity
    $product->logActivity($customerid=$productiteminsert->customerid, 
$type='add stock', $result=$productiteminsert->insertitemid.' ['.$newitem.'] ['.$productiteminsert->insertqrcode.']', 
$quantity=$productiteminsert->insertqty, $userid=$userid);
}

The App posts to the Addproductitems.php.

It would really help to be able to see the code that is causing this strange activity — RiggsFolly, Jun 19 '20 at 15:24
Also, how does the `LogActivity table` relate to the `items` table. The relevant parts of the schema would also be useful — RiggsFolly, Jun 19 '20 at 15:25
Add foreign key constraint to the log table, at least you will have a proper data integrity. You also need to share the code - Maybe there's something you're missing out — Alon Eitan, Jun 19 '20 at 15:27
When I perform the insert to the ITEMS table, I get the lastInsertId and pass it to a function that logs the insert. The activity table simply puts the ID into a results field along with some other information so there is no need for foreign key constraints. — webpointz, Jun 19 '20 at 15:31
It just happened again. This time, the missing entry was one minute after the previous entry and one minute before the next, so it's not concurrency. I'll post the code in the original comment. — webpointz, Jun 19 '20 at 15:33
But you said it yourself: _"...there are some entries with IDs but those IDs aren't in the ITEMS table"_ so there must be a bug somewhere :) — Alon Eitan, Jun 19 '20 at 15:34
How is it that the INSERT returns an ID when the insert doesn't appear in the table with that ID? — webpointz, Jun 19 '20 at 15:38
the activity table might have an entry with the results field "46088 [25595] [E-4-3]". The second number "[25595]" is the lastinsertid it obtained from the insert into the ITEMS table, but you go to the ITEMS table and the auto incrementing rows show ...25593, 25594, 25596, 25597... — webpointz, Jun 19 '20 at 15:45
My understanding of INNODB is that the commit is AUTO so I don't understand how it returns a lastinsertid without the INSERT being performed. — webpointz, Jun 19 '20 at 15:47
I assume that `$this->thisinsertid` and `$productiteminsert->thisinsertid` are in fact addressing the same object and instance ? — RiggsFolly, Jun 19 '20 at 15:49
When I asked for the code, I ment the relevant code and all the relevant code. Anything could be going on here where this fragments of code actually it is far from obvious to us that do not know this code — RiggsFolly, Jun 19 '20 at 15:50
this is not tested, but by any chance do you have __INSERT IGNORE__ in your insertion code? or some chances of where the insertion may fail? — Ajowi, Jun 19 '20 at 15:57
Or is there some other bit of code that might be deleting the row before you get around to looking for it — RiggsFolly, Jun 19 '20 at 15:57
Added more code. NO on the INSERT IGNORE and now DELETE either. — webpointz, Jun 19 '20 at 15:58
Just for clarity, today, there has been over 180 inserts into both tables and only 2 or 3 times where this issue has occurred. — webpointz, Jun 19 '20 at 16:17

score 1 · Answer 1 · answered Jun 19 '20 at 16:21

1

First, the following link my help you understand what is happening behind the scenes: https://stackoverflow.com/a/36573967/5922263 From that reading, please check if your functions (I assume these are assorted functions, and not methods as they do not contain qualifiers like public, private, etc) are doing any of these:

- creating some new connection by themselves; or
- doing multiple insertions at times; or 
- triggering some post-actions which modifies your 'global' $this->thisinsertid

This is not a direct solution but it might help point you in the right direction.

answered Jun 19 '20 at 16:21

Ajowi

449
3
12

Thanks...I have added to my code above. The PRODUCT.PHP object file includes CLASS and PRIVATE and PUBLIC declarations. – webpointz Jun 19 '20 at 16:25
Update: I added TRY/CATCH as well as a manual "BeginTransaction" and "commit" around the INSERT. Same thing happened this morning. An entry was logged but the insert item wasn't in the table. – webpointz Jun 22 '20 at 13:01

PHP/MySQL PDO - getting a lastInsertId but table is missing the inserted row

1 Answers1