[lug] MySQL work queue problem

[George Sexton]

Here's my anti-MySQL rant. As qualifications, I've been writing database 
apps since 1989. I have a software product that runs on

Oracle 8 & 9
Sybase SQL Anywhere
MS SQL Server
MySQL 5.0
PostgreSQL
IBM DB2
MS Access

of all of these, I hate MySQL the most. It ranks below MS Access in my 
opinion.

SET SOAPBOX ON

It's really a case of a mediocre product with superior marketing and 
mind-share triumphing over technically better products. Our product 
added support for MySQL last year when they finally added support for 
views. It's been nothing but a pain in the rear since.

Fox Software FoxPro had views in 1992. Everyone else has ALWAYS had 
them. Why did it take so long? Few hosting companys are running 5.0 
which has view support.

Just plain bad software quality. If you upgrade MySQL, and don't run the 
correct upgrade scripts, rather than refusing to run and issuing a 
useful error message, things just fail in strange and bizarre ways.

Their database engine contains a bunch of really stupid limitations. You 
can't have a date field, and set the default to the current date. 
Defaults can't contain functions, even if those functions are noarg 
functions. In order to create a trigger, and run it you have to give the 
user GLOBAL SUPER privileges. This is just retarded. Who thought of this?

Case sensitivity of tables names is non-standard, and just plain 
moronic. When they did the first port to Windows, they should have 
looked at this and said "Oh, crap, ... case sensitive table names was a 
bad idea and they're non-standard. Let's not do it any more...".  No, 
now you have case sensitivity under UNIX, and no-case sensitivity under 
Windows.... Brilliant. Totally non-standard, but Brilliant.

Replaceable engines. People will say. Oh, this is good. It allows the 
developer to choose the best options for their system. Well, if you're 
writing software for the hosted/embedded market, what it really means 
is: "Someone who doesn't understand database applications and has only 
the vaguest and least half-formed opinion will choose the database 
engine, and you have to live with it....". Yeah, anyone can write a fast 
db engine if you lock the whole table like MyISAM does. Ditto if you 
have a crappy query engine that can't do things like correlated 
sub-queries. You have people like the person who started this message 
thread running a multi-threaded application, totally sold on a DB engine 
that locks tables on updates. It's just a dead-lock waiting to happen.

Silly behaviors are another thing. Define a column NOT NULL. Insert 
null. What happens? It silently converts the null to an empty value and 
puts it in anyway. At least 5.0 finally added a switch to turn this off. 
Of course no hosting company will ever run with this switch because it 
will break too many poorly written MySQL apps.

SET SOAPBOX OFF

OK, you now know why I think MySQL is a piece of dung from an anemic and 
under-fed rat. Here's what's better.

PostgreSQL   -   Compliant SQL Engine, Free BSD License, Good Performance
MS SQL Server - Used by an expert, astonishingly good performance. 
Amazingly good developer tools. Absolutely first rate paid support. 
Ignore support contracts. Just go per-incident support for $300.
Sybase SQL Anywhere - Low cost, and terrific replication capabilities.
IBM DB2 - Rock Solid
Oracle - Industry Standard, but quirky in it's own right.


The truth is that the database is RARELY the limiting factor in 
applications, particularly web applications. Choosing a SQL engine 
because it's "the fastest" ignores the fact that it's the most error 
prone when your application is deployed. Why? Because your app went 
through a development cycle with no safety, and a lot of screwy corner 
cases that should have been rejected by the DB were not caught. CPUs are 
cheap. Even if DB performance becomes a bottle neck, it's a no-brainer 
to stick a load balancer in front of a lot  of boxes and get your 
application out the door faster than to spend an additional month in 
development chasing down corner cases the DB should have rejected.

Now of course, the smarter people will say "You have to test 
adequately... It's your own fault." Yeah, right. Like any customer ever 
is OK with a large testing budget.

The only reason to use MySQL is because it's ubiquitous. Of course, if 
you walk out in the green belt by my house where people walk their dogs, 
you can find other things that are ubiquitous as well.

Zan Lynx wrote:
> That's old information now.  MySQL's current versions support a variety
> of table backends, some of which implement full ACID compliance.  You
> can still choose fast and loose if you want it though.
>
> http://dev.mysql.com/doc/refman/5.1/en/storage-engine-choosing.html
>   

-- 
George Sexton
MH Software, Inc.
Voice: +1 303 438 9585
URL:   http://www.mhsoftware.com/