it's not that simple [LWN.net]

it's not that simple

Posted Apr 4, 2008 3:27 UTC (Fri) by drag (subscriber, #31333)
In reply to: it's not that simple by drag
Parent article: Sun Microsystems' Next Linux Move (Seeking Alpha)

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.

(Log in to post comments)

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.)