'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

Most Important Ever?

[lecithin]

Let me guess, MIESQL? Is this where the browser and database are integrated?

Re:Most Important Ever?

[tuxnduke]

Yeah, I'd vote for MIESQL (btw mies is Finnish and means man).. so it would suit the up'n'coming improved mysql very well.

Mysql 5.0, - the manly edition with triggers !

Re:Most Important Ever?

[Anonymous Coward]

MiEsql. That must be Spanish for MySql!

Spanish often puts Es in front of words that start with S, for example:

Latin "stare" -> estar
status -> estado

I think there is some linguistic term for this added "eh" sound, but I don't remember what it was. And of course mi is Spanish for "my"...

My Sql -> Mi Esql.

(A todos lectores hispanos: siento por haberte ofendido y defamado la lengua. Solamente soy gringo tonto con chistes malos.)

Re:Most Important Ever?

[blonde rser]

Is this where the browser and database are integrated?

clearly that was firebird

Re:Most Important Ever?

[RinzeWind]

A todos lectores hispanos: siento por haberte ofendido y defamado la lengua

Don't worry. Bush does that all the time.

(not fp)

[Ari Rahikkala]

What, does it come with data integrity, too?

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:(not fp)

[InfiniteVoid]

Heh, yeah. My personal favorite is when you run BEGIN TRANSACTION; on a table that doesn't support transactions. It fails silently. Sortof makes that ROLLBACK you call several commands later a bit useless.

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.

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

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.

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]

Am I the only one sharing a chuckle at the thought of a bunch of typically skilled MySQL users with triggers at their disposal?

I can hear the hard drives grinding now

Re:being a paying customer...

[Daniel Dvorkin]

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.

Given the widespread use of MySQL to run some very complex systems, I rather suspect that you, like most anti-MySQL trolls, have no idea of what the "typically skilled MySQL user" actually does. Yes, there are lots of people who pick it up because it's free, easy to use, and widely known who have no business doing any kind of DB work. There are also those of us -- a lot more than you think -- who make our living at it and know exactly what we're doing.

Re:being a paying customer...

[golgotha007]

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.

Re:being a paying customer...

[jbellis]

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.

Re:being a paying customer...

[Donny Smith]

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

Re:being a paying customer...

[fitten]

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.

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

[MindStalker]

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.

Re:being a paying customer...

[Anthony Boyd]

I'm hoping that the new MySQL won't sacrifice speed for all these features.

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.

Re:being a paying customer...

[trulymadlydeeply]

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.

Re:being a paying customer...

[Tassach]

It's faster than everything else because it doesn't have all the data integrity features that a RDBMS does

Because after all, who really cares about data integrity?

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.

However, I stick everything into the application layer, so MySQL lacking these features doesn't bother me a bit.

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.

Scheduled DB backups and logging in the application layer keeps me from needing any transaction or rollback features.

This statement just demonstrates the fact that you have no clue what transactions are for, or how and when to use them.

Re:being a paying customer...

[Anonymous Coward]

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

Re:being a paying customer...

[larien]

Yes, the logic may be the same, but part of the reason to use a database is to remove the whole clunky data integrity issues from your application. People make bugs in code; fact of life. However, after sufficient testing, the bugs will be removed.

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.

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

[cosinezero]

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.

Re:being a paying customer...

[As Seen On TV]

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.

Re:being a paying customer...

[LurkerXXX]

You've never had data corruption THAT YOU KNOW OF. The problem with it is that MySQL rarely throws an error, so the bad data going in goes in silently.

This isn't data corruption that throws up a big flag, 'your database file is corrupted and you can no longer access it', it's sometime subtle anomylies in the data. Some of which can be very very bad if you are relying on the data to be good (such as dealing with money, or used in calcuations that you rely on, etc).

I've often had MySQL folks told me they have never had data corruption. Until looking in detail at the real data. Then we've sometimes found it. All it takes is leaving out one tiny constraint check in your application you are writing.

Just because you haven't spotted the bad data, doesn't mean it's not in there. That's why I don't trust MySQL. I want to know the database is keeping the data safe. I want real foreign key and other constraints. Really enforced, with errors thrown up when someone tries inserting bad data. Not just guessing that since I haven't spotted any bad data, there's none in there.

If it's just for use in a blog, sure, who cares. But I generally work with databases where I actually care about the data. Postgres, Oracle, etc is the way to go if you really care about the data.

Re:being a paying customer...

[jusdisgi]

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.

Re:being a paying customer...

[As Seen On TV]

Do you know the story of the software that runs the launch of the space shuttle? I read an article about it in Infoworld years ago. I don't guess there's a copy on line.

Short version: This software, which is maintained by a team of fewer than 20 people, has never failed in the field. It's multiply redundant, but those redundancies have never, to date, been necessary. It works perfectly all the time.

From the time that the first line of code was written back at Lockheed in the late 1970s to the time at the article I'm telling you about was written (1996 or so) a grand total of exactly 17 bugs was found in unit testing.

One member of the team was quoted as saying, "We don't work late. We know that if we work overtime and get tired, we might introduce a mistake. If we introduce a mistake, somebody that we go to meetings with every week will die."

(That's me paraphrasing, obviously, but the "somebody will die" wording is a quote. That's the kind of thing that stays with you, you know?)

When you consider that a huge amount of computer code being written today is used in things like airplane avionics systems, car and truck engines, life-support machines and, yes, space ships, you realize that this whole "we're not perfect, we rely on users to send us bug reports" attitude is laughable in the extreme.

You don't get a bug report when the software that controls the flaps on a 777 fails. You hear about it on the news.

Re:being a paying customer...

[Anonymous Coward]

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

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.

Re:being a paying customer...

[Stone316]

I guess the point is, your doing extra work on behalf of the database that other RDBMS's provide. Its a complete and utter waste of your time to do transaction logging at the application level.. Its not like you will have to pay for Oracle or DB2.... Other OSS RDBM's provide you with that functionality.

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.

Utopia?

[rastakid]

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?

Re:Utopia?

[BrynM]

If they can fix 10 years of criticiscm in one release...

Imagine the change log...

• Fixed bug causing unhandled exception under Win32
• Optimized internal table structure for better performance
• Silenced 10 years of criticism and name calling
• Win32 installer now creates log file in....

Re:Utopia?

[kpharmer]

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

Re:Utopia?

[LurkerXXX]

I don't know much about cars, but I don't need that silly pedal on the left in my car. When I want to slow down, I just pull on the handle of the emergency brake.

(Just because you are uninformed and don't know what other things are, doesn't make what you are doing 'right' and that they shouldn't be done better.)

Meanwhile, back in 3.23.x land...

[polyhue]

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.

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

[drspliff]

Thats because a lot of people who 'run web hosting companies' know jack about administration, security and what their users really want, and are just jumping on the internet business bandwaggon.

It's sad to say, but now with products like Helm, Ensim Webppliance etc. anybody can run a hosting business.

Anyway, to get to my point (and the end of a rant), because there are a lot of webhosts detatched from 'all that funny dos stuff at the backend' upgrading to newer versions of services etc. can be non-trivial if there isn't a button on their control panel to do it.

If you have any spare time and want to see for yourself, go traul through the CPanel message boards for threads you'd expect from a unix green-horn, but are actually coming from admins of supposidly 'reliable and respected' hosting companies.

While it will be a few months into the mysql 5 general release before you start seeing it being used in the hosting industry (I know several people who are just switching over from 4.0 to 4.1), the benifits for developers will be great (in the long run).

I expect to see a new batch of commercial and open source development spured by the growing stability of mysql 5, but also an increase of help requests from newcomers to web development.

Btw: I think there should be a new moderator option.. Rant -2

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

[spectrm]

So those users will be seeing this version in... 2008 maybe. 2012. Right after Longhorn comes out. I think you're being a little too optimistic about Longhorn

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:Meanwhile, back in 3.23.x land...

[amram9999]

If something isn't broke, why break it by upgrading? MySQL is constantly changing little details about how their DB works... take a look at a random page in their documentation and you will see notes like "in 4.x.x we changed this" all over the place. 5.0 sounds like it will be a very feature rich database (Yay, triggers!) but it will be a probably be a pain to upgrade. Little quirks that I've relied upon will no longer work that way. Maybe after this release, things will start to stabilize. Doubtful though.

MySQL

[Scoria]

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!

Re:MySQL

[DrWhizBang]

As a MySQL user, I believe he is entitled to troll. I use MySQL myself, and I can confirm it is indeed as trollworthy as it is useful.

Re:MySQL

[wootest]

Self critisism is good. Even I - ever the daft prick - know that.

What about foreign keys?

[eibon]

TFA doesn't mention anything about foreign key relationships, but it sounds more than a little weird to implement views, triggers and stored procs before FKs. Anyone know?

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:What about foreign keys?

[LurkerXXX]

Yea, they have foreign keys! WhooHoo! Unfortunately, they didn't bother implementing them correctly.

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

Sounds like troube brewing

[instantkarma1]

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.

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.

Re:Sounds like troube brewing

[Osty]

the MySQL approach is to add things one at a time, making sure that everything works right from the beginning. It's possible that the endless sniping has caused the MySQL team to abandon its careful development process, but I don't see any evidence of that so far.

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.

Re:Sounds like troube brewing

[Anonymous Coward]

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.

You got it all backwards.

PostgreSQL is about correctness first and performance second. Its speed lagged a little behind MySQL in the early years, but now performs as well or better than MySQL (at least if you want ACID-compliance from MySQL).

MySQL otoh wasn't even a real database, merely a storage API with an SQL interface. Everything and the kitchen sink, as you say it, are required for most real work, and I'd much rather go for a product with these features designed correctly from the start than one which is adding them on top in some ad-hoc fashion..

Other DBs

[drivinghighway61]

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.

Comes with a price

[highcon]

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.

"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

Who's still using mysql?

[grahamsz]

Seems like sqlite or hsqldb make more sense on the low end and as always there are better (though often more expensive) options on the high end.

It's great for prototyping things, but i just can't imagine running something critical on it.

Re:Who's still using mysql?

[CDarklock]

I've built three enterprise-class web systems on MySQL and I'm in the middle of building a fourth.

In the early days, it was hard to work around the lack of stored procedures, triggers, and views... I was coming from an Informix background with some Oracle experience... but nobody I worked for wanted to license one of those for web stuff at the time. And now that MySQL is about to add the stuff I used to wish for, I actually don't really need it -- I've long ago gotten over the lack of these features.

So I'm primarily going to use the 5.0 release as an excuse to demand a week or two of feature-freeze for hack cleanup, under the guise of migration testing.

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

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

Most important evar!!

[Dachannien]

How do you copy and paste verbatim from the linked article, but then spell 'criticism' wrong?

Bugfest

[Doc Ruby]

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.

Re:Bugfest

[DogDude]

I agree. Especially with something as critical as database software, I can tell ya' that if the final version comes out, say, this summer, that I won't even consider looking at MYSQL for another few years.

values

[maxjenius22]

So it doesn't randomly pick a value when the user tries to insert something invalid???

Why would I switch from PostgreSQL now?

Re:values

[maxjenius22]

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?

Everything in one release?

[bovinewasteproduct]

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

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.

Real DB?

[sulli]

It's like it'll be a real DB!

Then what will Slash use?

Re:Real DB?

[wootest]

Post-it notes on a big wall, rearranged by monkeys.

Re:Real DB?

[multipartmixed]

Why, version 3.2, of course -- to match the version of HTML they are almost confirming to.

Re:Real DB?

[ArsonSmith]

Hopfully a stored procedure "Find Dupes"

get over it already

[jbellis]

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.

Re:get over it already

[Anonymous Coward]

This is a guy who maintains that ACID isn't really necessary for a database (see first post in thread). Sure, he processed 203 q/sec. Now, how much of that data is actually there and correctly stored? Your guess is as good as anyones. Frankly, if you think !ACID == DB you need your fucking head examined and probably a pink slip to boot.

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:get over it already

[IANAAC]

PostgreSQL has a history of difficult installations -- ...

Fair game (well, was fair game, anyway). Now consider this:

If you are converting from another commercial database to either MySQL or PostgreSQL, which do you think will be the more difficult install? My bet is you'll be weeping with MySQL.

It's easy to say MySQL is an easier install when there's not data or application code. PostgreSQL becomes much more viable once you already have working code.

Wake me up when it goes to production.

[blcamp]

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.

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.

At what cost?

[Jhan]

So now MySQL is a "real DBE". Does that mean this new version is no longer 5-10 times faster than the "real DBE" (Informix) that we abandoned for the one reason that MySQL has extreme performance?

I do hope all these new features are either off by default or easily turned off.

Firebird more popular than Postgresql?

[russx2]

From the article:

"It [MySQL] accounted for 40 percent of open source database deployments, while Firebird and PostgreSQL accounted for 39 percent and 11 percent of deployments respectively."

Are these stats really true? Despite being a firebird user myself, I'd always assumed postgresql was a much bigger, more widely used product.

Unless of course the author is including *all* databases based on the Interbase code in that percentage?

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

Core feature or table type feature?

[aweiland]

Some of the people making comments haven't really hit on one of interesting things in MySQL, the ability to use different table types.

For instance you can't do transactions with MyISAM, but you can with InnoDB. You pick what you need.

I think the question that needs to be asked is where in the mysql engine will these features be added? Into the core? Into the MyISAM tables? In a new table type (i.e. MyISAM2)?

well

[Sv-Manowar]

this is great news for the open source database community, but i can't help but wonder if we shouldn't be more interested in PostGreSql instead, given it's more open license terms

MySql a.b. have previously shown their willingness to change their license terms and this has manifested itself in issues with projects like PHP who notably have changed to pushing SQLite more heavily with PHP5.

Re:well

[oliverthered]

I think MySql should stick at what it's good at and not try to do something it isn't good at.

Joe sixpack isn't going to use Oracle 9i for his blog, and your bank will never use MySQL for financial transactions.

If anything MySQL should be cutting 'junk' so that it leans more on the SQLLite side of things instead of the PostGreSql side.

The biggest problem is not technical

[scorp1us]

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

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:The biggest problem is not technical

[MemoryDragon]

Add to that, table inheritance, point in time recovery in postgresql...

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.

Paying the Ferryman

[sabat]

And pay your $599 SCO license fee, you lazy slack-offs!

They just pulled an all-nighter, dummy!

[solomonrex]

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

this is advertorial

[SethJohnson]

This blurb on Slashdot is an advertorial. There's absolutely no real meat to the ZDNet article. It's got one quote from a guy at mySQL mentioning three new features. The older slashdot story pointing to the changelog at mySQL has way more information than this ZDnet piece. And it doesn't conveniently feature a big banner ad for SUN hardware.

It was submitted from an anonymous reader. I am betting that anonymous reader is a sales guy at ZDnet looking to boost hit reports for their Sun banner ads. "I'll call those dorks at slashdot and pay them to link to a little pseudo story about mySQL."

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:InnoDB is ACID-compliant.

[pHDNgell]

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:

Isn't that what wikipedia was running when they had their database corruption due to a power outage that caused them to have to replay the whole thing from backups (minus whatever transactions were lost)?

So it gives you transactions, possibly consistency. It doesn't seem to provide durability.

Short of a block on a hard drive no longer being readable, I can't see how a power outage should make an ACID compliant database corrupt.

Sarcasm not accepted around here

[ari_j]

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.

NOW IT'S READY FOR THE ENTERPRISE!!

[defile]

Copied from my blog^Wweb based journal.

My retort to a mailing list flamewar over the enterprise readiness of MySQL vs. Oracle vs. PostgreSQL vs. tin cans and string .

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.

Have they fixed the language incompatibilities?

[mellon]

One of the things that's a real problem for me with MySQL is the places where MySQL doesn't follow the SQL language standard. This means that MySQL scripts typically only run against MySQL. This is probably just ignorance on my part - perhaps they fixed this long ago, and people are just coding to the old standard - but does anybody know anything about this? I wasn't able to find anything about it in the press release.

Fixing 10 years of criticism, 10 years too late

[Osty]

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

Re:Fixing 10 years of criticism, 10 years too late

[reverendslappy]

Amen! I've posted my criticisms of MySQL here before, only to be taken to task by the unwashed masses of MySQL "admins".

The reality of the matter (that you articulated quite eloquently) is that MySQL has never been a true RDBMS due to it's lack of features. The aspect you mentioned of MySQL encouraging poor development habits is one that I hadn't thought to mention, though I deal with it on a daily basis.

Far too often, I find myself assigned to clean up after several developers in another department here who create disasters of database-driven crapplications on MySQL without following the most basic tenets of database design (referential integrity, normalization, etc... If I had a dollar for every time I've come across one of these guys' big, honking, 74-column tables, followed by someone asking me "Why are there so many 'dupes' in this table?"...<deep breath>).

For the longest time, I couldn't figure it out. Were they lazy? No, I had personally witnessed their work-ethic -- which was solid -- and they always got their deliverables in on time, spending lots of extra time in the office (there's a reason for that, of course...). Were they incompetent or unskilled or just dumb? No... All three of them always wrote very stable, elegant (albeit copious) application code. Although, with the shitty DBs they created, I guess they had to write a lot of application code -- lots and lots and lots of it -- to just make their stuff work. Anyway...

Eventually, a position in my department opened up, and my one-table friends decided to apply. In my interviews with them, I discovered that before working here (where they're occasionally exposed to Oracle, Sybase, and SQL Server), they had experience with only one database platform. Take a guess what it was. Think hard...

Needless to say, they didn't get the job (although I now have two of them writing application code ONLY... and they're pretty f'ing good, interestingly enough). Obviously, they're an extreme example, and I can't fairly attribute their shortcomings solely to using MySQL. But it certainly didn't help (I mean why bother creating a normalized schema when you can't ensure referential integrity at the DB level anyway, right?). Sure, they could have found a way to learn the skills somehow or they could have been more curious or driven to learn, and maybe they just aren't interested in DB stuff and maybe they shouldn't be being asked to do it, I dunno. But after seeing their resumes, I haven't hired one person -- not a single one -- who doesn't have database experience outside of MySQL. I just can't see a MySQL-only person fitting into a Oracle or Sybase or, heck, SQL Server world (yet, I guess).

Anyway, rambling aside, I think the moral of the story is that using robust tools lends itself to building robust skills, particularly in the realm of RDBMSs. MySQL isn't evil and it isn't a bad little database platform for happy little applications, and I'm sure a ton of now very-skilled DBAs and developers might have gotten started using it. But a true RDBMS it has not been, for ten freakin' years. And more importantly, MySQL skills do not real DBAs make.

Anyway, if it is in fact now a competitive product, I guess my collegues and I will have to stop jokingly calling it "Access for Programmers". But if it stacks up and (FINALLY) does what a lot of us need a DB to do, with the speed and stability it's already known for, I'll throw it behind one of my apps tomorrow. I've got my fingers crossed, but after 10 years of persistent let-downs, don't blame me for not holding my breath.

Re:Fixing 10 years of criticism, 10 years too late

[Osty]

i think your problem boils down to the fact you want the DB to do everything when really it doesn't matter. the application is there and the DB NEEDS IT cause no user sits down and writes SQL statements.

Right and wrong. Yes, my problem is that I want the DB to do what DBs do -- enforce referential integrity, ACID compliance, encapsulate business logic where it makes sense (near the data), etc. You're wrong about it not really mattering, though. Ask yourself, with 20+ years of research into relational database design, why would you think that you can provide better referential integrity in your application than a proper RDBMS? How could you possibly enforce ACID behavior efficiently from your application? Why should you have to business logic in your application when it could be stored near the data where a change would not require a new build of your application? The DB needs the application to provide the user a useful view of the data in the database. It should not need the application to make sure that data is in a consistent state. Remember, more than one application may be using the same database (including applications you didn't write, such as management tools like phpMyAdmin). Unless you replicate the same integrity logic in all of your applications (and pray you don't have any bugs!), you will eventually run into problems where someone else has screwed up your data.

if you were a competent programmer, doing "DB" things in your "application" wouldn't be stupid, it'd be easy, cause you'd know your data structures and your sorting algorithms like any CS grad.

Ah, the personal attack. Nice. Why not take it one step further and write your own storage mechanism? By re-implementing RDBMS features in your own application, you've relegated the database to nothing more than a fancy, searchable file system. If that's all you need, fine, but that means you don't need a RDBMS at all and you shouldn't pretend that what you're using is one. I know my data structures and sorting algorithms, but that's not going to help me enforce data and referential integrity in my data.

and meanwhile, the user could care less about your DB triggers as long as the damn application works. they probably don't even know there is a database back there. and what the user doesn't know, doesn't matter.

That's all true, up until the point where your database craps out on you (you've seen it happen to Slashdot frequently, Penny Arcade, and pretty much every MySQL-backed website out there). Then it's painfully obvious to the user. However, this isn't about the end user. It's about the programmer, and using the proper tool for the job. There's absolutely no reason you should have to replicate the functions of an RDBMS in each and every one of your applications, and if you find yourself doing so it's pretty clear that you're using the wrong tool.

Think about it. Why would you enforce referential integrity from your application? Why should have to write special "rollback" code (consisting of deleting what you just inserted in the simple case, or much worse for update scenarios) in your application? Why should you have to pull down more data than you absolutely need? There's a reason these concepts are integral to relational database design, and it's hilarious to think you can implement them better in your shopping cart web app than someone who's dedicated to building relational databases. (Hey, maybe you can, but you shouldn't have to)

Re:Fixing 10 years of criticism, 10 years too late

[quantum bit]

i think your problem boils down to the fact you want the DB to do everything when really it doesn't matter. the application is there and the DB NEEDS IT cause no user sits down and writes SQL statements. if you were a competent programmer, doing "DB" things in your "application" wouldn't be stupid, it'd be easy, cause you'd know your data structures and your sorting algorithms like any CS grad.

It's not that simple in the real world. Think about things like concurrency. If you have 200 clients running your app at the same time and 10 of them are trying to update a complex set of related tables, how do they avoid stepping on each others toes? Are you going to have your client apps communicate with each other and coordinate the inserts? Or just lock all the tables and kill performance? How do you ensure that everyone who is reading those tables sees a consistent data set?

Or you could let the DB server do it, since it knows both the data model and the state of all the clients. Things like transactions keep clients from seeing unfinished inserts (and PostgreSQL and Oracle have MVCC to make this very efficient).

Sure, for websites and blogs it doesn't really make a difference. Every once in a while you'll see some odd data appearing on the page but if you refresh it will be gone. MySQL has historically been very good at running simple things like this.

Anything more important than that needs real data integrity at the database level. Application level checks can't *guarantee* the state of the database when more than 1 user is connected, no matter how careful they are.

Question

[The Spoonman]

What about bi-directional replication? I know, t'ain't easy, but is it easier now?

how did this get modded insightful?

[jbellis]

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

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.

OK, how about java stored procedures?

[hey!]

This can be done in Oracle and I believe Postgres. Not only do they perform better than native stored procedure languages, they potentially allow you to take code that benefits from being in the database tier and make it portable.

So?

[DogDude]

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.

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.

This isn't just "customer base changed"

[jbellis]

I guess you don't know the history here like the grandparent does. MySQL really promulgated a lot of disinformation.

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:

There are so many problems with FOREIGN KEYs that we don't know where to start:

• Foreign keys make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied and removed.
• The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY checks are useless because you usually insert records in the right tables in the right order, anyway.
• There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.
• You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup).
• If you have foreign keys you can't dump and restore tables unless you do so in a very specific order.
• It's very easy to do ``allowed'' circular definitions that make the tables impossible to recreate each table with a single create statement, even if the definition works and is usable.

The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons.

Re:This isn't just "customer base changed"

[dcam]

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.

Here's a step-stool

[spun]

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.

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.

Now that is precision

[dingbatdr]

You know your level of confidence to four significant digits? Impressive!

LOL

[Zebra_X]

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.

postgresql 6.x is long gone...

[jbellis]

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

Re:Replication? Clustering?

[ShieldW0lf]

Thats a pretty harsh comment.I think MySQL is useful in a lot of situations. Heck it's even used by Yahoo
Yahoo surely employs "database professionals"?

Yeah, you got it bud. I'm an HTML programmer, and I see this all the time from elietist guys that say HTML isn't a programming language.

Yahoo employs lots of programmers. They use HTML. So surely HTML is a programming language too! Up yours elietist guys!

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:Replication? Clustering?

[lantenon]

It's not a programming language.

It's a markup language.

http://www.cs.tut.fi/~jkorpela/prog.html

foreign keys? try write-ahead logging

[js7a]

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.

Opinions...

[danheskett]

I worked with a group of professionals to port an COM+/MSSQL project to PHP/MySQL3.x platform.

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?

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

[kpharmer]

> Is MySQL 5.x the answer to my prayers? Or just a cruel reminder of why MS software costs what it does?

Well, what's your time worth? what's the business worth that would be supported by this database? How much risk can you take?

Assuming that you've got money at stake here, your best bet would be to stay completely away from mysql at this point in time: you aren't seriously thinking of porting 1000 triggers and 800 stored procedures to mysql's initial beta release of this functionality are you?

Given your requirements, I can't imagine considering mysql or even postgresql right now. On the other hand, there might be scenarios in which you could begin to break up this database into components, and perhaps some of these components could live in other technologies.

For instance, can some of your reporting & read-only queries run against a separate database? This is a typical oltp / olap split in which transactions hit a small transactional database with minimal data retention, then the data is moved over to a separate database for reporting & analysis. The reporting database ('data warehouse', 'datamart',etc) typically uses a denormalized model to avoid so many joins, typically also uses parallelism & partitioning for far better performance. You'll miss db2, oracle, sybase, or informix here. Even sql server most likely.

The above wouldn't get you completely out of sql server, but is probably achievable. And would help to avoid trying to port thousands of triggers & procs.

MySQL - I love it!

[kilodelta]

We just upgraded to MySQL 4.1.1 - suits our needs. Fast, cheap and reliable.

I would love to ditch our one critical MS-SQL server and convert it to MySQL but that one is heavily dependent upon triggers and stored procedures for two databases. Until MySQL 5 is up to snuff we won't even entertain transitioning those two.

That being said, I note that the MySQL Migration Toolkit is very nice. Too bad you can't suck the data from an MS-SQL server yet. But give it time - someone will figure out how to do it.

But does it support WITH RECURSIVE?

[mark-t]

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.

Re:But does it support WITH RECURSIVE?

[mark-t]

No

Conformance

[XanC]

Why, version 3.2, of course -- to match the version of HTML they are almost confirming to.

More like to match the HTML they are not conforming to.

Re:foreign keys? try write-ahead logging

[Unordained]

... or Firebird with safe-write enabled. There's no appreciable start-up cost even if your db server lost power in the middle of a large transaction. It will reclaim "garbage" space during normal activity as it discovers areas that aren't in use because their transaction got rolled back by the power failure. Handy. (Firebird and PostgreSQL are extremely similar in terms of transactional capabilities; the big difference was SWEEP vs. VACUUM, which apparently Pg took care of in 8.0?)

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.

too late

[danharan]

From TFA:

MySQL is the most widely used open source database, according to a Evans Data Corporation survey released in January. It accounted for 40 percent of open source database deployments, while Firebird and PostgreSQL accounted for 39 percent and 11 percent of deployments respectively.

Since Firebird is expected to hit 2.0 sometime soon, I'd expect them to give MySQL some competition. And how can anyone on /. forget PG?

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.

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: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:Come on stop whining! MySQL is SUPER!

[LurkerXXX]

First: Transaction isolation and innodb can NOT solve these integrity problems.

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.

Re:foreign keys? try write-ahead logging

[Unordained]

Doesn't "Vacuum" now run without having to take the database offline? As I understand it, that was what was keeping Pg from being truly 24x7. "Sweep" in firebird runs as a background process and -might- slow down the server while it's running, but doesn't prevent anything from happening. I thought that's what Pg had switched to?

Let me guess

[gnovos]

It'll be released on Feburary 31st?

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.

How about fixing the ACID compliance?

[Daniel Phillips]

Without proper ACID compliance, everything else is decoration. The recent failure of one of the Wikipedia MySQL servers to start up again after a power failure proves beyond a reasonable doubt that MySQL is not ACID compliant.

(Was the corruption due to a disk lying about cache flush/disable? Easy, test the disk. If it doesn't lie, then MySQL was the culprit.)

Re:Come on stop whining! MySQL is SUPER!

[sapgau]

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

Re:Come on stop whining! MySQL is SUPER!

[jadavis]

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.

yes, and are you surprised?

[KZigurs]

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

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.