php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #65994 PDO::prepare() with multi-queries doesn't work inside a transaction
Submitted: 2013-10-29 14:12 UTC Modified: 2014-02-25 12:56 UTC
From: pedro at sancao dot com dot br Assigned:
Status: Not a bug Package: PDO MySQL
PHP Version: 5.4.21 OS: Linux/Windows
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: pedro at sancao dot com dot br
New email:
PHP Version: OS:

 

 [2013-10-29 14:12 UTC] pedro at sancao dot com dot br
Description:
------------
Using PDO on MySQL 5.5.32 (Linux and Windows)

When executing a prepared multi-query statement after calling PDO::beginTransaction() the PDO::commit() will return true but no commit will be done.
Also the affected tables will be locked until the database server is restarted.

Nor error is raised neither exception is thrown.

My tests was within a try/catch block.

Test script:
---------------
$pdo = new PDO('mysql:dbname=PLACE_DATABASE;host=localhost;charset=utf8', 'PLACE_USER', 'PLACE_PASSWORD', array(
	PDO::ATTR_PERSISTENT => true,
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
));
try {
	$pdo->beginTransaction();
	$statement = $pdo->prepare('UPDATE ...; UPDATE ...; ');
	$statement->execute();
	$pdo->commit();
} catch (PDOException $e) {
	exit($e->getMessage());
	$pdo->rollBack();
}


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-01-01 12:27 UTC] felipe@php.net
-Package: PDO related +Package: PDO MySQL
 [2014-02-01 01:04 UTC] enrico_kaelert at kabelmail dot de
Its weird but use 
$stmt = null;
and THEN fire the commit()

it works.
Created a detailed report here: https://bugs.php.net/bug.php?id=66621

btw: the bug page search function is crap =(
 [2014-02-25 12:56 UTC] uw@php.net
-Status: Open +Status: Not a bug
 [2014-02-25 12:56 UTC] uw@php.net
It makes no sense to even try multi query using a prepared statement API call. Multi query is not available with PS. If at all it could be made working using query()/exec() but that would mean a security hole and was for sure out of spec for PDO (given there was a proper PDO spec)
 [2014-02-25 18:11 UTC] enrico_kaelert at kabelmail dot de
@uw@php.net
Whats the meaning of beginTransaction() and commit() then?
Adding those functions @PDO for single queries seems not "worth" to me.
Or do you recommend to execute each query like?:
function simpleExample(){
    $dbh->beginTransaction();
      
    $stmt = $dbh->prepare("INSERT INTO ...");  
    if(!$stmt->execute()){
        $dbh->rollBack();
        return false;
    }

    $stmt = $dbh->prepare("INSERT INTO ...");  
    if(!$stmt->execute()){
        $dbh->rollBack();
        return false;
    }

    $stmt = $dbh->prepare("INSERT INTO ...");  
    if(!$stmt->execute()){
        $dbh->rollBack();
        return false;
    }

    $dbh->commit();
    return true;        
}

And whats the security hole btw?
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Fri Dec 03 12:03:36 2021 UTC