'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

Beta?

[DarkHelmet]

A beta of the next major version of open source database MySQL was released on Monday and includes supports for a number of features that could appeal to corporate users.

Beta?

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

As you can see, MySQL 5.0.3 is still in alpha status. It hasn't reached Beta yet.

I'm not sure where the whole "beta" thing came from. Maybe 5.0.4 will be beta, but I don't believe 5.0.3 is.

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.

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

[niittyniemi]

> As someone who has been developing enterprise apps with MySQL
> for a while now, I'll answer this, even though I'm 99.44% sure
> you're trolling: what we've always done, so far, is put all the
> triggers in the application layer. Now we can make "real"
> triggers in the DB layer, but guess what?
> The logic is exactly the same.

Go and read Date, about why a 3-tier architecture is a good thing and then come back and tell me you develop "enterprise apps with MySQL....[with] triggers in the application layer" whilst still keeping a a straight face.

+5, Interesting? How about -5, Clueless. It's people like you who give IT a bad name. Do your homework before "developing enterprise apps"! I spent a considerable part of one year doing relational theory and RDBMSs at uni, what have you done? Obviously not a lot and quite frankly it shows.

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.

Re:being a paying customer...

[cduffy]

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?

Maybe he spent a lot of time cleaning up a legacy (read: developers bailed long ago) database application that was consistantly hitting bugs that wouldn't have existed if the DB had been safeguarding the app layer from screwing up too bad. That kind of experience tends to lead to a fairly nontrivial amount of anger over bad databases (and, worse, refusal of app developers to use readily available good ones).

I *have* seen actual DB corruption -- but more often, I've just seen bad inserts that a competant database, used correctly, could have stopped the app layer from committing. It's bad enough when you're the initial developer -- it's a whole different ballpark when you're the guy who's been hired on to clean up his mess.

It's a Good Thing that MySQL has finally caught on and started trying to do things right. I wonder, though, how long it'll take for the developers weaned on it to do likewise.

Re:being a paying customer...

[Len Budney]

Now we can make "real" triggers in the DB layer, but guess what? The logic is exactly the same.

Um, no, there's an important difference. Triggers are intended to ensure data integrity, not to implement application logic per se. When you denormalize a relation, and introduce redundant data, you also introduce the certainty that the redundant data will end up out of sync sooner or later. Triggers are there to correct that problem.

If you put the same logic in your app, you guarantee that someone, somewhere, will update one datum without properly updating the redundant copy. Hilarity ensues.

"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

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?

[drspliff]

MySQL already has foreign key support when using InnoDB tables. And from what i've seen it's very stable.

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:Information?

[Anonymous Coward]

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"?

Here you go.

Errors in MySQL

[Anonymous Coward]

I wonder if they will have thought of correcting a few pertinent errors in this new release ...

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:Sounds like troube brewing

[mikaelhg]

MySQL isn't free as in free beer anymore, for non-GPL projects, now that they have GPLd their JDBC and ODBC drivers.

InnoDB is ACID-compliant.

[DoctoRoR]

Why does this stuff always get propagated? There are several table types in MySQL. If you want ACID, use InnoDB and not the default MyISAM:

http://www.mysql.com/products/mysql/

Also http://www.innodb.com/index.php

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:Who's still using mysql?

[Keamos]

Okay, let's see...

Livejournal runs on mySQL (with memcached), and has had 970k users update their LJs in the past week. Assume that on average, each journal gets 10 views in that one week (it's probably higher, as there are some really large communities). That's 9.7 million page views in a week, or ~ 40 million a month. Plus the people who just browse and don't have an account.

Slashdot itself uses mySQL..I have no clue about the pageviews, though I know it's in the millions a month.

Google is a mySQL customer. As is Dow Jones, the people that publish the Wall Street Journal and its online editions. So is the NYSE, NASA, the US Census Bureau, everyone's beloved AOL, the Associated Press, Texas Instruments, among plenty others.

Just because mySQL doesn't work for your project doesn't mean it doesn't for others. Not to mention that it's free and ships with almost every major Linux distro out there.

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

Re:sqlite may be useful for the simpler tasks

[finnw]

sqlite has its own performance weaknesses.
Last time I looked at the source (about 6 months ago), ORDER BY was handled by buffering the results in memory and sorting them before returning them.
You might expect that an index on the column(s) you are sorting by would be used to order the results, but it isn't.

My prediction

[Aumaden]

Prognosticator that I am, I predict that MySQL 5 will achieve release status between April 18 and 21.

Re:will they fix gotchas too?

[sql-gotcha]

Err, RTFM? 13.1.8. Subquery Syntax. And an article too: Nesting SELECTs in MySQL 4.1. Note that this kind of statement:

DELETE FROM t1
WHERE id IN (SELECT MIN(id) FROM t1)

won't work though, at least as of 4.1.

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

[DAE51D]

Yes. The license does really suck for companies. Our company is stuck at 4.0.18 as well because after that point, they switched from GPL to their own license. http://www.mysql.com/company/legal/licensing/comme rcial-license.html It's not very condusive to using mySQL on a sold appliance/product because mySQL wants > $600 per license!!! https://shop.mysql.com/ Hurry up and save 0%. offer ends March 31st. LOL!

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

Re:Here's a step-stool

[scheme]

No, it isn't unethical. The main focus of MySQL has always been speed. MySQL is MUCH faster than PostgreSQL. If speed is an issue, then MySQL is the proper database to use (and MySQL has supported transactions since 4.0 see here). PostgreSQL is fine for a small to medium site or business, but for a mainstream site, it will not keep up.

I guess the .org and .info dns registries aren't mainstream then. They're running postgresql to handle those registries and they seem to keep up without problems.

Re:foreign keys? try write-ahead logging

[ahodgson]

No, you still need to vacuum Pg 8.0. It includes an auto-vacuum daemon that does a decent job, though.

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.

Re:Opinions...

[quantum bit]

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

Gaaaaah! The tragedy of your story is that Postgres quite likely would have worked well for the project your describe. MSSQL still uses row locking, so Postgresql > MSSQL for loads with high insert/transaction rates and many concurrent queries.

MySQL is great for simple stuff but absolutely bogs down when you throw anything complex at it. PostgreSQL isn't quite a match for Oracle yet, but it's getting damn close for mid-level stuff that doesn't need replication.

Re:foreign keys? try write-ahead logging

[Anonymous Coward]

InnoDB (that livejournal use for most of their tables) implements a doublewrite buffer for flushes to disk like this, but you can disable that if you want for performance reasons (*not* the default, though), like you probably would if you have battery backed up drives.

The problem with LiveJournal was that they had this flag turned off to sync() properly since they trusted their hardware's settings to be true.

This would have happened with any other RDBMS as well.

Re:Who's still using mysql?

[urbaneassault]

Yeah, especially not something with millions of queries in a large cluster environment.
Sabre has been using mySQL to power their GDS backend, which also includes Travelocity, for several years now.

Re:The biggest problem is not technical

[scorp1us]

This is not FUD at all. I leave it to the reader acusingmy of FUD to obtain and compare the licences between v3 and v4.

3 Allowed MySQL use where MySQL was used to fetch and organize data that was already availible through other means. Not only did we have a sequential text-log of all the data, but by running MySQL in parralell and doing some parsing and storing offsets of where the data was in the file, it greatly simplified searching.

v4 not only prohibited that, but it forbids use in any commercial environment. There is ONE exception. That is the PHP exception clause. PHP is (and commercial sites run on PHP) are permitted to use PHP. If they had not granted that, MySQL would eb effectively dead. It is short-sighted to assume that the only software interacting with MySQL is PHP. The client libraries are very often directly linked in.

Under the v3 license, this was the case. But the v4 license would require the app to be GPL as well.

IT IS ALL WELL DOCUMENTED. READ IT.

With PostgreSQL's BSD license, NONE of this crap happens.

Re:foreign keys? try write-ahead logging

[Pathwalker]

A regular vacuum does not block reads or writes. It does block changes to the table definition, so you won't be able to add, delete, or change the type of fields while it is running.

Running vacuum full or vacuum freeze do lock the table while they are running, but the need for vacuum full can be avoided by running vacuum before the free space map fills up, and vacuum freeze is only needed to prepare a read only database so that it never needs to be vacuumed again.

This is discussed in the docs here.

Re:But does it support WITH RECURSIVE?

[mark-t]

No

Re:foreign keys? try write-ahead logging

[Kent Recal]

You can not reliably turn off write caching on IDE disks.

Quote:
However, some IDE drives will report that their write cache is turned off, but still use it and remain unreliable. It is difficult to fine out which drives are lying without extensive testing.

Re:foreign keys? try write-ahead logging

[Jamesday]

The book chapter you quoted from isn't current, though it's still very useful as an overview. Recent versions handle this, both logging consistently and rolling back consistently, including to replicating slaves. See the manual section on the binary log.