ADOdb Transaction Mystery: Solved

Posted on October 20, 2006

You may remember my question a month and a half ago about why the ADOdb database abstraction layer for PHP wasn’t handling transactions correctly for me.

Well, nobody had a good answer as to why that would be, and I couldn’t figure it out, so I ended up ripping out ADOdb completely and going back to PHP’s mysqli library. It was a pain, but it just didn’t seem worth it to spend time debugging ADOdb.

It appears, though, that the mystery has been solved. Yesterday I got an e-mail from Intrepid Reader Robert-Jan de Vries of ThirdWave WebDesign in Leiden, the Netherlands. Robert-Jan explained that

AdoDB features separate drivers for each of PHP’s mysql extensions (both mysql and mysqli for instance) and that is where the solution to the problem lies. Using the standard driver (mysql) disables support for transactions. Switching to the mysqlt driver (I guess the "t" stands for "transactions") enables this. Rollback works like a charm now.

Kinda stupid though, that it is not mentioned anywhere in the documentation when the transaction functions are discussed…

Sure enough, if you look in ADOdb’s "Supported Databases" list in its documentation, you see that Robert-Jan is correct:

The name below is the value you pass to NewADOConnection($name) to create a connection object for that database…

mysql                    mysql without transaction support.
mysqlt or maxsql   mysql with transaction support.

In their "Connection Examples", though, where they provide sample code for connecting to MySQL and other databases, there’s no mention of the need to use "mysqlt" in order to get transaction support. The docs use the "mysql" driver exclusively. Hence the confusion.

It’s too late for me to make use of this info, since I’ve already expunged ADOdb from my project. I thought I’d share the solution, though, in case someone else might find it handy down the road. (Robert-Jan has also submitted it as a documentation bug to the ADOdb team, so hopefully this will be made clearer in future editions of the docs.)

All of which goes to prove once again that Just Well Mixed readers are the smartest people in the world! Well, smarter than the author, anyway 😉