PHP PDO There is no active transaction

Question

I tried to run a query with PDO class and got this error message: "There is no active transaction" when trying to commit.

Here's my code:

  public function runExQuery($sql) {
        $preparedQuery = $this->connect()->prepare($sql);
        $this->connect()->beginTransaction();
        $preparedQuery->execute();
        $this->connect()->commit();
}



private function connect() {
        return new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database . '', $this->username, $this->password);
    }

What is the cause of this error? I explored previous posts of this type of question, but did not find any solutions.

Answer 1

Your ::connect() method is creating a new PDO each time you call it.

Since transactions do not survive outside of connections, the reconnecting wipes it out.

To correct this, store the PDO object as a class property:

class MyPdoClass
{
    private $pdo;
    // ... 

    public function connect()
    {
        if ($this->pdo instanceof PDO) {
            return;
        }
        $this->pdo = new PDO(// .... 
    }

and then reference it after calling connect:

//...
    public function runExQuery($query)
    {
        $this->connect();
        $this->pdo->prepare($query);
        // ... 
    }