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.

Does `$this->connect()` create a NEW PDO object each time it is called? If so, you need to reuse the same object. — Mike, Jun 01 '15 at 21:25

Bryan Agee · Accepted Answer · 2015-06-01T21:34:56.347

3

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);
        // ... 
    }

edited Jun 01 '15 at 21:34

answered Jun 01 '15 at 21:28

Bryan Agee

4,924
3
26
42

After you posted the content of your connect method, I added some detail; see if that helps sort it out. – Bryan Agee Jun 01 '15 at 21:35

Mike · Answer 2 · 2015-06-01T21:47:28.513

You are creating a new PDO object every time you call $this->connect(), so if you have:

$stmt1 = $this->connect()->prepare(" ... ");
$stmt2 = $this->connect()->prepare(" ... ");

$stmt1 and $stmt2 will actually be completely different PDO objects, so if you start a transaction with one object, it will NOT apply to the other one. Instead you should save a PDO object and reference that instead of creating a new one every time.

Most of the time, I find it easier just to pass this to the class's constructor, however if you want to do minimal editing, you could just do:

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

However you might want to change the name connect() to something a bit more logical, like getDbh().

If instead you wanted to put it in the object's constructor, you could do this:

class YourClass {
    private $dbh;
    public function __construct(PDO $dbh) {
        $this->dbh = $dbh;
    }
}

$dbh = new PDO('mysql:host=' . $host . ';dbname=' . $database, $username, $password);
$yourclass = new YourClass($dbh);

Then in any of the other class methods, you would just reference $this->dbh. Using your code as an example:

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

Personally, this is the way I would do it.

PHP PDO There is no active transaction

2 Answers2