it's not that simple [LWN.net]

it's not that simple

Posted Apr 3, 2008 22:40 UTC (Thu) by epa (subscriber, #39769)
In reply to: it's not that simple by tialaramex
Parent article: Sun Microsystems' Next Linux Move (Seeking Alpha)

Yes, the hard part is not writing an SQL database system, it´s making one bug-compatible with
MySQL.  But why would Sun want that?  Which customers are particularly dependent on MySQL and
no other database?

The software that needs MySQL is usually low-rent stuff, online bulletin boards like
Slashcode.  It´s unlikely any banking applications, for example, are using it.  Yet these are
the high-paying customers.

(Log in to post comments)

it's not that simple

Posted Apr 3, 2008 23:46 UTC (Thu) by chromatic (subscriber, #26207) [Link]

A lot of people see MySQL as an excellent example of the Innovator's Dilemma.  Ignore the
low-rent customers at your own risk.

it's not that simple

Posted Apr 4, 2008 3:25 UTC (Fri) by drag (subscriber, #31333) [Link]

I don't know why people like to shit all over Mysql. 

It does it's job well and provides the levels of features that it's users desire. It's a
backing store for most all open source online applications. That's all. That's all databases
are for most people, just backing store. 

Then on top of that they have all sorts of different back-ends that provide any sort of
features that you want if you need them while not requiring users to change very much in their
apps to take advantage of it. This gives it a level of flexibility that I don't think your
going to find in any other SQL-ish database.

It _works_. And it's not just used by small stuff either. I am pretty sure that people have
successfully used Mysql in larger and more important sites then anybody has ever done with
postgresql. 

I mean it's not like postgresql is the paragon of correctness, stability, or anything else
that is important in databases. 

I am sure that it's fine, wonderful, and great, but why all the hate? Is everybody that likes
postgre feels like sand has been kicked in their face by the big bad commercial Mysql bully or
something?

it's not that simple

Posted Apr 4, 2008 3:27 UTC (Fri) by drag (subscriber, #31333) [Link]

No offense, but I am just amazed that a any sort of mention of Mysql in any sort of way in any
sort of article there are always a certain number of people that just love to talk about how
much it sucks compared to everything else.

it's not that simple

Posted Apr 4, 2008 8:34 UTC (Fri) by nix (subscriber, #2304) [Link]

Oh yes. SQLITE IS MORE FEATUREFUL RAH RAH RAH

(er. perhaps not. God knows mysql is more *usable* than bloody Oracle.)

it's not that simple

Posted Apr 7, 2008 8:47 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

Here, here. Anyone who claims MySQL is a toy should take a long hard look at Oracle.

* You cant use column aliases in the having clause, e.g. "select foo as bar ... having bar>0"
is a syntax error.

* Null is the same as an empty string.

* 30 character maximum on table and column names.

* Varchars can't be longer than 4000 characters, for anything longer, you have to use clobs,
which are glacially slow.

* The Oracle character set named utf-8 is not actually utf-8. The character set name al32utf-8
is actually utf-8.

* Passwords are case insensitive.

* Oracle uses two different but very similar languages, SQL to perform simple queries and
PL/SQL to define functions, procedures, etc. These languages have different definitions of the
basic types, e.g. the maximum length of a varchar variable is different in the two, one has a
boolean type, the other does not.

* The oracle installation is loads of fun. At one point in the installation, a popup window
appears prompting you to something like "open a new terminal as root and run the following
commands: ...". You also manually have to edit about half a dozen system files.

I don't know how a program can have those kinds of design issues and still be considered even
remotely enterprise ready.

Sure, MySQL has various limits on how many triggers you can define, but that can be worked
around pretty easily. By default, it is far to forgiving about bad data, but that is a
configuration issue. It is surprisingly easy to corrupt the database on crashes, etc.. but
that is true for Oracle as well and in both cases it is almost always the case that the logs
can be used to restore the database. MySQL is glacially slow on subselects, but there are
loads of other types of queries where Oracle is dog slow and if you write your SQL with MySQL
in mind, your queries will usually fly.

Remind me why Oracle is better than MySQL?

it's not that simple

Posted Apr 7, 2008 14:52 UTC (Mon) by nix (subscriber, #2304) [Link]

* Null is the same as an empty string.

Now, now, don't go casting false aspersions. They changed this, silently: NULL is now the same as an empty string *in WHERE and HAVING clauses only* except that if the optimizer chooses to do particular things to your query or your database has particular patches, it isn't.

Isn't that clearer?

Remember also its lovely flow analysis in the vile abomination that is called Pro*C: pass an uninitialized char array into the *OUT parameter* of a PL/SQL function, or into a SQL statement's INTO clause, and it does no flow analysis at all and strlen()s the uninitialized string prior to sending it to the server. Great design, guys.

(I'm not even getting into Pro*C's other manifold faults because this post would be 300K long.)

* Varchars can't be longer than 4000 characters, for anything longer, you have to use clobs, which are glacially slow.

And PL/SQL can only manipulate clobs by cutting them into pieces (whose maximum length is the same as the maximum length of a VARCHAR, which is not 4000 characters in all situations but rather depends on your *database character encoding*) and manipulating them that way!

* Oracle uses two different but very similar languages, SQL to perform simple queries and PL/SQL to define functions, procedures, etc. These languages have different definitions of the basic types, e.g. the maximum length of a varchar variable is different in the two, one has a boolean type, the other does not.

It's worse than that. There are separate implementations of PL/SQL in each of Oracle's products. These are different languages with distinct bugs, different 'specifications' (most nowhere documented that I can find) and sometimes-diverging development histories. There is no way to find out which language/VM you are running against at compile- or runtime. Many of the bugs are catastrophic: e.g. Oracle Forms 9's PL/SQL implementation allegedly supports the CLOB and TIMESTAMP types, but if you use it the Forms frontend sprays incomprehensible errors at you (in the latter case) or instantly terminates (in the former).

Remind me why Oracle is better than MySQL?

I'm a PostgreSQL man (just never used MySQL much and heard enough about it to stick with what I like). It has faults (the lack of full support for table inheritance for one) but like it or not at least they try for consistency, which is something that's wholly lacking from the big database vendors.

it's not that simple

Posted Apr 7, 2008 17:23 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

Wow. I knew about the clob limitations, but the rest... Had no clue. I think it's awesome that
Oracle is the 9:th largest contributor to Linux. I'm sure they'll make Linux enterprise ready
in no time.

it's not that simple

Posted Apr 7, 2008 18:14 UTC (Mon) by nix (subscriber, #2304) [Link]

i.e. bugger it up completely? Yep, that's possible, but I hope not!

I've been doing Oracle stuff for ten years now and much of it consists of 
spending great effort to find ways to *avoid* using Oracle, because using 
it is so unpleasant. Using Unix stuff instead is generally vastly more 
enjoyable.

I suspect a lot of the ugliness in the Oracle stuff is because their 
competition is largely other hugely expensive databases like SQL Server, 
which are *also* incredibly wart-filled, because they're all based on 
really really old code and have been maintained for decades by people who 
care more about getting the next version out and getting a new marketing 
buzzword implemented than they do about internal consistency.

(e.g. Oracle's 'object oriented' features introduced in Oracle 8. There 
are *still* bugs in there whereby unprivileged users can crash entire 
database instances. They *still* don't implement inheritance as far as I 
can tell. It's *still* nearly useless for any practical purpose... because 
it was never intended to be *used*: it was just a marketing buzzword, 
when 'object oriented' was the latest big thing.)

it's not that simple

Posted Apr 4, 2008 13:38 UTC (Fri) by i3839 (subscriber, #31386) [Link]

All I'll say is that for teaching SQL to students, MySQL is rather unfortunate in its limited
support for standard SQL features. That it requires workarounds to let it accept otherwise
fine SQL code only make it more annoying. That MySQL isn't alwasy smart enough to optimise
more complex queries and thus forces you to write ugly queries instead, isn't very nice
either. Let's say that from the people who have experience with MySQL and other databases I
also don't hear great things about it either, to put it mildly, so it's not only my own
unfortunate experience. But I'm sure the newest version is better.

it's not that simple

Posted Apr 7, 2008 13:06 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

All databases have their warts. Postgres arguably has fewer warts than MySQL, but it's not
perfect either. For one thing, table creation and simple insert statements are often orders of
magnitude slower than in MySQL, in my experience. This is using the latest Postgres version in
Ubuntu dapper. Also, having to vacuum the tables to reclaim storage is a bit silly.

it's not that simple

Posted Apr 7, 2008 15:00 UTC (Mon) by nix (subscriber, #2304) [Link]

PostgreSQL table creation is a bit slower because it's transactioned :))) 
personally I'm willing to accept a bit of sloth to gain the benefits of 
being able to roll back on screwups. With transactioning, you can easily 
have any random thing create tables and vape them automatically on 
ROLLBACK: something which should have been the case in all relational DBs 
from the very start, I think.

Vacuuming has been automatic for about a year now.

it's not that simple

Posted Apr 7, 2008 17:39 UTC (Mon) by liljencrantz (subscriber, #28458) [Link]

I don't know about table creation, but MySQL inserts a lot faster even when using transactions
and InnoDB, at least in my experience. And vacuuming is still silly, even if it's done
automatically. Last time I checked, it has a noticable performance impact on any queries
running while vacuuming, meaning Postgres is not very good at handling continously high loads.

I like Postgres just fine, don't mean to slam it at all, every database product has some
warts. MySQL probably has bigger warts, but both are in my experience pretty nice systems to
deal with, unlike say... Oracle.

it's not that simple

Posted Apr 7, 2008 18:16 UTC (Mon) by nix (subscriber, #2304) [Link]

Yeah. Actually as far as I can tell you have two choices if you want 
transaction consistency: use multiversion concurrency control, or use 
locks. Locks have a *much* worse performance hit in the concurrent case, 
and annoy developers more, but a lower hit in the single-threaded case. 
It's a toss-up which to use :)

(Oracle moved to MVCC a while back, too, while keeping around a huge pile 
of grot to try to mollify people who were used to locking everything and 
messing around in horrible ways with 'rollback segments' and that crud.)

it's not that simple

Posted Apr 4, 2008 5:49 UTC (Fri) by AJWM (subscriber, #15888) [Link]

> I mean it's not like postgresql is the paragon of correctness, stability, or anything else
that is important in databases. 

Some years back when I was choosing an RDBMS for a project I went with PostgreSQL for a couple
of reasons.  At the time it was certainly more correct, stable and so on than MySQL -- that
may well have changed as MySQL has improved.

The other important criterion was that it supported Embedded SQL in a manner almost identical
to Oracle (one or two trivial #ifdef differences, much closer than any other Embedded SQL I'd
seen).   I don't think MySQL does yet, does it?

But sure, if all you're using the DB for is a backing store, there are plenty of options.

Actually I think if PostgreSQL had had a name as pronounceable as MySQL, it would have become
the clear front runner a long time ago ;-)