Unexpected results in PDO Transactions

Question

My problem is kinda simple or it maybe a silly mistake from my side. But I don't know I am just getting unexpected results while using PDO in php.

Code goes like this,

try
{
    $_pdo = get_pdo_instance();

    $_pdo->beginTransaction();

    //query 1
    $_pdo->query("some query"); // I have error in query 3 but this query 1 is still executed.

    //query 2
    $_pdo->query("some query"); // only executes when there are no errors.

    //query 3
    $_pdo->query("some wrong query"); // let's say I have an error in this sql

    $_pdo->commit();

}
catch(Exception $ex)
{
    $_pdo->rollback();
}

I am explaining the problem now, In given example I have some sql error in query 3, so none the queries should run as they all belongs to single transaction.

But in my case query 1 always run even if there are errors in that try block.

Maybe its something simple but I have no idea why this is happening.

Edit: Function definition,

function get_pdo_instance()
{
    try 
    {
        $conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } 
    catch(PDOException $e) 
    {
        die('ERROR: ' . $e->getMessage());
    }

    return $conn;
}

I think you are missing this: http://stackoverflow.com/a/8992933/1169798 You have to tell PDO to throw Exceptions upon failed queries. Otherwise it will just return `false` from the query and proceed. — Sirko, Jan 15 '16 at 18:17
@Jordy I have `get_pdo_instance()` function defined somewhere in my file. This functions creates and return the pdo connection. — Indian Dollar, Jan 15 '16 at 18:21
@Sirko I will give that a try, but why only query 1 is executed but not query 2 ? — Indian Dollar, Jan 15 '16 at 18:22
Does your query have a database definition language (DDL) statement? Statements such as `DROP TABLE` and `CREATE TABLE` get committed automatically, even if you later rollback. — Cedric Ipkiss, Jan 15 '16 at 18:22
Just for completeness sake: have you checked the return value of `beginTransaction()`? — Sirko, Jan 15 '16 at 19:08

score 0 · Answer 1 · answered Jan 16 '16 at 08:26

0

Exceptions are used only for PHP errors. In the code above, you're trying to catch a MySQL error. When PHP reads your try block, everything looks fine, and it therefore has no exception to catch. You can do error checking with PDO and MySQL like:

if(!$_pdo->query("some query")) {
// Do something
}

answered Jan 16 '16 at 08:26

Cedric Ipkiss

5,662
2
43
72

1

One can set PDO to actually throw exceptions - see the other answer. But your solution would be valid as well. – Sirko Jan 16 '16 at 09:39

score 0 · Answer 2 · answered Jan 16 '16 at 08:59

0

See the documentation for PDO Error Handling. You need to turn exception raising on.

try {
  $_pdo = get_pdo_instance();
  $_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $_pdo->beginTransaction();
  $_pdo->query("some query");
  $_pdo->query("some query");
  $_pdo->query("some wrong query");
  $_pdo->commit();
}
catch(Exception $ex) {
  $_pdo->rollback();
}

answered Jan 16 '16 at 08:59

br3nt

9,017
3
42
63

It is ON, check my function code in my post. Or do you think I have to do it outside the function ? If so, what's the reason ? – Indian Dollar Jan 16 '16 at 18:37
Ah ok. I missed that. You shouldn't have to do it outside the function definition. Are you sure the that the SQL statements are invalid and code execution is going into the catch block? If the SQL is valid then no exception will be raised. Also, can you clarify... are you expecting a PDOException to be raised when 0 results are returned? – br3nt Jan 17 '16 at 02:14

score 0 · Answer 3 · answered Jan 17 '16 at 09:43

0

Here is my solution,

I was catching Exception, instead in this case I had to do catch PDOException.

I guess that solved my problem, but I haven't tested this thing completely. I will post updates if any.

answered Jan 17 '16 at 09:43

Indian Dollar

301
4
13

1

Catching `\Exception` would catch `PDOException`. – N.B. Jan 17 '16 at 10:42
@N.B. I am personally not sure about this, so I didn't selected my answer as correct. Still checking things. – Indian Dollar Jan 19 '16 at 03:14

Unexpected results in PDO Transactions

3 Answers3