ADOdb Transaction Mystery: Solved

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 😉


Comments

Oscar

October 20, 2006
10:40 am

Good for you – you should see at least a 2x speed increase in your mysql connections / queries

Oscar

October 20, 2006
10:41 am

Good for you – you should see at least a 2x speed increase in your mysql connections / queries

Oscar

October 20, 2006
10:42 am

sorry bout the double post – I got a 500 internal server error page on the first try.

Sebastian Fahrenkrog

June 26, 2008
6:13 am

Thanks a thousand time! That’s the information I was looking for.
Greetings
Sebastian

Bruno Canongia

July 29, 2008
10:32 am

Solved my problem… thanks from Brazil!

Weirong Zhang

November 19, 2008
6:16 pm

Thanks from Canada.

Mrigesh Shresth

February 27, 2009
12:18 am

Thanks bro for the info. It worked for me…

joeph

June 29, 2010
2:09 am

Thanks, I was going mad…

remi

October 11, 2010
11:39 pm

thanks bro, that works for me too. God bless you.

indra

March 27, 2012
10:03 pm

thanks, it helped

ROY

April 26, 2012
4:17 am

Thx from PL,

additional to mention you control transactions with:

$VARDB->StartTrans();
$VARDB->CompleteTrans();

Tried with ->Execute(‘BEGIN;’) .. >Execute(‘COMMITl’);
and brought my MySQL server down.

Geo Sukarno

May 1, 2012
2:07 am

Thank you, Jason Lefkowitz.
This article very helped me.

caiyunlong

January 12, 2013
3:40 am

Thank You!!This problem waste me a lots of time.!!!!

mc418a

December 27, 2013
4:34 am

you save my day, or maybe week
greetings from PL

Vitor

April 9, 2014
5:49 pm

Thanks from Minas Gerais – Brazil.

You Save me