[Zope] MySQL - PostgreSQL - ZODB

Hannu Krosing hannu@tm.ee
Tue, 09 May 2000 00:01:14 +0300


Monty Taylor wrote:
> 
> MySQL doesn't implement Foreign Keys. It doesn't implement transactions.
> For better or for worse, those are the two things, and the only two
> things that really count that would cause you not to use it in
> production.

The main part concern is transactions - foreign keys can be worked
around
by program design (checking and rechecking), transactions can't.

> If these two things are very important to your personal
> sense of well-being, then I would emphaticly join the others in saying
> DON'T USE MYSQL. It's not the right choice for you.

Yes, don't use it for "valuable" data - use it for anything that comes
in 
abundance and small losses and inaccuraces are acceptable

> However, if you are indifferent to those, or don't know enough about SQL
> or RDBMSs to have an opion yet, please consider the following: (I'm
> going to contrast against Oracle, due to current personal involvement,
> but only as a convenient source of examples)

You can just say RDBMS instead of Oracle ;)

> 1) MySQL is intended for a different audience. In Oracle, you write code
> (stored procedures) that are stored in the database. The schemas are
> stored in the database. The development environment is in the database.
> It is intended as an almost sub-operating system. You can live your
> whole development life and never set foot outside the database. MySQL,
> on the other hand, is a place for people to put things and refer to them
> later in a relational manner. The intended users are programmers or
> other power-users. (Much like, other flame-war aside, VI and EMACS) The
> same people who like the extended power for the end user at the expense
> of ease of use, would like MySQL.



> 2) MySQL doens't do things for you. As mentioned earlier, it expects you
> to be a responsible programmer. It will let you do what you want.

It will do what you tell it to do, not what you want ;) If you want the 
an RDBMS to do what you want, you must first tell it the rules (foreign
keys,
checks, triggers)

> If you
> want to define a column with a logical foreign key to a look-up table,
> and then insert into that column an un-defined value, MySQL won't stop
> you. (It won't even know) Oracle will throw up an exception and say
> Foreign Key Constraint violated. People says this makes things safer,
> because it forces the data to be ok They also say that MySQL would be a
> pain because you have to write code to make sure you are inserting valid
> data each time. Well, you have to do the same in Oracle on the other end
> of the stick, because you have to write code to handle the exception the
> database will throw if you are careless. The difference here is clean
> code rather than a shotgun and a shovel to handle the exceptions.

There are often speed advantages to doing the try/except thing when 99% 
of the foreign keys are ok.

> Nothing is actually gained in data integrity by this, because you also
> have to define the constraints, (which would be part of the program
> design) and the constraint can just as easily be coded elsewhere.

You gain a lot if your database is accessed by mor than one concurrent 
users, specialli in absence of transactions:

       user  1                     user 2
check referenced key - OK
                               check if ok to delete key - OK
                               delete key
insert referencing key
                         
and you have an inconsistent state you had no way to detect
user1 could of course do another check after inserting the referencing
key and undo the insert by doing a delete, but then he would need to 
do all the check again and he could never stop being sure that he is OK,
unless he uses transactions which he can't do in MySQL


> 3) MySQL is smaller. MySQL is faster. Period. 

MySQL is faster for simple queries. Not so for more complex ones (i.e.
it
realli is a file system with an SQL interface)

> If speed or size are a
> determing factor, MySQL is for you. Because MySQL treats itself as a
> relational storage medium, rather than as a mom for your intentions
> towards your data, it can be optimized for speed and size in ways that
> aren't possible for Oracle. Our Oracle product directory contains 438M
> of stuff. That's not even data.

I've been told that oracle's install is now even bigger than the DB,
itself ;)

> 4) The biggest complait I've heard from the Zope World is that MySQL
> doesn't support transactions, and since Zope is highly transactional,
> there is an inherent incompatability.

There are other parts of Zope that are non-transactional (like
mailhosts)
but no other DAs.

> This is the one that irks me the most. Like the foreign key checking 
> from earlier, this just doens't buy you what you may think it buys you.

It depends on what you may think it buys you ;)

(see the comment on corresponding "why not MySQL" thread on Slashdot
about
DBAs jumping from skyscrapers and yelling ROLLBACK! befor hitting
ground)

> Zope's undo funciton is wonderful and quite useful. 

UNDO != transactions

Zope has much more than a DB-style transactions - it has also
long-running 
transactions (aka versions) as well as UNDO, which has nothing to do
with 
transactions at all.

> But the transactions in an RDBMS don't get you that.

They are not meant to. Their sole purpose is to ensure consistency 
for multi-user DBs.

> Transactions in the database world are most useful in stored
> procedures to prevent the effects of a program inserting its data
> half-way and then dying, leaving corrupt data.

More often it is for concurrent updates.

> As MySQL has no stored procedures, this is almost a non-issue, and 
> again something that program design can completely do without.

Only if you disallow concurrent access

> Since MySQL is small, fast, and I forgot to metion, stable, stuff 
> crapping out mid-transaction is almost never a problem as far as
> MySQL itself is concerned.

what does small and fast have to do with it ;)

> 5) I must add a fifth (sorry, I actually didn't mean for this to get so
> long) 
...
> Case in point for good program design. The program uploads the mugshot
> file into memory before inserting it (which should then go quite quickly
> if the program runs on a machine in the same LAN) Insert the mug-shot,
> then the demographics, then the user key info in the users table,
> causing the display of users still not to be bothered with the on going
> insert until the key record is there (This would not be possible in the
> world of Foreign Keys, mind you, where you would need the user row there
> before you could insert the slower data -- the 'deficiencies' work quite
> nicely together, no?)

No! Foreign keys can be enforced at transaction commit time, when all 
the needed inserts/updates are done.

> I guess the main thing I'm trying to say is, it isn't better or worse,
> necessarily. 

> Nor is it necessarily less stable. 

No. It just has intermittent inconsistent states, which can result in 
wrong dtata in concurrent use scenarios.

> Either way can cause vast amount of crying and gnashing of teeth if
> you go into it with the wrong mindset.

But if someone loses money _with_ transactions it can be surely traced 
to programmer error, when using MySQL you can point fingers at MySQL 
and prove that this was unavoidable ;) 

...

> But I'm still not going to claim that it isn't stable or that you
> shouldn't run production data on it,

If you care for your data (or their representation at all times) and 
have enough updates you can't ensure they are done serially, you should 
not.

With low update volumes you may be able to arrange your updates so that 
you can.

> because then I'd be joining in the attitudes of all the MS people 
> who think that running an open-source scripting-language based
> system is silly. 

Being able to imply some suprficial ties of your thinking to the 
supposed contrary of what MS people are supposed to think is a very 
weak argument in technical fields. Grow up!

--------------
Hannu