PHP reset auto increment function no longer working after changing PDO settings

Question

I have a website using PDO for connecting to a MySQL database and i'm trying to improve its security.

After reading this post, i added the line $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); so the connection lines are now like this:

                  $this->conn =  new PDO("mysql:host=localhost;dbname=dbname;charset=utf8mb4", "user", "pass"); 
                  $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                  $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);     

After this change, the website's PHP functions appear to work fine, with the exception of this one:

    function resetidserials($table,$id){

            $sql = "SET @num := 0;
            UPDATE ".$table." SET ".$id." = @num := (@num+1);
            ALTER TABLE ".$table." AUTO_INCREMENT = 1;";
            $smt = $this->conn->prepare($sql);
            $smt->execute();
     }

This function is used to reset the auto increment ID numbers when a row is deleted. When the line $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); is not present it works as expected, when it is present i get a syntax error. Why it doesn't work and how can i get it working with the line present? Thanks in advance

Answer 1

MySQL's prepare() does not support multi-query. You cannot run multiple statements separated by semicolons when you use prepare.

Reference: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

If you set PDO::ATTR_EMULATE_PREPARES to false (which I agree is a good thing), then you must run each statement individually.

You don't need to use prepare() & execute() in your case, since your query has no parameters and you're only using it once anyway.

But there's no good reason to use multi-query anyway, whether you are using prepare or just executing the query directly. It's simpler to run the queries one at a time. Since none of these SQL statements has a result set, you can use PDO::exec().

Here is how I would code this:

function resetidserials($table,$id){

        $sql = "SET @num = 0";
        $this->conn->exec($sql);

        $sql = "UPDATE `$table` SET `$id` = @num := (@num+1)";
        $this->conn->exec($sql);

        $sql = "ALTER TABLE `$table` AUTO_INCREMENT = 1";
        $this->conn->exec($sql);
 }

Notice I have used back-ticks to protect you in case the table name or the id column name needs them. Also I used PHP variable interpolation syntax instead of string concatenation with .. I believe 98% of all use of PHP's . makes code less readable.

---

I must comment, though, that the whole idea of re-numbering the auto-increment id's in a table is not a good idea. The auto-increment id is not a row number. The values must be unique, not necessarily consecutive.

You do not need to reset auto-increment id's after a row is deleted. This may happen regularly, and you shouldn't renumber the whole table after every delete. What if the table has 100 million rows? What if the row deleted was the last row in the table anyway?

Also, gaps in the sequence of id's may occur for other reasons besides deletions. What if an INSERT is rolled back? What if an INSERT results in a conflict and then a subsequent INSERT uses the next higher id value? What if the auto-increment mechanism skips a value as it allocates the next id value (this does happen)?

One more comment: Using := in MySQL is discouraged. See https://dev.mysql.com/doc/refman/8.0/en/user-variables.html for the reasons.