Enabling autocommit in PDO on a mysql with autocommit set to off

Question

I've just been assigned to a legacy PHP codebase that worked without PDO.

Connections are done using this pattern:

// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);
 
// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

$con->autocommit(true);

And using that autocommit(true), even though the MySQL server is configured with SET autocommit = 0, works as expected.

Now I'm trying to migrate queries from plain SQL to prepared statements, so I wrote this

$dsn = "mysql:host=$host;dbname=$database";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);

and the following example function

function updateTeam($pdo, $name, $id)
{
    try {
        $data = [
            'name' => $name,
            'id' => $id
        ];
        $sql = "UPDATE teams SET name = :name WHERE id=:id";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);
        // $pdo->commit();
    }
    catch (PDOException $e) {
        db::$pdo->rollback();
        error_log('Failed: ' . $e->getMessage() );
    }
    catch (Exception $exc) {
        db::$pdo->rollback();
        error_log('Failed: ' . $exc->getMessage());
    }
}

doesn't commit. I have to uncomment the $pdo->commit(); to make it work, which is something i do not want, because that will force me to change every single query at the app.

What am I missing on the PDO config? I tried var_dump($pdo->query('SELECT @@autocommit')->fetchAll()); and the result was

array(1) { [0]=> array(2) { ["@@autocommit"]=> string(1) "0" [0]=> string(1) "0" } }

Answer 1

PDO's autocommit is not the same as MySQL's autocommit. This means that if the value of autocommit is set to default to 0 in your MySQL config, it doesn't change the default value of PDO::ATTR_AUTOCOMMIT. The default value of PDO::ATTR_AUTOCOMMIT is always true.

Changing the value of PDO::ATTR_AUTOCOMMIT to the same value or anything other than the bool or int is ignored. This means that both operations are no-op:

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, true); // because the value by default is true
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1); // converted to bool true, which is the same as the default

You can trick it by disabling and enabling the PDO::ATTR_AUTOCOMMIT:

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false);
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, true);

This will make two calls to MySQL server, which should set the MySQL's autocommit value to 1.

Alternatively, you could just execute SET autocommit = 1 to align the MySQL value with PDO's value.

$pdo = new PDO($dsn, $user, $password);
$pdo->exec('SET autocommit = 1');