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!
What, does it come with data integrity, too?
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?
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.
US Democracy:The best person for the job (among These pre-selected choices...)
Why, version 3.2, of course -- to match the version of HTML they are almost confirming to.
Do daemons dream of electric sleep()?
...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~"
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 ...
You forgot the most important part: Tell everybody how they don't need a feature, and how they can work around it with application code, and how adding that feature will make MySQL suck to the high heavens, and you really don't want that now, do you? Then, when they finally do add the feature, act like they've never said anything bad about it before. The endless sniping at the MySQL team is completely justified by their cavalier attitude towards proper RDBMS design, and their insistence that the programmer can "easily" work around their incompetent RDBMS design in his application code. If that sniping has encouraged the MySQL to abandon its "careful development process" in favor of turning MySQL into a real RDBMS, then mission accomplished.
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.)
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"?
*shakes head*
;-)
Putting data logic in the application is a problem that was (supposedly) solved in the 60s. Apparently some folks didn't get the memo..
You wanna know what the problem with putting logic in the application is? Yup, you (hopefully) guessed it: there are very few systems where only ONE application is guaranteed to modify the data.
Maybe your client goes in with a DB tool to quickly change a value. Maybe the summer hire writes a quick Ruby script to adjust some values. Maybe your client just wants a Java frontend to go with your web app.
Are your constraints well-documented? Are you sure you got them right when you translated from Ruby to Java? Personally, I don't want to even *try*.
I have plenty of "war stories" about this. I'm surprised you haven't run into problems. My first big programming project was an ecommerce database without referential integrity constraints. Sure enough, there was bad data in the DB.. order line-items without corresponding orders. The client had just gone in and deleted them by hand, and had been doing this for 5 YEARS. The problem? Author royalties were paid based on order line-items. So they had been overpaying their supplies, basically, for years.
A bug in your code is easy to fix. A bug in your data means you've got a dependency graph starting from the moment the data entered the database, extending into the future. Who knows what other bad data was produced based on that one piece of bad data? How do you fix it? How do you rollback/replay ALL your data changes??
You MySQL lovers can crow all you want. Frankly I don't know why you would pick a DB with less features when others are freely available with the same cost, but whatever. Until it's possible to build a system in MySQL that doesn't allow bad data, those of us who *really* know what we're doing won't touch it, even for throwaway projects (I've got plenty of war stories about prototypes that are still in production use too).
On second though, considering how many consulting dollars I've made fixing broken crap, go right ahead...
> It's faster than everything else because it doesn't have all the data integrity features that a RDBMS does.
Hmmm, really? Lets see...
if you're using myisam (the fast io layer), then all locking is done at the table level. Which means that if you get multiple connections attempting to write to the same resource they'll all have to wait while their operations run in series. Fast? Nope, hard to imagine a slower scenario than that.
again, if you're using myisam (again the fast solution), and need to select 10% of the rows of a table with 10 million rows, what will it do? It'll do a scan of all 10 million rows. What would oracle, db2, or informix do? Rely on partitioning to just scan those 1 million rows. Of course, these commercial databases will also break the query up into pieces and run them all in parallel across multiple cpus on your server. You could easily find mysql taking 40x as long to respond to this query as one of the others.
How about if you've got a complex query? Well, mysql admits that their optimizer is very primitive, and will take a while to fine-tune. Great, so now you just write a few different versions of your query until you get the speed you want, probably depending on 'gaming' the optimizer. Then when you upgrade the software next year you'll find that your gaming is killing performance. Time to rewrite the query. No big deal, we all went through this (about twenty years ago, and glad to have it behind us).
ok, how about using their non-isam option. Ok, now we've got the kind of data integrity we've taken for granted since about 1981. But, reads are very slow, often 1/10th the speed of myisam.
On the Innodb site they brag about getting 800 inserts / second. I think the most recent db2 benchmark on a 4-way intel box hit almost 3,000 transactions per second. Note: Not just simple inserts & updates, it was a tpc benchmark. And that's on a low-end box. At the high-end db2 is running 80,000 of these transactions per second.
Fastest? please, only in read-only transactions running on tiny hardware...
Why don't you give 5.0 a try? You'll find things have changed a bit. You can startup the server in TRADITIONAL, STRICT_ALL_TABLES, and ANSI modes and get the behavior you want:
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.
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.