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