'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!

Comments

being a paying customer...

[fishdan]

I just finished the Using and Managing mysql course in Boston. VERY much worth it btw if you're like me -- A developer and not a true DBA who supports the Database because there's noone else.

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).

Re:being a paying customer...

[ShieldW0lf]

Of course we're trolls. We don't have any points over here in our camp, just a bunch of hooting and hollaring.

Some of us actually hold the database they use to higher standards, and aren't interested in using middleware hacks to do things that should be the domain of the database engine. If you want to call that a troll, go ahead. There are probably some very complex systems out there using XML files as their data source. But that doesn't mean XML is a good database either.

A good database should refuse to let you fill it with junk. Full stop. It should hold it's data integrity and force the application to break before allowing it to be corrupted. MySQL doesn't do this.

Some examples?

NOT NULL perhaps:

CREATE TABLE null_1 (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

INSERT INTO null_1 (id) VALUES(1);
INSERT INTO null_1 (text1) VALUES('test');

mysql> SELECT * FROM null_1;
| id | text1 | text2 |
| 1 | | foo |
| 0 | test | foo |

2 rows in set (0.00 sec)

If a column has been flagged not null, that generally means you shouldn't allow an insertion without a value for that column unless a default value has been expressly declared for the column. Of course, MySQL just sticks in some junk and keeps right on trucking.

Or maybe some data truncation... try this guy:

mysql> CREATE TABLE bounds_test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
price NUMERIC(4,2),
code VARCHAR(8),
numbers_only INT
);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO bounds_test VALUES (
99999999999999,
21474.83,
'ABCDEFGHIJK',
'A quick brown dolphin...'
);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM bounds_test;
| id | price | code | numbers_only |
| 2147483647 | 999.99 | ABCDEFGH | 0 |

1 row in set (0.01 sec)

Wow, MySQL must be psychic... that was exactly what I wanted inserted in that table!

Yeah, I'm really going to trust something more sophisticated than a forum to this database...

Re:being a paying customer...

[dago]

Hint for moderators : the parent is karma whoring by pasting content of the MySQL gotchas page.

Or trolling ? Or both ?

Anyway, if somebody is using mysql in any kind of environment, he surely has already heard of this by now unless he's living in a Cave, thanks to all the postgresql zealots.

Re:being a paying customer...

[kpharmer]

> 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...

Re:being a paying customer...

[Anthony Boyd]

If there is an area where slow data performance is acceptible, its web development, for exactly the reasons you gave. The bottleneck is often the wire... not the database, not the browser or CPU behind the browser. Having a faster database, just means that your users get to wait faster!

No. These delays are not parallel, they are serial. In other words, the delays are cumulative, building on top of one another and extending the delays further. If the network, CPU, database, and code each add 500ms lag, that's a 2 second wait for the Web page. By getting the code to a point where it executes in only 100ms, and by switching to a database that can return the query in 100ms, the end-user now waits only 1.2 seconds for the Web page. You may think that the difference between 1 and 2 seconds is insignificant, but that would be the kind of thinking that got Intrabuilder into trouble. I recently had outshine.com move to a new server, and instantly saw visitors to the site decline sharply. Why? Well, the new server was doing DNS lookups on each request, and it wasn't caching the results for more than a second! So nearly every request had a 3 second delay in response. That alone killed off about 25% of my traffic. In an e-commerce site, such a loss would be a disaster.

Re:being a paying customer...

[Anonymous Coward]

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:

mysql> CREATE TABLE null_1 ( id INT NOT NULL, text1 VARCHAR(32) NOT NULL, text2 VARCHAR(32) NOT NULL DEFAULT 'foo' );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO null_1 (id) VALUES(1);
ERROR 1364 (HY000): Field 'text1' doesn't have a default value

mysql> INSERT INTO null_1 (text1) VALUES('test');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

mysql> CREATE TABLE bounds_test ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, price NUMERIC(4,2), code VARCHAR(8), numbers_only INT );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO bounds_test VALUES ( 99999999999999, 21474.83, 'ABCDEFGHIJK', 'A quick brown dolphin...' );
ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1

Re:being a paying customer...

[ultranova]

Did you spend a lot of money on a DB cert, only to be angered that others are getting the same job done with OSS and good programming techniques?

You seem to be implying that OSS databases make do without transactions. This is incorrect, since PostgreSQL has supported them for a long time (and, AFAIK, so has MySQL, for some table types - I wouldn't know for sure, since I use PostgreSQL myself).

I was under the impression that transactions enable you to lump together a series of queries into one transaction, so if something fails, none of the queries/inserts execute. If I'm wrong, then I stand corrected.

You are correct, with the addition that transactions guarantee that the changes have been logged into permanent storage (disk) when the "COMMIT" command returns.

I understand the importance of data integrity but also keep in mind that in the 8 years that my career has focused on this business, I never once had a query or insert fail.

What happens if there's a power outage in the middle of a some update that requires multiple queries ? Or in the middle of a single update query ? Or if the database server simply crashes ?

PostgreSQL executes each command in as a "mini-transaction" with implied BEGIN and COMMIT wrapped around it (assuming, of course, that the command wasn't already a part of a transaction), which means that if power gets cut in the middle of a command like "UPDATE customers SET DEBT = DEBT + 1", then either all the records are updated or none is. As a result, data stays consistent even when if something unexpected happens - the whole thing works a bit like a journaled filesystem like ext3.

Of course you can work around these kind of issues in the client software, but it's more convenient to let the database system handle them.

"Like a real DB"

[Space cowboy]

Considering that MySQL probably runs more databases than all the others put together (it being the poster-child for most OSS projects involving DB's), I think that's a little harsh. Sure it's not ACID, but it does well enough for most purposes...

As a data-point:

simon% mysqladmin ver
mysqladmin Ver 8.40 Distrib 4.0.18, for suse-linux on x86_64
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.18-Max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /opt/mysql/mysql.sock
Uptime: 5 days 21 hours 32 min 52 sec

Threads: 2 Questions: 103591631 Slow queries: 101 Opens: 181809 Flush tables: 1 Open tables: 64 Queries per second avg: 203.291 ... the only reason it's only 5 days is a server upgrade, but its performance seems pretty "real" to me :-)

Simon

Re:Beta?

[JianTian13]

http://dev.mysql.com/doc/mysql/en/news-5-0-3.html

This Beta release, as any other pre-production release, should not be...

Uhh, just look a little deeper.

Dupe

[bdigit]

Wow Taco, all I did was search mysql and bam

http://developers.slashdot.org/article.pl?sid=05/0 3/28/1856255&tid=221&tid=8/

Expect more hatemail.

Re:Replication? Clustering?

[fishdan]

yes, all those things. Replication has been around for quite a long time -- very easy to configure too.

http://dev.mysql.com/doc/mysql/en/replication.html

http://dev.mysql.com/doc/mysql/en/mysql-cluster-ov erview.html

Re:What about foreign keys?

[fishdan]

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

Re:Replication? Clustering?

[jbellis]

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.

Article unreadable

[vorpal22]

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.

Re:(not fp)

[LurkerXXX]

And did they fix it so that you input out of bounds data in a field that has constraints on it, it throws an error rather than just silently changing your input to a value it likes? Silent data corruption kinda sucks... That's why I use Postgres.

Re:Meanwhile, back in 3.23.x land...

[jaylee7877]

RedHat Enterprise Linux 4.0 ships with MySQL 4.1 (and the server is fully supported unlike RHEL3). Fedora Core 4test1 ships with MySQL 4.1. wish granted.

Re:get over it already

[Anthony Boyd]

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.

No. If that were true, then they would have seen far greater adoption rates. PostgreSQL has a history of difficult installations -- I tried it years ago and was stymied, then tried it again in 2003 I think and was stuck doing VACUUM (sp?) over and over. It also had some byte-size limits, but I don't think I even understood that complaint or experienced it. In the meantime, for MySQL I just hit "install" and it did, with excellent defaults, so that I did not need to babysit it at all.

And as for Firebird, no. I worked at Borland, I saw the limitations of that monster. I'm not suggesting that Firebird is problematic now -- I suspect it is devoid of problems to the point that I'd prefer it over PostgreSQL. I am merely disputing your assertion that it has been "just as easy" as MySQL. It hasn't. It may be now. But now may be too late.

Also note that I am not suggesting that MySQL is perfect. But let's focus on legitimate complaints, such as the way it quietly recasts data and stores it, rather than error out. For example, storing dates as 0000-00-00 when your table setup did nothing of the kind. Once that little (in)convenience introduces itself to you for the first time, you really wish you had been using PostgreSQL. Of course, again, it looks like a "compliance" mode is being integrated into MySQL, so by the time I'm ready to ditch MySQL over this, they will have fixed it, and I'll stay. :)

i'm confused....

[drew]

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?

Re:The biggest problem is not technical

[Trifthen]

* Easy replication on MySQL/ Not so easy on PostgreSQL

Not really.

Go ahead and click on any of the links on that page which describe bugs fixed in a release of the many branches. In almost every one, there's a critical bug that causes replication to fail, turn itself off, crash mysql, or otherwise act in an unpredictable manner. We've wanted to use it for two years now, but every release has some terrible flaw that makes this impossible.

Heck, they've only recently fixed a bug in the 4.0 branch that's been there since at least 4.0.12 which caused mysql to silently segfault and restart itself. Not to mention the bug before that, which segfaulted, restarted mysql, and randomly corrupted open tables. 4.0 is just now getting to the point where I'd recommend it to other people. I won't touch 5.0 with a mile-long pole until it hits 5.0.20.

Re:Firebird more popular than Postgresql?

[Anonymous Coward]

No, the stats aren't "true." They come from a single Evans Data survey whose results are uncharacteristic of all other surveys, including other surveys by Evans Data. Why can be explained if you look at the survey sample.

Evans pointed out in their release that 90%+ of the respondees did their development on Windows. At the time of the survey, there was no PostgreSQL release version native on Windows. Further, Evans specifically surveyed users who use open source. PostgreSQL doesn't account for 11% of the world's database developers, either, more like 6-8% according to other sources. Then there's plain old sample error.

So, that 39% of Evans respondees to *one single* survey of Windows-based OSS developers have and use Firebird? Completely plausible.

Of course, this noise about Firebird should hopefully get more people to evaluate it. We've put a Firebird session into OSCON, so mabe the FB folks will join us in Portland?

--Josh Berkus
PostgreSQL Project

you're kidding, right?

[portscan]

from the article:

"stored procedures, triggers and views,"

or just spend 2 minutes on the mysql website and you could have found this.