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!
Let me guess, MIESQL? Is this where the browser and database are integrated?
It could be worse, it could be Monday.
What, does it come with data integrity, too?
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"?
It's astonishing how far mysql has come. I'd been using 3.23 since before the dawn of time. Like most users of my ilk, I'd hacked alot of "databasish" functions together at the application level. My dilemma now is throwing away all that work to migrate to something I know is better. But there's no doubt that replication, triggers etc are all worth it.
The *best* thing that I got out of the class though, was to talk freely with the MySQL guys about their reality of trying to make a living with a "mostly" free product. They convinced me to buy a membership in MySQL Network which is essentially support that I probably won't use. This upgrade they are turning out though is good enough to make me WANT to pay (once).
Nothing great was ever achieved without enthusiasm
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.
Store procedures. Triggers. Views. It's like it'll be a real DB!
So, the Slashdot editor, whose own Web site uses MySQL, is actively trolling other MySQL users in the article summary? Hilarious!
Do you like German cars?
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.
This is certainly good news for MySQL, but many open-source advocates forget about other open-source DBs like PostgreSQL, which has had these features for a while. But competition is always good, and it's good to see MySQL stepping up its value.
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.
Uhh, just look a little deeper.
Wow Taco, all I did was search mysql and bam
0 3/28/1856255&tid=221&tid=8/
http://developers.slashdot.org/article.pl?sid=05/
Expect more hatemail.
http://dev.mysql.com/doc/mysql/en/replication.html
http://dev.mysql.com/doc/mysql/en/mysql-cluster-ov erview.html
Nothing great was ever achieved without enthusiasm
Yes. http://dev.mysql.com/doc/mysql/en/ansi-diff-foreig n-keys.html has all you ever want to know about foreign keys and mysql
Nothing great was ever achieved without enthusiasm
How do you copy and paste verbatim from the linked article, but then spell 'criticism' wrong?
How useful is it to be able to scale to large numbers of servers if your database doesn't even support the features your application developers need?
The number of companies who NEED clustering is much, much smaller than the number who need triggers, views, etc. No database professional would touch a product that doesn't support those with a ten foot pole, which is why people who actually know databases (as opposed to your average 50-pages-of-PHP newbie) have disdained MySQL for so long.
Then what will Slash use?
sulli
RTFJ.
US Democracy:The best person for the job (among These pre-selected choices...)
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.
The article was unreadable. I went to the page and was presented with a large, intrusive flash-based (I believe) advertisement that refused to let me read the text until it was over, and given the obnoxious nature of the ad, that's not a feasible option, IMO.
> 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?
If you recall, their management made the (unconvincing) argument that 99% of the time people didn't need fluff like:
* referential integrity (pk & fk constraints)
* views
* triggers
* stored procedures
So, they never implemented this because they had been arguing that nobody needs it anway. Nevermind that it's been standard fare for over twenty years.
Personally, although I'm glad to see them support views, I would rather have them clean up their exception-handling than add triggers & stored procedures right away. The problems with silent errors, silent truncation of strings & integers, date validation errors, etc are far more serious than what they are adding in this *beta* release.
Maybe in five years that'll be the release that fixes 15 years of criticism?
...but legal. The community seems to have over-looked the license change from the 3.xx days. I should know. I had software that was permissible to be run on gratis-MySQL, but as of 4.0, the license changed. I now use PostgresSQL which I throughly advocate, not just because of the license, but because of the feature set and the anal developers.
There where 3 reasons why MySQL got popular:
* Free
* PHP
* Windows support
Free has been removed because fo the license change. PHP is a non-issue, these days, and naitive Windows support is now in PostgreSQL 8.0.
Now, we have a much more level playing feild. On brief analisys we have:
* Easy replication on MySQL/ Not so easy on PostgreSQL (when only soncidering the free varietyfree)
* Experimental/new features on MySQL, but throughly tested features on PostgreSQL.
* Limited license on Mysql, BSD license on Postgres.
Those three are, IMHO, the remainging differences pertinant to typical DBS selection.
Then there are the addional features. I like the sandards-compliance and no gothcas (MySQL Timestamp) of PostgreSQL.
Just my $0.02
Slashdot's rate-of-post filter: Preventing you from posting too many great ideas at once.
Or maybe they just hired all those burned-out EA employees.
ex-EA'er: "Can I get 3 whole months to do 10 years of wish lists?"
MySQL management: "Take your time- have 4!"
"WOW....! You're the best boss a guy could hope for!~sniff~"
Nobody at Slashdot is ever sarcastic. Ever. Especially me. I've never been sarcastic, and I don't understand sarcasm at all. I can't smile and laugh when someone makes an obvious crack at the fact that the insanely popular web site where his joke will be viewed is run by MySQL. The joke itself is stored in the database he's making fun of. It's a good thing, though, that he wasn't being sarcastic, because people here have a hard time with sarcasm.
That's right, everyone at Slashdot hates and fails to understand sarcasm. Except moderators. Those guys love the stuff. They eat it up and mod funny things "funny" even though the sarcasm they perceive isn't really there, and the comments are just trolls or flamebait.
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.
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 ...
"the PostgreSQL philosophy is to throw in everything and the kitchen sink and then make it work right, whereas the MySQL approach is to add things one at a time, making sure that everything works right from the beginning"
I guess that's one way to make MySQL's "foreign keys are for wimps" documentation look good: anyone who implements more than MySQL does is just throwing stuff together and worrying about debugging later! Never mind that just about anyone else is more standards compliant; the MySQL team are heroes of stability and everyone else is just trying to add features for marketing.
Nobody really needs that ACID crap anyway, and if you say you do, you're just a poseur...
Here's what the MySQL docs (source: http://web.archive.org/web/20000619203550/http://w eb.mysql.com/Manual_chapter/manual_Compatibility.h tml) used to say about foreign keys, for instance:
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.)
So, are they just going to pretend that 10 years worth of flaming on message boards, mailing lists, etc. about how you don't really want those features in your rdbms because you can just implement them in application code without slowing down the database never actually happened?
If I don't put anything here, will anyone recognize me anymore?
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?
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.