April 30, 2003

Damn lying software

So, at work we're using MySQL as our main database for the obvious reasons--it's free and it's good enough. Personally I prefer Oracle, but that's just old habits and being used to massive operations where Oracle was the rightest answer available. (I'll withhold judgment on whether Oracle's ever really the right answer, but you take what you can)

Anyway, the new app design wants transactions. No problem, right? MySQL supports them with InnoDB tables. Fine. All the table create SQL has a TYPE=InnoDB, to make sure they're InnoDB tables. Run the app and...

Crap in tables. Rollbacks aren't working. This is odd. Futz around, and find all the tables are of type MyISAM, which doesn't support transactions. So, of course, we do an ALTER TABLE foo TYPE=InnoDB. Accepted, table noted as converted.

Except it isn't.

Turns out that this build of MySQL doesn't have InnoDB support built in. Which is fine (well, no, it isn't, but it's not the issue here, and we can work around that) but the damn table create and alter never complained! The alter even succeeded. It just didn't do anything. Or, rather, it did a lot of work to ultimately do nothing.

Yes, I know, I can build my own, and probably will if I can't get stock RPMs. (Though I really would prefer stock RPMs, for reasons I don't want to go into) What just pisses me off is that, not only did the software not tell me that it couldn't do what I asked, but it acted like it did. And I burned a whole afternoon.

Makes me wonder how many folks have DB installs that they think have transactions enabled but really don't.

Update: I now have a working MySQL with working InnoDB support built in, installed. Which is good. It will still quietly lie if the inno settings aren't set up, which is still bad.

Posted by Dan at April 30, 2003 05:24 PM | TrackBack (0)

that was the biggest problem i've had with mysql, the fact that it tends to just silently fail to do what you tell it, without any indication of a problem (at least not until your referential integrity constraints fail to be consistent, or your transactions don't work).

Posted by: garrett at April 30, 2003 07:00 PM

Annoying isn't it :-) If I write anything that has to use MySQL I always add a test script that creates a table and checks that transactions work. Doing this has saved me lots of debugging time.

Posted by: adrianh at May 1, 2003 03:38 AM

Most open source software, like MySQL, is very configurable at the source level. If your using OSS in production, why *wouldn't* you compile your own build? Some tools (make, perl, gcc) are stunningly useful in a stock configuration, or have a strong core featureset/behavior in any configuration.

FWIW, MySQL 3.23.x is in the class of software I'd prefer to compile myself. I'm pretty sure MySQL 4.x has InnoDB as a standard feature, so I'd be less wary of using an RPM.

Posted by: ziggy at May 1, 2003 11:27 AM

The version of 4.x I have on my OS X box here does have InnoDB built in, but I'm not sure if that's the default or just what it came with, since I snagged someone else's build. And, unfortunately, I need to make sure my stuff works with stock vendor releases or updates, since we can't always count on people's ISPs being willing to rebuild from source. Having a RedHat RPM handy makes things much, much easier. It's also easier to say "For RedHat, make sure you have RPMs X, Y, and Z installed" (And there are updates from Redhat that do have InnoDB, so I'm fine now) Yeah, Open Source means I can mess with things as I'm sufficiently geeky, but this is for an environment where people just don't want to, shouldn't have to, and I'd prefer they didn't.

Still, there is the bigger issue--why the heck didn't MySQL complain? It should have, both when I tried on a build with no InnoDB support, and tried on a build with InnoDB support but without InnoDB enabled. A successful table converted message when it didn't is actively deceptive.

Posted by: Dan at May 1, 2003 11:43 AM