Category: Toys, Tools, Tech
Tuesday, September 5, 2006
Here's a question for any of you out there who use the ADOdb database abstraction layer for PHP.
I have a block of code that looks like this:
$sql1 = "MySQL INSERT statement";
$sql2 = "MySQL UPDATE statement";
$dbconn -> StartTrans();
$dbconn -> Execute($sql1);
$dbconn -> Execute($sql2);
$dbconn -> CompleteTrans();
Now, here's the thing. There's a typo in the SQL statement I put into the $sql2 variable. It will never process successfully.
Given that, nothing should be committed to the database. Correct? Because ADOdb's "smart transactions" means that it will catch the error being returned from the second Execute and automatically roll the transaction back.
Right?
That's certainly how the docs read to me. Thing is, that's not how ADOdb is behaving. Instead of rolling back the whole transaction, it simply fails the second Execute() call, and commits the first -- which is pretty much the exact opposite of what anything calling itself "transactions" should do, since it leaves loose data hanging around.
Am I missing something regarding ADOdb's transaction handling? Should I be using an alternate syntax, throwing in some manual rollback triggering of my own, or what? Frankly if I can't rely on ADOdb to handle transactions reliably my impulse is just to throw the whole library out -- but this seems like a basic enough use-case that I can't imagine nobody's run into it before, if it truly is a bug and not just me screwing up somewhere.
PHP gurus, whaddaya think?
UPDATE (Oct. 20, 2006): Problem solved!
Except where otherwise noted, all content on this site is provided under the terms of the Creative Commons Attribution-ShareAlike license.
If you think anything I write here represents the opinions of anybody but myself, you need more help than I can give you. The opinions are all mine, folks. Nobody else's.
If that's too hard to understand... well, I'm sorry. There's only so much I can do. I'm not a therapist, and I'm not a miracle worker. I wish I could help you work through your delusional belief that I'm speaking for anyone else but myself. Honestly, I do. But in the end, that's a monkey you'll have to get off your back on your own. Sorry.
Web feed
Are you using tables that support transactions, i.e., innoDB?
If you don't need that kind of support (row-level locking, true transactional support), then I'd ditch ADODB, unless you really are going to be swapping out DB engines underneath on a regular basis. ADODB seems to be the best of the abstraction libraries out there (at least for PHP4), but it is still roughly twice as expensive as plain mysql_* functions, in our experience.
I am using InnoDB -- bitter experiences that shall not be recounted here have convinced me that using MyISAM is just setting yourself up for pain down the road...
Here's a question, then: any of you used the new mysqli_ and PDO libraries in PHP5? Any reason to use them over the standard old mysql_ libraries?
I haven't used mysqli or PDO yet. Doesn't adodb have a driver for mysqli and PDO. You could give that a try.
You could always swallow all pride and dignity and do this:
<cftransaction>
...
</cftransaction>
:-)
Hi there ... I have run into the same problem here. Using the standard mysql extension in PHP and I am using InnoDB for the table that needs to be rolled back. But nothing happens. No rollback.
Have you found a solution to your problem yet?
By the way ... when I run a simple test using transactions on the MySQL commandline ... rollback works as expected ... so MySQL should not be the problem.