Ok, second post for the PHP5 board today. This one, I've tested and recreated a small example, so I'm pretty sure I need help with this one :p

When I execute the following Code, I would expect no changed to be made to the database and the transaction to be rolled back. Why isn't it?
The table is simple, has two collums, first is an int, second a varchar.

<?php

header("Content-type: text/plain");
$test = new PDO("mysql:host=localhost;dbname=gmac", "root", "", array(PDO::ATTR_PERSISTENT => true));
$test->beginTransaction();

try{
	$test->query("TRUNCATE Area;");
	$id = rand(0, 100);
	$test->query("INSERT INTO Area VALUES ($id, 'Edinburgh');");
	throw new Exception("failed");
	$test->commit();
	echo "commited";

} catch (Exception $e){
	$test->rollBack();
	echo "rolled back\n" . $e->getMessage();
}

My first instinct is to call BUG, but I know there is probably something I'm overlooking. If I had another server set up I'd try another DBMS.

Ok, my last sentence triggered me to check the mySQL bug tracker and I found this : http://bugs.mysql.com/bug.php?id=8151

So, truncate table cannot be rolled back. How can I get around this otherwise? I imagine "DELETE FROM Area" would be pretty slow...

    Ok, here are some suggestions:

    1. Don't use persistent connections. REALLY. There are a zillion reasons not to, using transactions especially.

    2. Indeed, TRUNCATE cannot be used in a transaction. The reason for this is that TRUNCATE drops and recreates the table implicitly, this is not transaction-safe. That's fine, use DELETE FROM table instead.

    Of course DELETE FROM table; is slower, but it needs to be - as it has to keep all the deleted rows until commit.

    1. Set PDO_ERROR_MODE_EXCEPTION. Always. Otherwise, you may not know if a SQL query has failed.

    Happy PDOing

    Mark

      And (you probably know this since you're just testing the concept of exception handling here), but just for future reference for other readers), "try{throw new Exception}catch(Exception){}" is a very expensive way of simulating an if() statement.

        Thanks, I did know about the persistent connections, It was something I added into my example to try and get it to work. For some reason a bunch of examples I seen online used persistent connections, so i chucked that in but it didn't help of course.

        Thanks for the other tips.

        The worrying thing about the TRUNCATE is it seems to close the transaction then everything else is commited instantly. It'd be nice is mySQL gave me some kinda feedback about using TRUNCATE when a transaction is started.

          Write a Reply...