Slashdot Mirror
'Most Important Ever' MySQL Reaches Beta
An anonymous reader writes "The open source database company says it is 'fixing 10 years of critcism in one release', and is aiming at boosting enterprise take-up." Stored procedures. Triggers. Views. It's like it'll be a real DB!
How about linking to an article or page that actually has some useful informtion about what is going to be in the release that makes it "the most important ever"?
The open source database company says it is 'fixing 10 years of critcism in one release'
If they can fix 10 years of criticiscm in one release, why couldn't do that before? Or maybe in several fixes rolled out within the 10 years?
In need of reliable and affordable server monitoring?
A more impressive feat would be to get ISPs who do lots of low-end hosting to actually update from the 3.23.x series for starters... which would probably mean Redhat, Debian, etc. need to ship it. So those users will be seeing this version in... 2008 maybe. 2012. Right after Longhorn comes out.
Let's see....throwing everything and the kicthen sink into one release can't possibly affect stability, right?
I love MySQL, and use it, as well as PostgreSQL and Oracle, depending on the project. However, if stability or data integrity becomes an issue because of all these feature additions allowing them to play with the big boys, I'll drop MySQL in a heartbeat.
If your database isn't reliable, it nothing else really matters.
And then suddenly, MySQL isn't quite so fast. It used to be, if you need a speedy db and don't need all the fancy features (like integrity) you choose MySQL. If you want to sacrifice a little speed but need features, you got PostgreSQL. Products should stick to what they're good at.
You can either complain, or do nothing. You don't get both.
And it will take 10 more years of use to find and address all the bugs in this huge, overdue upgrade. One reason the persistent incremental changes in open-source is better for users, is that we get started on finding and fixing bugs earlier, without a dizzying array of them from which to choose.
--
make install -not war
I don't know about you, but the thought of someone adding that many core features at one time scares me. They should have renamed it and called it version 1.0, because thats what it really is....
Plus, are they following the ANSI standards for the features that have them? If so, they are going to break compatiability with prior versions.
I would wait until atleast version 5.1 before even thinking about using it.
BWP
Honestly, why would anyone use mysql when it ignores foreign key constraints and corrupts data by inserting arbitrary values? What advantage does it have over postgres, which is also free and actually supports data integrity?
if I read "well enough for most purposes" by a mysql fanboy one more time I will have to start drinking before noon.
popularity isn't proof of clue, guys. How many people run windows, right?
with postgresql and firebird there have long been available real open-source databases that are just as easy to get up and running as MySQL, but won't hamstring you when you start to learn more.
I'm glad to see MySQL joining the club, but it must be shocking for the "we don't need no steenkeeng logic in the database" fanboys to adjust... Parent is case in point, I guess.
Normally I put in beta software on my box (never in any production units, mind you... just my own personal box) just to "kick the tires" and see whether a new version of an app or some new piece of technology is going to get the job done.
But not databases. I won't even mess with alpha, beta or even release candidates of ANY database software until it is RTMed or "gold". It's gotta work and work right, or there's no point messing with it. I don't want any suprises with database system issues when working on any projects... not even in the earliest development stages.
I'd just as soon see that MySQL take thier time and get 5.x released when it is ready. And when it is released, I hope it works RIGHT.
The problem with socialism is that they always run out of other people's money. - Margaret Thatcher
I have done enterprise apps and 2 major websites using MySQL.
The one thing that MySQL excels in is raw speed. It's faster than everything else because it doesn't have all the data integrity features that a RDBMS does.
However, I stick everything into the application layer, so MySQL lacking these features doesn't bother me a bit.
As for data integrity; I haven't done a banking application yet, so I'm unconcerned.
Scheduled DB backups and logging in the application layer keeps me from needing any transaction or rollback features.
Basically, I'm hoping that the new MySQL won't sacrifice speed for all these features.
Copied from my blog^Wweb based journal.
Once you start getting into "serious" work, or "enterprise" level computing, which is all anyone argues about, every single assumption gets tossed out the window.
Thought your OS was stable? Yeah, it's pretty stable, but when it gets hit all day every day at 100%, it crashes for some reason every few months. I'd love to say that Linux is the exception here, but well, it isn't.
Maybe you bought the highest quality disks? And avoided the "bad" vendor? Wrong! This year the bad vendor is the one you bought plenty of! Looks like your recovery plan didn't consider that 25% of your disks would fail in the first year!
Thought you had enough RAM? You don't! And you can't add more because you're on a 32-bit platform. Sucker! Start migrating to 64-bit and learn a whole new bunch of gotchas the hard way.
Hey! This RAID adapter has an awfully funny glitch! When you pop a brand new disk in, if you reboot, it treats it as a whole new array, and the funniest part is that it renumbers all of the other arrays! Kernel panic: can't mount root device! What a laugh! Good thing we have RAID here to give us added reliability!
Thought you'd never max out that fridge computer? Well, you just did. It looks like your developers decide to get sloppy when they think they have infinite capacity. A couple of weeks of performance analysis and retuning the algorithms instead of doing real new work!
Thought that replication setup would scale infinitely? Well, infinitely actually means 10,000 queries/sec. Yup, that's the ceiling. No choice now but to re-architect the whole system into a decentralized dataset. Hey, since it's all so decentralized, lets just store CSV files! Added bonus: management types love it!
Six months of re-engineering to decentralize the whole system, and another six months to phase it in. And it sure will require downtime!
For all of the talk of mission critical feature this and enterprise functionality that, in the end, these "real work" loads are handled by the resourcefulness of your people, because no platform is going to even come close to solving all of your problems.
Package X vs. package Y does not make a difference in the big picture. If only. MySQL, PostgreSQL, Oracle, BerkeleyDB, or peach fuzz? The answer is obvious: pick the one your team is most capable and most comfortable with. Got it? Great! You've just solved the easiest problem you're ever going to have.
Bah! Humbug.
I hope to heaven I never have to maintain anything you write.
Ever heard of the "Don't repeat yourself" principle? Putting logic in the application that belongs in the database means that if just one place that touches the data forgets to do what your trigger should have done, you're screwed.
Maybe your applications are small enough that this is an acceptable risk for you. The ones I have worked on haven't been.
>MySQL has had replication for a long time already.
Yeah, but how reliable (read: it sucks) is it?
From TFA:
"I believe it will change how MySQL is perceived in the market," said Axmark, who then added that he thought this release would make MySQL an option for at least ten times as many users as before."
It says in the article that mySQL has a 40% share in the open source DB market. If they're gonna get 10x as many users, that'd be 400%, so I guess they plan to make further gains in the commercial market.
If open source DBs have a 10% share (means shit, anyway), then mySQL plans to shoot from a 4% to a 40% market share. I just don't see that happening.
About the market share: what does that mean - 40%? Of what? By number of copies in use, by data, by number of paying customers...?
In the ensuing 10 years, they've thoroughly corrupted the minds of young programmers and DBAs by making them think it's okay to sacrifice data integrity for the illusion of speed ("illusion", because mysql chokes when you get into complex data sets or queries; the vaunted speed of mysql only applies when you're working with a data set so simple you could represent it with flat files or xml without any difficulty). That it's okay to work around the shortcomings of a RDBMS in your application code (no, I am not going to implement transactions, referential integrity, or subqueries in my application code. That's just stupid). That you don't need views or sub-selects or triggers or stored procedures. That adding those features actually slows down your RDBMS (well, yeah, if you implement them poorly).
While it's nice to see that they'll finally support most of the features of a proper RDBMS, it's too little too late. Even if they ship tomorrow it'll be years before this new version is ubiquitous (how many ISPs and hosting providers are still running an ancient 3.x version of MySQL?). The best way for them to have "fixed" those 10 years of criticism was never to have allowed the criticism in the first place -- by fully implementing an RDBMS, or at least acknowledging the benefits of the features you don't implement, like foreign keys, rather than spouting out crap about how adding those features will slow things down, and any "smart" programmer can do without them anyway. At least that way they could've avoided looking like hypocrites.
"You don't need transactions. Transactions just slow things down. Look, we have table-level locks. Use those. You can ensure data integrity from your application by using table-level locks. Performance concerns on locking a full table to update a single row? What?" and then, "Would you look at that? Transactions! You can get them if you use this new table type. Of course, if you don't have that new table type and you try to use it, or you do have the new table type but you don't explicitly mark your tables as that new type, you're not going to get transactions. Oh, we won't fail, we'll just silently not open a transaction, and silently not rollback or commit when you ask us to."
"Sub-selects? Sub-selects are slow. Why would you need sub-selects when you can do two queries, pull all of that data back to your application (because you don't need stored procedures either), and mimic the sub-select there?" and then, "Oo! Sub-selects! Pretty!"
etc ...
Yeah... a lot of folks use screwdrivers as chisels, wrenches as hammers, knives as screwdrivers, and pliars for wrenches, too. It doesn't make a screwdriver a chisel, a wrench a hammer, a knife a screwdriver, or pliars a wrench.
Umm ok lets say there are 1 million database "shares" whatever that means.
100,000 or those are open source.
40,000 are MYSQL.
so they are shooting for 10X meaning 400,000, this is obviously impossible for them to obtain that many users. But do note he said it makes MYSQL an "option". This means that 40% of database users require the features that MYSQL now has and nothing more. I can believe that.
Sure it's not ACID, but it does well enough for most purposes
Also works well enough for "most purposes": Flat files, MS Access, etc. That doesn't mean I'm going to build any kind of important app around them.
I don't respond to AC's.
Let me help you down off your high horse. Plenty of 'Database Professionals' use MySQL. Not every project needs triggers and views. A real professional uses the tool best suited for the job and doesn't try to over-charge his clients for things they don't need.
- None can love freedom heartily, but good men; the rest love not freedom, but license. -- John Milton
Agreed. I don't think other database vendors understand the importance of speed to a Web Developer. We have to go over a network layer, with the requisite delays factored in. We use a client-server model, and cannot rely on the client CPU, like a traditional software product. And even a crappy Web site can find itself loaded down with a large audience. So we have to squeeze out milliseconds anywhere we can get them. A fast database and optimized code, that's pretty much what we can control.
I mentioned this story once before on Slashdot, but it's relevant, so here it is. Borland had a product called Intrabuilder. It had a poor-man's version of Live Script on the backend, with a built-in database -- so back in 1997 you could do some very PHP-like stuff with the system. It was promising. But as a Web guy there, I was tasked with using it on the borland.com site. And it was giving me huge lag -- 1 or 2 seconds per simultaneous user. So with 5 people testing my app, each page took 10 seconds to display. I told this to the Intrabuilder team. Response? "That is an acceptable delay. It's how databases work." For all I knew, they were right. Maybe databases did work slowly like that back then. I was young & new to that stuff. But I knew that I didn't work that way and I didn't want my site to work that way either. Borland eventually abandoned the product, because the developers didn't see the shift in the market: Web Developers need speed. It's not like an ATM transaction, where I'll wait 15 seconds to get my money. MySQL needs to keep its speed, especially under load. And other database teams would be wise to take note.
My Greasemonkey scripts for Digg &
The same way that MySQL has the constraints feature on columns. But it also isn't implemented correctly. If you insert out-of-bounds data, MySQL silently changes it to a number that it likes. Features implemented half-assed like that aren't really good features.
If they implement views, triggers, and stored procedures in the same crappy way, MySQL is still going to be a crappy DBMS. It will have lots of broken features which will still make it an unsafe place to store data that you care about.
As far as a reference for just some of the problems with their foreign key implementation, and as pointed out elsewhere in the thread, and found here...: http://sql-info.de/mysql/referential-integrity.htm l%233_5
3. Foreign Keys and Referential Integrity Foreign keys are an essential part of any relational database. In MySQL's foreign key support has been added on through the InnoDB extension and is continually being improved. However some aspects of the foreign key implementation, especially in combination with other areas of functionality, may cause unexpected problems.
3.1. ALTER TABLE ... SET NOT NULL
If a NOT NULL constraint is applied to a column, MySQL will set any rows containing NULL in that column to 0 (in integer or numeric columns) or '' ( empty string, in character columns). No warning is given.
In certain circumstances - particularly if the column contains character data - this may be quite practical, saving you an entire UPDATE tbl SET col = '' WHERE col IS NULL.
But - imagine the column is an integer foreign key. And the column it references does not contain a zero. Hmmm...
mysql> CREATE TABLE exmpl5 (
id INT NOT NULL,
val TEXT,
UNIQUE (id) ) TYPE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE exmpl6 (
id INT,
blah TEXT,
INDEX(id),
CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES exmpl5(id) ON DELETE NO ACTION ) TYPE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
INSERT INTO exmpl5 VALUES(1, 'test');
INSERT INTO exmpl6 VALUES(1, 'foo');
INSERT INTO exmpl6 VALUES(NULL, 'bar');
INSERT INTO exmpl6 VALUES(0, 'oops'); ERROR 1216: Cannot add a child row: a foreign key constraint fails
SELECT * FROM exmpl6;
| id | blah |
| 1 | foo |
| NULL | bar |
2 rows in set (0.00 sec)
So far so good - this proves the foreign key constraint is being taken seriously. Now the fun starts:
ALTER TABLE exmpl6 CHANGE id id INT NOT NULL
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 1
INSERT INTO exmpl6 VALUES(NULL, 'bar');
ERROR 1048: Column 'id' cannot be null
INSERT INTO exmpl6 VALUES(0, 'oops');
ERROR 1216: Cannot add a child row: a foreign key constraint fails
This is perfectly normal behaviour for a well-adjusted database. Now let's have another look at what the table contains:
select * from exmpl6;
| id | blah |
| 1 | foo |
| 0 | bar |
2 rows in set (0.00 sec)
I don't recall successfully inserting the zero in that second row - do you? Perhaps I secretly inserted a row into exmpl5 with 'id' set to 0?
SELECT * FROM exmpl6 e6 LEFT JOIN exmpl5 e5 ON e5.id=e6.id;
| id | blah | id | val |
| 1 | foo | 1 | test |
| 0 | bar | NULL | NULL |
Err, no. All I can think of is that the foreign key was arrested as a potential terrorist suspect while I was seeing what other databases did given the same set of queries.
(Note: MySQL 4.1.7 raises a warning after the ALTER TABLE statement above with the cryptic message Data truncated for column 'id' at row 2.)
You're either a fool or a troll. I've develop for Oracle and MySQL. MySQL is faster by magnitudes. Even something like say, logging in, takes much longer on Oracle than in MySQL. In MySQL you feel like you're interacting with something at about the speed of the unix shell. In Oracle you feel like you are sending telegraphs off to a server that cranks away and responds to you after a while. Data integrity is not the only Oracle slowdown. Things like character set conversion make everything bog down. As far as "code overhead", modern development doesn't use much in the way of database constraints. They're nice from an ideal-world point of view, but it's awkard to try to insert a row and catch a constraint violation coming from way down deep in your database access code and few people do it. A little experiment: Try building a table in Oracle with the absolute loosest of constraints. All columns can be varchar2(100) with no primary or foreign keys and no check constraints. To be fair, I wouldn't use any exotic options like PARALLEL or NOLOGGING, but do whatever you will. Load it up with 10M rows and check your watch..... Do the same in MySQL and check your watch.... On second thought, you probably don't have access to either of these pieces of software.
So what if the accounting system is off by $12,000,000? It's fast.
Who cares if the customers actually get the products they ordered? It's fast.
Who cares if we bill our customers for the right amount? It's fast.
Yes, because it's so much more professional and a more efficient use of your time and employer's money to hack together a half-assed system rather than learning how to use a tested and proven one that someone else wrote. This statement just demonstrates the fact that you have no clue what transactions are for, or how and when to use them.Why is it that the proponents of "one nation under God" are so eager to get rid of "liberty and justice for all"?
In one release they will get the features out there - and the next 4 or 5 releases will be spent optimizing performance. It's a step in the right direction - but it will take more time for MySQL to be ready for prime time.
I've been using postgresql since 7.0 in 2000. I admit that I never experienced what are largely characterized as the Bad Old Days of 6.x, but I doubt most people calling postgresql hard to setup haven't, either.
:)
If we talk about 7.x and if "./configure; make install; initdb; set up cron job to run vacuum" makes postgresql hard to install I guess I was wrong, but if it does your threshold of "hard" is going to cause you a lot of pain.
I'd rather rely on the database to ensure my data integrity as I can be reasonably confident it's been tested a lot more than my code. I can say "make that column unique in that table" and be 99.99% sure it will always be unique (let's leave the .01% chance of data corruption or some wacky bug). If I rely on the app to do the same, I have to introduce locking protocols to ensure the uniqueness of that column so I don't get a race condition. Back to my original point: I have a reasonable chance of leaving a bug in my code.
Now, MySQL has had the UNIQUE constraint for some time, but the same logic above can be applied to all the other features such as foreign keys & triggers; use the code in the DB to do your work and leave your app code to be simple and, hopefully, less bug-prone.
It's not a programming language.
It's a markup language.
http://www.cs.tut.fi/~jkorpela/prog.html
Wrong.
What the parent poster was saying was that he wants everything under his control to be as fast as possible. You can control your database speed (don't use one with 1-2 second delays per connection), app code (optimize, cache, etc), and even your outgoing bandwidth (big data center, etc). You can't control the end user's speed, but you also can't control however many end users there are.
If 'web development' was an area where slow data performance was acceptable, Amazon, Yahoo, and others wouldn't need to bother with indexes, DB upgrades, or other attempts to make their data performance faster. Because, after all, it's just 'web development', right?
creation science book
write-ahead logging is why I use and advocate PostgreSQL. Witness the recent power failure problems at Livejournal in which WAL recovery would probably have cut downtime to a few hours instead of days.
A 150GB database is very lower mid-range for a real-world database. Even with very "highly optimized" replacements of stored procedures and well-designed queries to replace views, and every tweak we could find or think of, they could never match the performance the client was used to on the old platform, even with more powerful hardware. After months of tweaking, the project failed, and I had to eat a lot of billable hours. The database choked down with any significant INSERT or UPDATE activity. In testing and demos, it was great - fast and zippy. When we threw the switch for a simulation of a real days work logged from the live system, the world nearly ended -- a 24 hr day of work for the live system took at our best 28 hours. For example, we had problems with queries that should require only indexed fields scanning an entire table for any given query. These are the problems you may never notice if you just run a small website from MySQL, but will hurt you when you have a table with 100M records in it.
I hope and pray that 5.x allows me to port this application. I'd love to get the whole thing end to end on a free platform. (Postgres wouldn't fly with the customer at the time because of vague issues with not knowing the product, not wanting to gamble on another OSS project, etc).
Everything about getting this app to MySQL was a nightmare. It was a complete non-stop cluster.. well.. you can imagine. By the time the project was called off I had devoted my most skilled programmer to looking for bottlenecks in and hacking MySQL code.
We revisited the effort when the 4.x series hit its stride, but were afraid of the chance of failure again. We noticed that hard limits had been raised, and that the client lib was solidly performing, but, well, we never got things to that level where it beat what was already in place.
Right now the database stands at 550GB or so (the server was upgraded to SQL2000 a while back [without incident, I may add]). If had of stuck with MySQL the first time through... I shudder to think where things would be 2+ years later. Failure, in this case, probably saved a lot of trouble.
So, to the educated masses: can anyone speculate about this releases capabilities? The list of requirements would be:
550GB, projected to be 1TB by 2007?
2500 tables
Full-text searching in approximately 1500 tables
Queries that routinely join 25-150 tables
~800 stored procedures
~1500 views
~1000 triggers
500-750 inserts/updates per second average, 20000 inserts per second peak, (approximately 40M new rows per day)
1800-2500 queries per second average, 15000 queries per second peak
Is MySQL 5.x the answer to my prayers? Or just a cruel reminder of why MS software costs what it does?
If you really believe coding triggers in the application "layer" (Should be in the data access layer if you were coding real enterprise apps) is the same logic, you really should have your head examined.
Consider your BEST CASE scenario:
Client (webserver) opens connection to database.
Client inserts into tA
Client receives identity response
Client inserts related into tB
Client closes connection
Now consider my best case:
Client opens connection to database.
Client inserts into tA
client closes connection.
A -drastically- smaller amount of network communication, and milliseconds faster because less discussion is going on between the client and server.
Don't even get me started on transactions, either.
Wow. It's been a long time since I read anything so completely wrong.
A database is not (to borrow my own phrase) just a flat file with an API. It's supposed to enforce a data model. In a perfect world, every piece of integrity and sanity checking would happen inside the database, with none inside the application layer. A database application that includes logic that looks like "if input is X then reject" is not a good database application.
Add to that, table inheritance, point in time recovery in postgresql...
I must really sound like a broken record... I distinctly recall asking _exactly_ the same question when the announcement for PostgreSQL 8.0 appeared on slashdot.
File under 'M' for 'Manic ranting'
It's faster than everything else because it doesn't have all the data integrity features that a RDBMS does.
I stick everything into the application layer, so MySQL lacking these features doesn't bother me a bit.
So, you use the database because its lack of features makes it faster. But then you reinvent the wheel by writing those features into the application. Surely you realize how likely it is that you are wasting all that speed and more in the application layer trying to do a bunch of stuff that your database is supposed to handle for you. Unless you really think you are so superior to the coders at Postgres that you keep an advantage (good luck), you are wasting your time and adding foolish complexity to your apps for no reason.
Given a choice between free speech and free beer, most people will take the beer.
Reasons to choose MySQL for a project:
- It's small and simple.
- The project will maintain logic in the application layer.
- Other "Enterprise" databases seem like overkill for the project.
Reasons to choose a more bloated MySQL with new features already well established in other database servers:
- None
Seriously, I can't imagine that this would be required or desired. Are they actively trying to move themselves out of the niche that they fill so well?
To be fair, I wouldn't use any exotic options like PARALLEL or NOLOGGING
Oh, so you think that to be "fair" making comparisons between the two, you have to cripple Oracle by not allowing use of features that speed it up? I'm not quite sure I follow your logic.... Doing things in parallel and skipping the logic on repeatable loads certainly doesn't qualify as "exotic" to anybody worth their salt with Oracle.
> I never once had a query or insert fail. Never once did I have data corruption
Of course you haven't had queries fail, this is exactly the point: when mysql doesn't like data it just modifies and it and inserts it anyway. So, strings & integers get truncated, invalid dates are accepted, and table configurations for innodb - are ignored and myisam is used instead.
And while using such a system, how would you find the corrupt data? Problems like these don't equally affect each row in a table. They affect maybe one out of a hundred: just enough to erode the credibility of your application.
Don't know how many times I've heard programmers tell me that their data was perfect, only for me to analyze it and produce an endless list of problems...
"data integrity ... I'm unconcerned."
You have absolutely no fucking business working with anybody's data. If you have a boss (and not just customers you've surely conned) I hope he's clueful enough to fire your incompetent ass.
If you had any fucking clue, you'd know that Postgresql beats mysql in speed. mysql has no speed advantage. Go back to the kiddie pool, you script kiddie.
First: Transactions isolation and innodb can solve most of these integrity problems. Both can be used with current (and earlier) versions of MySQL.
... ;) Count yourself lucky to have something like MySQL available for free today. ... My two canadian pennies.
Also, just make sure you give just enough rights to your users so they don't fsck up the database.
And why would someone make change to the tables ?? These are supposed to be done prior going into production phase as in the design phase. Have you ever heard of relationnal database theory stuff like the normal forms? There is really no such things as a bad MySQL but really just bad (or absence of) good design. You really need to look over this while developping a system. I learned to never trust the engine and use the programming language to determine if my data where correctly committed/erased, etc. The database is just that : a place where to store your data. I understand that its sometimes tempting to rely on the database error system instead of your own mecanism, but you should be smarter than the database and think about where those things (bad integrity, errors, suspicious things) could happen and prevent them or if you can't, code an error checking/catching mecanism to deal with them : error catching is the way to go.
I don't know about your background, but alot of criticism about MySQL come people who have poor programming skills and tend to code half-assed attempts of systems thinking that the database will solve all of their problems when they don't even know that an index on a frequently used column could increase their query times by ten folds...
Others come from very high-end legacy systems (think Oracle) and bitch and whine about MySQL not having this or that feature. Excuse me but Oracle's views , triggers and stored procedures are really usefull, but most of these things (if they are THAT necessary) can be done by having a good design and appropriate coding behind the application/system.
I'll say it again: a database is a place where to store data and preventing their corruption by providing you with an integrity checking mecanism
. That's it. Now if other features exists and you can make good use of them then good for you. But I see these as extras and not necessary when good design an implementation is done. Its really a pity these days when people bitch about MySQL but don't know how to implement a good data storing scheme using plain old textfile. (In my days
Phew
This is a stolen sig.
Since I'm using Hibernate for most new development, there's nothing stopping me from looking at the more advanced RDBMSs out there. Given how MySQL told us we were wimps for wanting things such as triggers and FKs, I don't really trust them to keep understanding what I need as a developer.
Information: "I want to be anthropomorphized"
I've been a DBA for 8 years and i've seen countless developers spend days, weeks programming features that are already in the database. I thought developers loved to reuse code but I guess some like to do everything from scratch by themselves.
I dunno, maybe if you had real dba's to tune the DB rather relying on developers supporting it, maybe you would see the same performance out of the 'slower' RDBMS like oracle, DB2, postgresql.
Personally I'd rather rely on transaction logging, integrity being handled by the database.. Why? Because depending on the RDBMS they've been there a long time and pretty stable. Not to slight your skills but your time is probably better off spent on other parts of your code rather than somethings thats been tried, tested and true.
"Thanks to the remote control I have the attention span of a gerbil."
The integrity problems are specifically because of poor implementation of foreign key and other constraints. Read up at http://sql-info.de or a million other sites on the net. There are fundamental problems in their implementation. It doesn't matter if you are using transaction isolation or innodb tables, MySQL silently changes data in many many circumstances. This is bad.
My 'users' aren't touching the database design. The database developers are. Multiple developers. When one makes a change and goes off shift, the next guy working on the table should immediately know if something changes made by the previous developer have invalidated some new data/scheme he's implementing. A database should never silently accept errors. It should always flag someone and refuse to make (or appear to make) a bad change.
I don't know about your background, but a lot of MySQL users haven't a clue how a real database should be designed or what real data integrity is. I'm not bitching about MySQL not having features, I'm bitching about it's shoddy implementation of the features it already has. Foreign keys (in innodb) do not work right! Constraints do not work right! Many other basic features that MySQL claims to have do not work right!
I'll say it again: A database should protect your data. It should not silently change data it doesn't like, instead of aborting the transaction and throwing proper error message.
Postgres is also available for free. And it's designers appear to care about data integrity.
I was just typing out an impassioned response to this, when I reread your comment and realised you were quoting from the MySQL documentation.
This is just insane. There is no other way to describe it. Clearly concepts like DRI clearly just whooshed right over the heads of the MySQL team.
meh
I learned to never trust the engine and use the programming language to determine if my data where correctly committed/erased, etc. The database is just that : a place where to store your data. I understand that its sometimes tempting to rely on the database error system instead of your own mecanism, but you should be smarter than the database and think about where those things (bad integrity, errors, suspicious things)
Part of the benefits of having a good db product is that you have a good level of trust when you delegate data for storage or retrieval. I don't think you need to go back and re-check the data you inserted/updated, you should check for unexpected errors and delegate to your application accordignly.
Sounds like your code is getting coupled with a sort of database management system because you suspect or can't trust your database to do fairly atomic operations.
My CAN$0.02
Most of your points just fall apart when multiple applications are accessing the same data set. In order to "never trust the engine" all the applications need to independently verify the consistency of your data.
If one application does something slightly wrong (perhaps due to a crashed webserver or whatever) if could leave inconsistent results in the database, and could cause other applications to fail, perhaps with very hard to find bugs.
For all the applications to reliably maintain the consistency of the data requires redundant code orders of magnitude more complex than simply relying on the DB engine. What if a web server crashes after it inserts some data but before it checks if it's right? What if another application accesses wrong data before the first application fixes it? All of these are non-trivial problems that take database developers a long time to get right.
I see why you came to the conclusion that you should "never trust the engine" when your engine is MySQL. However, perhaps it will save you some time if you use a database that you can trust (like, pretty much any other RDBMS).
Also, some databases are more than just a place to shove/grab data. They call them "Relational Database Management Systems". Relational mathematics (which have been developed over 30 years) are used to abstract the data from the physical storage. That's important, because application A might prefer to write data in one way, and application B might prefer to read that data in another way. RDBMSs avoid client-side manipulation or processing. You can not only get whatever data you want, you can get it in whatever form you want.
The kind of databases you're talking about are record databases, or some such. Those were available many years ago, and people decided they needed something better. Relational databases took over for a reason.
Social scientists are inspired by theories; scientists are humbled by facts.
And you even attempted to consider MySQL for such a project? Started development? Oh my, oh my. I hope you have learned your lesson.
I really wonder why, oh why, people even consider PHP/MySQL combo for any significantly active application development, lest likes of enterprise applications.
PHP is (and still it is) just a templating system for www applications. It's ridiculous, it's slow, it's insecure, but it's simple and forgives any brainfucks mistakes so that he can insert that dynamic list of his favorite pr0n movies of all time. MySQL is small, reasonably elegant, small-datasets oriented database with minimized mainteance overhead (on expense of chance to tune any more advanced options).
You could, possibly, just possibly, pull this off with a postgresql (we have), assuming you had a good DBA with experience in PG configuration and optimisations, but even attempting it with MySQL?
Fuck, CHOOSE THE RIGHT TOOL FOR THE JOB! And even if you have client that believes in yet another OSS publicity shit flying around (like, we are the only, the best, M$ is evil, oracle has larry, db2 is so oldschool and informix is satan's outspawn) RUN, the shit will hit the fan really soon.
(and yes, your requirements most obviously calls for a nice, good Oracle 8i+ database (it isn't THAT expensive) with java frontend and data objects caching in middle layer. Nothing that experienced programmer couldn't compile within a month (based on tablecount). And everybody would be happy. Oracle DBA's are pretty easy to find, even reasonably competent ones and Java programmers grow on trees.)