How to get the value of expression from LAST_INSERT_ID(`my_column`+1)?

Question

DB Type: MariaDB
Table Engine: InnoDB

I have a table where inside it has a column with a value which is being incremented (not auto, no inserting happens in this table)

When I run the following SQL query in phpMyAdmin it works just fine as it should:

UPDATE `my_table` 
    SET `my_column` = LAST_INSERT_ID(`my_column` + 1) 
WHERE `my_column2` = 'abc'; 
SELECT LAST_INSERT_ID();

The above returns me the last value for the my_column table when the query happened. This query was taken directly from the mysql docs on locking: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html (to the bottom) and this seems to be the recommended way of working with counters when you don't want it to be affected by other connections.

My PDO:

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
    $sql = "UPDATE `my_table` 
                SET `my_column` = LAST_INSERT_ID(`my_column` + 1) 
                WHERE `my_column2` = 'abc'; 
            SELECT LAST_INSERT_ID();";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    $result = $stmt->fetchColumn(); // causes general error
    $result = $stmt->fetch(PDO::FETCH_ASSOC);// causes general error

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() . " records UPDATED successfully";
        
} catch(PDOException $e) {            
    echo $sql . "<br>" . $e->getMessage();        
}
$conn = null;

Exact error SQLSTATE[HY000]: General error, If I remove the lines where I try to get the result, it updates the column, but I still do not have a return result... how do I perform that update query and get the select result all in one go like I do when I run it in phpMyAdmin? This all needs to happen in one go as specified by the MySQL docs so I don't have issues where two connections might get the same counter.

Do not catch exceptions for no reason. Catching and displaying error message is completely pointless. — Dharman, Jul 11 '20 at 14:12
@Dharman thanks, but how would i get the value im trying to? Also I grabbed the snippet for the PDO from a tutorial, I've never actually used PDO before but I want to move to it. Why shouldn't I catch an exception so I can later maybe perform a task like creating a log which I can later review? I just posted the snippet I'd do more work if an exception actually occured — Uriahs Victor, Jul 11 '20 at 14:16
For reading https://www.php.net/manual/en/pdo.lastinsertid.php — RiggsFolly, Jul 11 '20 at 14:18
Error logging should be a generic task for your application. If you do not know how to implement a complex error logger then leave it up to PHP. PHP has a simpler error logger built-in. Catching PDO errors to display the error message does nothing useful, since the exception will be converted to a text message anyway. — Dharman, Jul 11 '20 at 14:18
@Dharman I thought PDO could execute multiple queries at once, presuming emulation is on. — user3783243, Jul 11 '20 at 14:22
Maybe https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd is outdated? — user3783243, Jul 11 '20 at 14:25
@user3783243 You are correct, but it should not be done anyway. There is no reason for it. — Dharman, Jul 11 '20 at 14:26
@Dharman Surely, there would be no `LAST_INSERT_ID()` available directly after making a new conections — RiggsFolly, Jul 11 '20 at 14:34
@RiggsFolly OP is using custom sequence. It works slightly different in that case. It takes a custom expression. You use it instead of AUTO_INCREMENT — Dharman, Jul 11 '20 at 14:35
@Dharman what is unclear? I have a table which has a counter column, in PHP I want to (in one fell swoop, one connection, one ping) to update that counter column, and grab the value it was just updated to. So if the counter was on 2, the return should be 3 (because it updated it to +1, then grabbed it using LAST_INSERT_ID ) LAST_INSERT_ID function is acting on the counter column, so that would be the value it should return I did some reading using your link and it seems to work $conn->lastInsertId() — Uriahs Victor, Jul 11 '20 at 14:36
@Dharman I'm doing this all in the sake of data integrity, there's a possibility where two connections might be trying to get the counter value at the same time, thats when I stumbled upon SELECT FOR UPDATE, but as i was reading the docs, I saw that the mysql docs was doing exactly what i wanted to do, and they mentioned that "In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:" then they showed the snippet which I used in my question: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html — Uriahs Victor, Jul 11 '20 at 14:39
Generally, the auto-generated ID is done for primary keys only and using SQL AUTO_INCREMENT which was what threw all of us off. If you are generating custom sequences for other columns than primary key then you can use this approach. The question would be more clear if you have given us your schema — Dharman, Jul 11 '20 at 14:42

Dharman · Accepted Answer · 2020-07-11T21:59:15.940

6

There is no need to perform SELECT LAST_INSERT_ID();. PDO will save that value automatically for you and you can get it out of PDO.

Simply do this:

$conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
        
$sql = "UPDATE `my_table` 
            SET `my_column` = LAST_INSERT_ID(`my_column` + 1) 
            WHERE `my_column2` = 'abc'";

// Prepare statement
$stmt = $conn->prepare($sql);

// execute the query
$stmt->execute();

$newID = $conn->lastInsertId();

lastInsertId() will give you the value of the argument evaluated by LAST_INSERT_ID().

edited Jul 11 '20 at 21:59

answered Jul 11 '20 at 14:40

Dharman

30,962
25
85
135

can you safely say this would prevent multiple connections from getting duplicate counters? Or would a SELECT FOR UPDATE be a better approach? This approach you have above seems like its 99.9% proof but Im not a DBA – Uriahs Victor Jul 11 '20 at 14:47
@UriahsVictor `LAST_INSERT_ID` is only ever going to give you the ID for the current session. It is safe. There's no way you will get an ID from another connection. – Dharman Jul 11 '20 at 14:48
I also had wrote a quick test using `->query()` as there is nothing really worth preparing, and that worked, Totally surprised! But learned something, so thats good. Not sure if its supposed to do this, it does not really make a whole lot of sense :) – RiggsFolly Jul 11 '20 at 15:03

How to get the value of expression from LAST_INSERT_ID(`my_column`+1)?

1 Answers1