MySQL Moves to Prime Time

MagLev writes "MySQL, especially version 5.0, is popping up on the radar screens of database gurus who built their reputations and book sales using other SQL databases. Ken North, who did those ODBC performance benchmarks for Oracle, Sybase, and DB2, wrote a recent article about MySQL 5.0. The article profiles mission critical database software and discusses how well MySQL 5.0 fits the profile. It gives good marks to MySQL, except for Java and XML integration."

Comments

So, uh

[Anonymous Coward]

What time and what channel?

Re:So, uh

[Lenins_beard]

MSNBC of course

Interesting

[bulio]

The article looks pretty good, I haven't read all of it, but it seems to point out some strong points in mysql.

Re:Interesting

[bulio]

Thats great, same to you too :-)

Re:Interesting

[bulio]

In fact, I really really think you should read it :)

Re:Interesting

[Haydn Fenton]

That, sir, is one of the most pathetic attempts at an above-0-scoring first post I have ever seen. Did you get that out of a Learn First Posts in 21 Days textbook?

Chapter 3. Ambiguous Comments
As discussed in the previous chapters, gaining a first post can be a difficult task in itself, let alone scoring something above -1, Offtopic. For this reason, it is often advisable to post something relevant to make sure your first post is visible to all who read the comments. This can lead to several problems, namely having to actually read the arti... er.. I mean, having to type a relevant comment quickly enough to beat another first-poster. For this very reason, we have provided a list of easy to use copy and paste ambiguous comments that will slide past your average moderator. Below is a short list of comments, guaranteed to keep your FP above 0. Write them down, learn them off by heart, and most importantly, keep them in your clipboard or in a notepad window so you're ready to paste and post as soon as your NST (New Story Checker© - Provided on CD) alerts you to a new post.

• The article looks pretty good, I haven't read all of it, but it seems to point out some some good points about the product.
• I am pleasantly surprised at the developments made in this area in the past few years, looks like before we know it we'll have flying cars, flying toasters and Duke Nukem Forever.
• Looks like the server is getting sluggish already, why don't the editors just use coral links instead?
• Oh come on, this is just getting stupid, how many more slashvertisements am I going to see today?
• This story is a duplicate, it was posted around 2 months ago, does anybody have the link?

I'm sure you can think of many more.

In the next chapters we will cover the popular slashdot jokes guaranteed to get you those 5, Funny scores to impress your friends, and the best way to change your signatures to GNAA related text once your posts reach 5.

Re:Interesting

[Anonymous Coward]

On Slashdot +5 funny scores YOU.

Re:Interesting

[Anonymous Coward]

Am I too late to jump in on this thread? Oh, good.

Fuck cunt fuck cunt fuck cunt fuck cunt fuck cunt fuck cunt fuck cunt fuck cunt

Really?

[Sheetrock]

I might have to check it out then -- thanks for the info.

Slowly But Surely

[oirtemed]

OSS can compete with commercial offerings, it just usually takes more time to mature. Now, I'd expect to see a burgeoning market for MySQL support companies or companies offering database services and supports based on customized OSS MySQL sources. I use MySQL for websites and such, it's a great little database but I had no idea how much it scaled: MySQL has a demonstrated capacity for managing very large databases. Mytrix, Inc. maintains an extensive collection of Internet statistics in a one terabyte (1 TB) data warehouse that contains 20 billion rows of data. Sabre Holdings runs the oldest and largest online travel reservation system. It replicates 10-60 gigabytes per day from its master database to a MySQL server farm. The MySQL databases are used to support a shopping application that can accommodate a million fare changes per day.

Re:Slowly But Surely

[Sheetrock]

A little ironic, but I think we've got Slashdot to thank for that.

I suppose that's no surprise -- if you're the largest forum on the net it's no surprise you're bound to push the development of Perl and MySQL.

Re:Slowly But Surely

[Anonymous Coward]

Hehehe. Bullshit. Pure bullshit.

Re:Slowly But Surely

[figleaf]

There are much larger non-tech forums on the net.
For example: http://fray.slate.msn.com/?id=3936 has more users and more posts than this forum.

Re:Slowly But Surely

[Anonymous Coward]

> There are much larger non-tech forums on the net.
> For example: http://fray.slate.msn.com/?id=3936 [msn.com] has more users and more posts than this forum.

I just tried it -- it takes forever to even get the page up. How many people have the patience for such slow forums???

Re:Slowly But Surely

[Anonymous Coward]

When I worked on Sabre front and back ends over 5 yrs ago they had Oracle under the hood. They may have changed now but I can't see going from Oracle to mySQL for a company who's core business is the data.

Re:Slowly But Surely

[v01d]

Strange. Seems faster than Slashdot.

Gosh

[Mateo_LeFou]

Might we see an end -- or at least pause -- in the constant "MySQL sucks"-oriented comments/blog entries/ etc?

Re:Gosh

[Anonymous Coward]

Not until MySQL doesn't suck (by which I mean getting ACID compliance, a real security model, real transactions, sane syntax for adding users and granting privileges, and better stored procedures).

That it can be replicated should come after ACID compliance in priorities, or you're just replicating wrong data.

Re:Gosh

[Anonymous Coward]

Hmm, it's too quiet here you know. A massive postgres troll attack must be imminent.

Re:Gosh

[Anonymous Coward]

Not until posting comments on a high-load website that uses MySQL, like Slashdot, doesn't sometimes result in complete garbage (e.g. the other day I posted a comment, and on the comment posted page, my comment appeared with somebody else's sig attached, and then it disappeared from the article altogether, never to return).

Re:Gosh

[digidave]

That's retarded. The database shouldn't try to fix buggy code.

Re:Gosh

[macaulay805]

... ... . ... ..... MySQL SUCKS!!1!!one!!11

(PS: Damn Lameness filter is ruining my joke)

Re:Gosh

[timmarhy]

no, the database should maintain the integrity of it's data reguardless of buggy code. thats the problem with mysql, it doesn't protect your data.

Re:Gosh

[Osty]

That's retarded. The database shouldn't try to fix buggy code.

You're absolutely right. The database should fail to accept bad data. Integrity doesn't mean that the database tries to make an educated guess about what you meant (MySQL does this, and its one of the many reasons why MySQL sucks). It means that the database isn't going to let you insert data if it's not correct. Take the simple case of a foreign key. Your application may be buggy and try to insert a value into a column with a foreign key constraint that doesn't exist in the referenced table. The database should fail your insert, not try to find the closest match to the value you were asking for, or let you insert the value anyway even though it violates the constraint.

The people that defend MySQL's lack of features by saying that you can fix all of the problems in application code obviously do not know what they're doing when it comes to databases. They may truly be smart people, but as soon as they spout such nonsense you can be sure that any DBA that hears it will never take them seriously again.

Re:Gosh

[Anonymous Coward]

How do you know that that was a database problem?

Re:Gosh

[Michalson]

It's not that it sucks, it's that it just doesn't stack up.

The added features of MySQL 5, if put into the context of the auto industry, would be like a car manufacturer announcing that some of their 2005 models would now come with airbags and anti-lock breaks. Yes, it shows improvement, and yes, it may plug some longstanding criticisms, but in the larger picture it still means that company is years behind everyone else.

MySQL 5 would have been a great advancement to put it in serious technological competition with other databases...if it had been released in 1999 or 2000. The reality is that Postgre is in version 8 with serious Windows support, Oracle is at 10g with gobs of new features 1% of DBAs will use, and Microsoft is in the process of unleashing a major new version of SQL Server onto a world that has done it no wrong. MySQL has only managed to catchup to where the industry was 5 years ago. Everyone else has kept moving.

Real DBA's don't like MySQL for the same reason real web developers don't like IE. They're both behind the times, fail to live up to standards (CSS/ACID), and only got to where they are because of aggressive bundling. IE is "popular" because it's preinstalled and thus used by the average joe who doesn't know any other "internet". MySQL has made sure it is sitting on every free and cheap LAxP host out there, resulting in droves of kiddie web developers whose experience involves a few web tutorial on PHP and MySQL being locked into its heavily proprietary interfaces and dodgy "optimizations".

Re:Gosh

[Anonymous Coward]

It smells like a database problem, specifically, the lack of transactions. Where else would the problem occur? The comment submissions go through Apache 1.x (check the headers), which means each request is in its own process - no chance of multithreading screwing things up. At some point, two processes will have attempted to submit data simultaneously, and the lack of transactions meant that they both wrote to the same record, thinking it was a new one. See, when you are testing or have a light load, you don't pick up these things because the chances of the timing being precisely wrong when two people are submitting at the same time is miniscule - only larger sites like Slashdot demonstrate these problems. The database is meant to enforce data integrity, except it obviously isn't on Slashdot.

Re:Gosh

[superpulpsicle]

There is a decent sized market out there where organizations don't need a complicated schema or fancy features. I have seen places where MySQL is heavily preferred due to speed and liteness.

They just want to do your average query on a fairly large db, but do it fast, hella fast. They'd rather put MySQL on a fast proprietory filesystem. Stripe and load balance off some fast storage arrays. And just blast away.

Re:Gosh

[temojen]

But there is no marker out there that does not need ACID compliance, just those who don't know they need it.

Re:Gosh

[Cylix]

Thank you...

I'm not even a serious DBA and I know the problems.

I use postgres off and on when I need DB applications. Sometimes an app balances between a bdb hash file and then there are some that do need that extra umph.

Still, that said... there are so many extra little things I had to do when writing code for mysql. Try postgresql and see how much time it saves you. (assumming you don't write your code just like you do for mysql). I haven't touched mysql in a long time and maybe it has changed A LOT.

Anyhow, MySQL has a footprint... can't argue that... but comon folks... try something else.

Re:Gosh

[consumer]

Fails to live up to ACID? MySQL has had ACID transactions for years now. If you didn't know this, you have no place commenting on MySQL at all. It has the same sort of MVCC transaction and locking support that PostgreSQL does, and has since version 3.23.

Re:Gosh

[ThaFooz]

You're absolutely right. The database should fail to accept bad data. Integrity doesn't mean that the database tries to make an educated guess about what you meant (MySQL does this, and its one of the many reasons why MySQL sucks)

Agreed, MySQL's data truncation has been a major problem and prevented it from being deployed on critical apps. However, as of 4.1 most of the connectors out there can convert the truncation warning into an exception, and 5.0 has server modes of varying strictness and SQL standard compliance to solve the problem.

I'm hardly a MySQL fanboy, but to say it 'sucks' is probably an overstatement. There are plenty of operations out there that don't need the features or can't justify the cost of the proprietary systems (nevermind the platform lock-in of MSSQL). Which really just leaves Postgres and MySQL - kind of a wash IMHO. Historicaly it was a few more features & better transaction support vs. lightweight/larger community/easy to configure - but the two are rapidly converging.

Re:Gosh

[Anonymous Coward]

The added features of MySQL 5, if put into the context of the auto industry, would be like a car manufacturer announcing that some of their 2005 models would now come with airbags and anti-lock breaks. Yes, it shows improvement, and yes, it may plug some longstanding criticisms, but in the larger picture it still means that company is years behind everyone else.

And there are thousands of fatal accidents every year due to cars, and many due to massive SUVs crushing smaller more efficient cars. All of this could be avoided if people just walked or rode a bicycle or some other form of transportation.

Get it? Just because "everybody else" is driving a big SUV or using a $50k database for their applications doesn't mean YOU need to. There would be a lot more apps delivered on-time and on-budget if people used the right tool for the right job.

Besides, Oracle is less like an SUV and more like a $100k train that requires its own staff of engineers and coal shovelers.

Re:Gosh

[Anonymous Coward]

Might we see an end -- or at least pause -- in the constant "MySQL sucks"-oriented comments/blog entries/ etc?

Even if you believe that MySQL doesn't suck, there is just no question that there are better options. PostgreSQL is better in every conceivable way, for example.

Re:Gosh

[Anonymous Coward]

If you don't know the difference between "brake" and "break" then I have a hard time taking your comments seriously. What has happened to proper spelling and grammar?

Re:Gosh

[Anonymous Coward]

There are plenty of operations out there that don't need the features

Since when is data integrity an optional feature for databases?

Re:Gosh

[Anonymous Coward]

Funny.. I'm a "Real" DBA and a "Real" web developer with over a decade of experience. My browser of choice is IE (For one, webpages look better in IE and 2, 98% of the world uses IE.. why bother developing for the infinitesimal small percentage of users who use something like Firefox or Opera or other obscure teeny-tiny players (nobody but the whiny, bitchy few)?), my software language of choice is PHP (I used to be a Microsoft/Windows guy before I learned Perl and PHP. Perl is fabulous but PHP is more catered to quick and easy yet versatile web development. No need to get messy with C/C++, and never had a reason to even bother learning bloated/ugly Java--I'm surprised Sun is still around), and my database of choice is MySQL. MySQL is fast and does the job. No need to spend a week setting up a beast like Oracle, and the support is fabulous.

Re:Gosh

[kpharmer]

> There is a decent sized market out there where organizations don't need a
> complicated schema or fancy features.

then they might want to check out sqllite: a simple and completely free database *without* bizarre integrity problems.

> They just want to do your average query on a fairly large db, but do it fast,
> hella fast. They'd rather put MySQL on a fast proprietory filesystem. Stripe
> and load balance off some fast storage arrays. And just blast away.

if they're blasting away with mysql, then they aren't doing much with the data:
  - no parallel query capability
  - no memory tuning
  - no partitioning
  - no optimizer sophistication

In short, unless you've got extremely simple queries looking up small sets of rows - mysql is slow as a pig, and can't compete with the commercial products. Again, if you *know* what you're doing.

And assuming that you're interested in data integrity and are using the innodb database, then postgresql is just as fast. Possibly *much* faster if you're writing moderately complex queries with 5 or more tables.

The idea that mysql is fast is a myth that came from php kids playing with a database for the first time. Once you actually compare the products available today mysql has nothing going for it - except quite a lot of inexperienced fans. Which, I have to admit, is probably worth quite a bit.

Re:Gosh

[Osty]

However, as of 4.1 most of the connectors out there can convert the truncation warning into an exception,

Wait a second. If that's a warning, it means that it went ahead and did it and warned you that it did so. If a connector translates that into an exception, how do you roll back what MySQL already did? Besides, since when is it the domain of the connection software to handle referential integrity constraints?

5.0 has server modes of varying strictness and SQL standard compliance to solve the problem.

What's the default, and why would I trust them? MySQL has already proven they don't care what you (the developer) want by doing stupid stuff like trying to set defaults when you don't want them, silently ignoring commands (like when you try to create an InnoDB table on a deployment of MySQL without InnoDB), etc. Given that, my assumption would be that the default of MySQL is not the stricter modes, and even if I did choose them they wouldn't be as strict as you would expect.

I'm hardly a MySQL fanboy, but to say it 'sucks' is probably an overstatement.

I'd call it a generous understatement, personally.

There are plenty of operations out there that don't need the features or can't justify the cost of the proprietary systems

Data and referential integrity is not a "feature", but a requirement (if it's not a requirement for you, you don't need a RDBMS). Other features may not be that important, but they're still nice to have for the one or two times you need them. PostgreSQL is free if price is a concern, and there are plenty of other free DBMSs with varying levels of functionality out there.

Which really just leaves Postgres and MySQL - kind of a wash IMHO.

Really? Postgres -- fully functional, powerful RDBMS that routinely competes with the Big Boys in terms of speed and features, but has a funky maintenance system (vacuum) and doesn't run natively on Windows. MySQL -- toy database propelled to stardom by open source fanaticism, notoriously unstable, its vaunted speed only applies to relatively small, simple datasets (let's hope you're not doing something crazy like a join!), with arrogant developers who tell their users that they don't need certain features ... right up until the point that they implement the feature and then pretend they never bad mouthed it (yes, that's right, the developers did say that you didn't need row-level locking and transactions because you could do everything you need with a table lock and some programmer "smarts"). Sounds like a wash to me.

Historicaly it was a few more features & better transaction support vs. lightweight/larger community/easy to configure - but the two are rapidly converging.

In comparison to Oracle, Postgres is trivially easy to configure. It also has a very large (but not as vocal as MySQL's) community, and it's not very heavyweight. If anything, MySQL is slowly converging towards Postgres, but it's doing so in a distinctly MySQL sort of way -- deny, deny, deny right up to the point you implement the feature. Because foreign keys will make you slow!

Re:Gosh

[Not The Real Me]

that uses MySQL, like Slashdot, doesn't sometimes result in complete garbage (e.g. the other day I posted a comment, and on the comment posted page, my comment appeared with somebody else's sig attached

Wheew!!! I'm glad you're blaming MySQL. I'd hate to see you suggest server environment variables, connection pooling, session variables, mod_perl, or /dev/shm (not that anyone uses that anywhere on /.).

Re:Gosh

[Anonymous Coward]

Let's take these one at a time...

Server environment variables: WTF are you talking about? Why on earth would a comment be stored in them, and how could the environment from one request corrupt data in another request? Remember we're talking Apache 1.3 here - no threads in sight.

Connection pooling: the only possibility I can see apart from what I said.

Session variables: why on earth would a comment be stored in the session? It has one purpose and one purpose only - to get stored in the database. Furthermore, why would somebody else's session data end up in my session?

mod_perl: again, we aren't talking about a threaded environment, my request is executed in a different process to other requests.

Re:Gosh

[Anonymous Coward]

I've never heard of mysql's auto_increment screwing up, short of a corrupted database. I'm not going to defend mysql, but slashcode is a piece of shit and the janitors have a habit of throwing buggy code into production.

Re:Gosh

[lysergic.acid]

What standard commercial features are MySQL missing that other databases have? How is MySQL behind the times?

I'm just curious because despite how long of a post you've made, you're claims against MySQL are awfully vague. It'd be nice to know some specifics as to why MySQL wouldn't hold up against other database servers in most heavy applications.

Re:Gosh

[Anonymous Coward]

>> The idea that mysql is fast is a myth that came from php kids playing with a database for the first time. Once you actually compare the products available today mysql has nothing going for it - except quite a lot of inexperienced fans.

The article mentioned at the top of this thread had links in the left column to case studies. The Sabre link takes you to an EDS case study. EDS is a computer services company that does about $20 billion per year

• not exactly inexperienced.

There was an article several months ago that discussed why Sabre chose MySQL over postgreSQL. When I tried a Google search to find it, this presentation by a Sabre VP popped up. It explains the application and the server network:
http://www.ctug.ca/English/presentations/20040414% 20Healey.pdf

Re:Gosh

[Anonymous Coward]

Check here for starters: http://sql-info.de/mysql/gotchas.html

Yes, I know it says "Note to Slashdot readers: this page deals with issues related to MySQL 4.1 and earlier, not 5.0". But how is this different from Microsoft saying "Windows now really is getting secure, promise!"

I wouldn't feel comfortable using a product whose developers made glaring mistakes or just didn't care in the past, no matter if they "got it together" now.

And it still doesn't invalidate the point of the GP that MySQL is just recently catching up and now you ask how it is behind the times. There are simply other databases that did it right the first time and have earned their trust.

Re:Gosh

[Anonymous Coward]

This is EXACLTY why the rest of the world doesn't trust the USA.

Re:Gosh

[Anonymous Coward]

Wow. Just... wow.

The past week or so I've been learning PHP and SQL (web tutorials of course, why the fuck would I buy a book on it), and working on my site (which is hosted on my PC, using apache).

Here I thought I've been actually learning something, and growing an appriciation for web developers (and developers in general), but according to you, I'm just a kiddie? Fuck you. It's people like you who drive away "normal" people from actually being interested in things like this. As for mySQL, yes I used it because it was a)free and b)HOLY SHIT BATMAN, easy to use. I may have problems with it sometimes in the way it does things, but for most people, it works.

Re:Gosh

[dfetter]

Really? Postgres -- fully functional, powerful RDBMS that routinely competes with the Big Boys in terms of speed and features, but has a funky maintenance system (vacuum) and doesn't run natively on Windows.

Actually, autovacuum has been around for awhile, and the native Windows version of PostgreSQL started with 8.0 :)

Re:Gosh

[adtifyj]

And then, 10 years on, they find themselves in the same position as Sabre Holdings: convincing themselves they made they right decision to go with MySQL.

Re:Gosh

[LizardKing]

Here I thought I've been actually learning something

You may be learning the rudiments of SQL, relational databases and a web templating system, but you need to be aware of the limitations and quirks of MySQL. Too many people are leaving college having only used MySQL, and end up perpetuating the myth that MySQL is particularily fast in all but the simplest applications and that the missing features are somehow "unnecessary" in all circumstances. That last thing is what fucks me off about MySQL. For years Monty and co. said that foreign keys were a misfeature, implying that a couple of dodgy[1] coders at MySQL AB knew better than all the other companies and researchers who have decades of database analysis to back them up. When MySQL belatedly added foreign key support with the InnoDB table type, they suddenly stopped saying that database integrity should be handled at the application level. Cunts. Oh well, at least it keeps me in work, replacing shitty systems written by novices like you with something more robust and scalable.

[1] I say dodgy, because they release dubious changes at micro patch levels that break existing functionality.

Re:Gosh

[Jackmn]

The past week or so I've been learning PHP and SQL (web tutorials of course, why the fuck would I buy a book on it), and working on my site (which is hosted on my PC, using apache). Here I thought I've been actually learning something, and growing an appriciation for web developers (and developers in general), but according to you, I'm just a kiddie? Fuck you. It's people like you who drive away "normal" people from actually being interested in things like this. As for mySQL, yes I used it because it was a)free and b)HOLY SHIT BATMAN, easy to use. I may have problems with it sometimes in the way it does things, but for most people, it works.

It's just about as easy to set up, and PostgreSQL is even more free.

PHP is a pretty poor choice too. You may find that Ruby on Rails or Perl + Mason are better.

Re:Gosh

[stiggystiggy]

That's what people said about Linux in the 90's. Give it time. MySQL is a steamroller. Get out of the way or become part of the road!

Re:Gosh

[Anonymous Coward]

No, MySQL does not have a sophisticated MVCC.

Simply start two transactions, A and B, in A update a key field, and in B insert a row into the same table.

Watch as MySQL holds B until A is done.

I use MySQL 4.1 and it is only good for applications where simple selects are all that is needed. No advanced queries.

PostgreSQL and DB2 are what I use when I need to do real work rather than a simple WIKI or something.

Re:Gosh

[se7en11]

Agreed! Acid beats anything! Whoa - was that Snoopy I just saw? My parents have been using ACID since the 60's and they are doing ok. Well...minus a few flashbacks.

Re:Gosh

[archen]

(disclaimer: I'm a Postgresql zealot)

I agree, that saying MySQL is playing "catch up" is misleading. Mysql's goal has been to have a quick, open source database, that is fairly easy to use. They've accomplished that, and done fairly well for themselves. I think it's only pressure from Posgresql being close to enterprise class that has caused the userbase to pressure the MySQL team to even add these features.

Now not having stuff like (real) foreign keys seems crazy to me, but I could see how many people may not need them. MySQL has attained its initial goals, its just not exactly the same as the other database vendors goals.

Re:Gosh

[arkanes]

sqllite serves a totally different need than MySQL would. sqllite is a file-based DB, like Access, and is fantastic for embedding (far, far superior to "embedded MySQL", patooie), but is totally unsuited for a server role. And I hate MySQL and would recommend PostgreSQL anywhere someone is thinking about using MySQL.

Re:Gosh

[Anonymous Coward]

That's nice, but I do this as a hobby right now. Fuck, I can't even afford college yet. So the only people using my "shitty" systems are me at the moment. Cut me some slack, I didn't say MySQL was the end all be all. However, I don't like the attitude you holier-than-thou types give off to us newbies. Maybe the fact that I've never even heard of anything other than MySQL until today will give you an idea as to why I picked that first. I did go check out PostgreSQL because of this, though. Not exactly the best way to introduce people to it, but it worked this time. As for the drama going on company-wise, that doesn't matter to me right now.

Sheesh, and some of you wonder why the IT field is dying. Oh wait, no you don't -- you like having all the jobs to your money-grubbing selves. Good attitude. Asshole.

Re:Gosh

[arkanes]

The past week or so I've been learning PHP and SQL (web tutorials of course, why the fuck would I buy a book on it), and working on my site (which is hosted on my PC, using apache).

If this is the extent of your experience, then yes, you are a kiddie. You like MySQL primarily because you had a positive first experience with it and have no experience with alternatives, and secondarily because you've never needed to do professional caliber work with it. That makes you a kiddie.

Being a kiddie is not (neccesarily) a permanent state, and it does not preclude learning something and gaining an appreciation for web developers. However, it does pretty much mean that your opinion of MySQL isn't interesting or important.

The fact that you're profane and whiny in reponse to a perfectly reasonable description of your expertise and experience, that wasn't even personally directed at you, just kinda brings the point home. MySQL is enormously popular in this market.

Re:Gosh

[Anonymous Coward]

Well, yeah. I just have a different preception when it comes to the term "kiddie", something more akin to "I just downloaded PHPBB and installed it, I am so leet!", I see how you mean though, the term being used as sort of a newbie identifier, and in that respect, I most definently am.

Re:Gosh

[Anonymous Coward]

> EDS is a computer services company that does about $20 billion per year
> * not exactly inexperienced.

EDS is a disaster: used to only hire non-cs degreed graduates, then put them through training to become programmers, pay them poorly, burden them with crazy bureacracy, and worst of all - inflict Ross Perot's moral insanity with rules like having to wear white underwear, etc.

It has improved some, and does occasionally have some bright folks, but they screw up more than any just about anyone else (except maybe CSC). And they are the people who almost went backrupt on a massively-screwed-up navy contract a few years ago.

Take this project for instance: their "server farm" has how many servers in it? 10? 20?

I've got to tell you, maintaining 20 linux servers plus 20 mysql databases is never going to be cheaper than 2 oracle/db2 databases on HPUX. Think of all the admin labor that will go into patching, tuning, changes to the database, upgrades, etc. Ick.

Re:Gosh

[Anonymous Coward]

You may think that you are a DBA, but you are not.

Re:Gosh

[LizardKing]

Maybe the fact that I've never even heard of anything other than MySQL until today will give you an idea as to why I picked that first.

Maybe if you'd done some research before settling on MySQL you might have picked a better RDBMS. There again, given the sheer amount of MySQL books and apologists maybe not. As for "having all the jobs to your money-grubbing selves" I'd quite happily see more people coming into the industry with a modicum of knowledge. Rather than muppets like the bloke I dealt with today who considers an XML document valid if IE6 treats it as XML rather than plain text!

Re:Gosh

[Anonymous Coward]

You win. Very simply stated and 100% accurate.

Re:Gosh

[schon]

The added features of MySQL 5, if put into the context of the auto industry, would be like a car manufacturer announcing that some of their 2005 models would now come with airbags and anti-lock breaks.

Actually, it would be like a car manufacturer announcing that some of their 2005 models would come with electric starters and power steering, as options.

The "new" features are requirements for any RDMBS application. As someone else said, if you don't think integrity is a requirement, you don't need an RDMBS.

Re:Gosh

[Blkdeath]

Purely for interests sake; what sized DB do you administer, what type/frequency/size queries does it handle, and what DB software are you using presently? What are your (professional) experiences with other packages? How did they scale, and how much hardware is/was in use at the time?

Thanks!

Re:Gosh

[Anonymous Coward]

> Purely for interests sake; what sized DB do you administer at the moment:
- 3 TB data warehouse on 4-way p5 server with 8 gbytes of memory and 4 storage arrays
- 600 gbyte data mart on a 6-year-old 4-way rs/6000 with 8 gbytes of memory & 2 storage arrays
- 2x100 gbyte data marts on 6-year-old 4-way rs/6000s with 8 gybytes of memory & 2 storage arrays

> what type/frequency/size queries does it handle
- warehouse: handles about 600 loads a day
- warehouse: spends about 30 minutes a day summarizing data - many of these queries are
extremely complex (normlizing counts across different data sources, multi-step olap, etc)
- small data marts - around 60,000 queries a day - mostly hitting summary tables
- large data marts - around 400 queries a day - all hitting detail data

> and what DB software are you using presently?
- db2 8.2 udb - workstation edition (inexpensive) and enterprise edition (expensive)

> What are your (professional) experiences with other packages?
> How did they scale, and how much hardware is/was in use at the time?
- oracle 9i - on 2-way intel box with a single raid-5 storage array,
300 gbytes of storage, inserting 300 events per second 24 hours a day. Mission
critical database. Scaled fine, but really maxed this server out.
- oracle 9i - on sun e450s (many implementations) - all scaled fine
- SQL Server 2000 - on 2-way 700 mhz HP boxes with 1-2 storage arrays, each handling
120 gbytes of data and thousands of narrowly-focused search queries a day. Scaled
fine.
- Postgresql 7.1 on shared servers at Command Prompt - scaled fine, didn't spend enough
time to really benchmark anything.
- Oracle 8i - on Sequent 4-way with emc storage. Scaled great, was easily able to load
50 million rows in about 15 minutes. Queries that ran for 18 hours on E6500s with 12
CPUs in SAS ran in 6 minutes. It was all about the data modeling & tuning tho. This
was around 1998.
- Informix XPS on IBM SP2s (10 nodes, each with local SSA disk) and 800 gbytes of data.
This was around 1995-8. Note that these CPUs were only 66mhz, but the queries, split
across all nodes were extremely fast. 10 seconds to scan 300 gbytes with complex
queries was very good for that hardware.
- Sybase 4.9 & 10 on DEC hardware. This was in early nineties, before the commercial
databases got sophisticated scalability. It was ok, sybase now has their IQ product
which is the high-end for complex queries.
- DB2 2.1 - 2.3 (many mainframe implementations). Back in the eighties to mid-nineties.
The cost of sharing resources on mainframes back then prohibited large databases most
of the time. Can't really compare that to today.
- very many shorter side projects in which I've been pulled in to consult on a piece of
a project, its direction, strategy, etc.

A few more comments:
- db2: somewhat clunky (it's from ibm), but extremely good performance for the price and

Re:Gosh

[rtaylor]

ACID is more than just the transaction. Lets look at C:

Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database's consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

Now, take a peak at MySQLs datetime handling regarding dates such as 2005-02-29. I'll admit I haven't checked recently but does it still round down integers are are too large as well? It should reject the data completely and not massage it into place.

With feature creep being what it is, crappy website databases all of a sudden become important and store customer information. I simply don't trust MySQL.

Re:Gosh

[consumer]

That seems awfully nitpicky compared to supporting transactions and referential integrity constraints, but MySQL 5 supports the sort of strict date checking you're asking for as well.

Re:Gosh

[rtaylor]

That seems awfully nitpicky compared to supporting transactions and referential integrity constraints, but MySQL 5 supports the sort of strict date checking you're asking for as well.

For my own usage, any flaw which can cause data loss is equally as bad as any other. Nitpicky is exactly right. Bugs are one thing as they will eventually be discovered and quashed, but a feature designed (or mis-designed) with dataloss potential is reflective of the programmer and vendor priorities -- they don't care about my data only that there is a product on the shelves.

Re:Gosh

[aztracker1]

Just have to contend one point, PostgreSQL, as of 8.0, has a stable windows version, I like it a lot, and am glad to be able to test with it in windows now... Posgres is so far beyond what MySQL is, it isn't funny... and cleaning up data because of a small error, that would have been caught of the RDBMS had done proper errors isn't fun.

Also, in the 4.x tree (maybe better now), when I was using foreign key constraints in mysql, I did regular dump/backups and when I had to migrate to another system, found that the dumps didn't go in an order that could be re-imported... hard to edit a 80mb file (could have been much larger) to re-order the tables so they would import properly with fkey constraints... (I know I could dump each table separately, but when the dump is the primary backup, it should damned well work for a re-import when you have fkey constraints).

I hate mysql with a passion, and will *NEVER* use it again, if I don't have to, the language, even in ANSI/IBM mode is only partially complient, for foreign keys, you still have to use backticks instead of ansi quotes, I mean, WTF did they decide on such a non-standard identifier character, hell even TSQL's (Sybase/MS) square brackets make more sense.

Re:Gosh

[Anonymous Coward]

Wrong...

mysql> create table testtran(c1 int primary key, c2 int) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testtran values (1,1),(2,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update testtran set c1 = 3 where c1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

*** now in second MySQL client window ***

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testtran values (4,1);
Query OK, 1 row affected (0.00 sec)

Re:Gosh

[larry bagina]

suggestion:

If you use php and mysql, I suggest using the Pear::DB module (include('DB.php')).

1. It's more similar to how other languages use SQL
2. It makes it easier to switch databases
3. It decreases the chances of an SQL injection attack

Also, throw these in your .htaccess file (or php.ini):

• php_flag magic_quotes_gpc off
• php_flag register_globals off

Since those options are on by default, and retarded. (Not "slow" retarded, not even "short bus" retarded, I mean "wearing diapers because you shit in your pants and eat it" retarded).

Re:Gosh

[musicmaker]

Dude, the fact that _ANY_ system would allow 2005-02-31 as a valid date would fail just about any commercial QA process. The product would NOT GET VALIDATED AS FUNCTIONAL!!!

You can set strict checking on, but then the application can turn it off at will. Cute.

If you care about your data, you use an RDBMS. MySQL isn't one. Even Microsoft Jet has foreign keys and transactions on by default. Yes - MySQL is WORSE than Access folks.

Re:Gosh

[Anonymous Coward]

We'd like it BY DEFAULT, and without any random downgrades.

Presently, this doesn't work. The MySQL DB schema falls back to MyISAM if InnoDB isn't available, or if the table manager isn't specified. Failed inserts fall back to some weird value (e.g, failed enum inserts usually fall back to '').

Until MySQL *works by default*, the criticism stands. Being possible to tune to work does not cut it.

Oh, and the error messages from the SQL parser are really horrible. Just to add insult to injury. (I work with MySQL day to day and have for a couple of years, and I've worked with PostgreSQL day to day before that. PostgreSQL was WAY preferable - we just have problems taking the migration costs.)

What possibly could anyone have against MySQL?

[dynooomite]

I thought everyone was just in love with Java and XML?

Apparently not

[Anonymous Coward]

'prime time' enough for Sun.

I can think of a pretty big plus in the column...

[mister_llah]

MySQL is free, that's a pretty hefty plus for many applications. ... though it might not be the best choice for an enterprise wide solution just yet...

Will it dethrone any of the biggies? Not for a long time and not without improvement.

We're not talking the timescale for Linux to take ground in the desktop war, since databases are already technical... there isn't that 'learning curve' from the user (well, there is, but the 'user' shouldn't be as terrified as a Windows user switching to Linux)... :)

New features

[Spy der Mann]

(for the lazy)

        * capacity for very large databases
        * stored procedures
        * triggers
        * named-updateable views
        * server-side cursors
        * type enhancements
        * standards-compliant metadata (INFORMATION_SCHEMA)
        * XA-style distributed transactions
        * hot backups.

Re:New features

[smallguy78]

That looks like a feature list for SQL Server back in 1998

Re:New features

[Anonymous Coward]

Notably absent from that list is "SQL"...

Re:New features

[Michalson]

Or more specifically ISO SQL-92, or any other SQL standard. Everyone else seems to be smart enough to be able to implement a well documented industry wide standard as their base. MySQL didn't even start supporting UNION until version 4.

Re:New features

[killjoe]

As long as you are handing out information....

Any word in improvements to replication? Specifically merge (multi master) replication and sliced (replicating sub sets) replication?

Re:New features

[ThaFooz]

For the lazy, or those who cut-and-paste for karma and miss major components:

• The Server Mode variable (allows varying tolerance of 'bad' data & sql standard compliance, fixing the truncation 'feature' that everyone complains about)
• The Archive Storage Engine (compressed, insert & select only engine ideal for logging)
• The Federated Storage Engine ('virtual' tables, allows for cross-database joins)

Re:New features

[SatanicPuppy]

Yea, the price is pretty similar too.

Oh...Wait...

Re:New features

[psychosystem]

Although not well documented yet, it does now support multi-master replication. I've done some testing with it (it is one of the main features I REALLY need for my current project) and have had no problems this far.

Re:New features

[samael]

Submitters: Use Coral Cache!
Before: website.com/path
After: website.com.nyud.net:8090/path

Actually, as I'm behind a corporate firewall that _doesn't allow ports other than 23 and 80_ I'd rather they didn't...

Yay for MySQL

[Sliptwixt]

As a developer who went from Open Source (5 years) to .NET programming, the only thing I *really* missed was working with MySQL. Fast, light, stable, and easy to work with. With all the new version 5 features, plus the help of adapters like ByteFX , MySQL is now part of a valid enterprise solution to .NET developers (IMHO).

Re:Yay for MySQL

[Sargon1969]

Riiiighhhht

Re:Yay for MySQL

[The Bungi]

The ByteFX provider has been discontinued since early last year. MySQL hired the guy and he abandoned his original work to do the MySQL adapter, which like all of their stuff is 'free-licensed' under the GPL, so you're screwed if you want to do anything other than commercial and you happen to dislike having the GPL forced on your (equally free but not released under a viral license) code.

I started using the ByteFX provider, reporting bugs on sf.net and whatnot because it was licensed under the LGPL. Then all of the sudden it's MySQL or the highway babeee!

Heck, if he had continued working on the LGPL version I would have 'bought' a license from him or I'd have found some other way to throw some decent money his way. But MySQL? I wouldn't touch them. They've changed their licensing scheme once and they can do it again.

(actually, I would buy their provider if it wasn't written in managed code. it's too slow and consumes too much memory)

'Vendor lock-in' also means having a company do a 180 on the license and abandoning your branch of the product when you don't have time or expertise or interest to go in and fix someone else's code. I don't want code, I want a binary black box that works and support to go along. I don't want/need free. I don't want that social movement bullshit either. It's software. Sell it to me and support it. So far the two open source vendors (MySQL and RHN) we've worked with at that level suck much more than Microsoft, IBM or Sybase (IBM is especially great with their 3-year 'we won't support it anymore even for money' product cycles).

Anyway, rant over =)

Re:Yay for MySQL

[HeadDown]

I don't think you understand what 'enterprise' means. One thing it means for sure is 'conservative'. With all these version 5 new features just released, any *sensible* enterprise will wait to see how they work in the real world. At *somebody elses* real world. Enterprises are not going to volunteer to be beta sites.

Re:Yay for MySQL

[Anonymous Coward]

• I don't think you understand what 'enterprise' means. One thing it means for sure is 'conservative'. With all these version 5 new features just released, any *sensible* enterprise will wait to see how they work in the real world.

The Sabre case study linked to by that article is by Electronic Data Systems (EDS). It's a very conservative computer services company founded by Ross Perot.

They're touting MySQL at Sabre, so it will probably show up at their other clients.

MySQL? not mine!

[h15n]

MySQL days are over. Let YourSQL be PostgreSQL. Huh! Sun will choose PostgreSQL

Re:MySQL? not mine!

[CKnight]

I suspect that what may be pushing Sun to postgresql is the fact that they can, in essence, 'own' their derivative work without licensing it as they would have to do with mysql as opposed to any technological reasons. Pay no mind to this post though, that's just the conspiracy theorist in me talking.

No roles? Prime time, my arse

[Anonymous Coward]

Without support for security roles, MySQL isn't ready for the prime time, at least not in the enterprise space.

MySQL != SQL

[SharpFang]

One thing you must remember about, when considering MySQL. It's a relational database, all right, but it doesn't really support SQL.
It supports most of SQL syntax, so SQL gurus will find it easy to learn. Most of basic SQL stuff works. But more advanced constructs like nested queries are either unsupported or terribly unoptimal, and some SQL features are there just for compatiblity sake but shouldn't be used at all. Instead you should learn and use a bunch of MySQLisms that aren't found anywhere else and do the same thing, much better (faster, safer, bug-free). So if you have a database app and ponder what database to integrate it with, choosing MySQL means more than plain tweaks. It may mean deep hacks. MySQL is devilishly fast when it comes to simple queries. Few databases can beat it in this domain. But it comes with a cost, shortcuts taken prolonging/breaking many other tasks. So choosing MySQL is a dangerous choice - it's a lock-in.

Re:MySQL != SQL

[C. E. Sum]

What are the biggest areas you see these problems?

Re:MySQL != SQL

[matt4077]

If MySQL supported only a subset of the SQL Standard (big IF since it does have stuff like nested queries, transactions, triggers etc now), thats the opposite of lock-in. Obviously, there is no harm in using only a subset of SQL and then moving to a different RDBMS. It's exactly the Oracles and Microsofts with their embrace & extend policy that makes it difficult to switch: Oracle has _every_ (well, most - not the excellent fulltext search) feature of MySQL, so there is no problem to switch.

Re:MySQL != SQL

[vadim_t]

I share your dislike of mySQL, but database lock-in is nearly impossible to avoid.

It can be done if you do something simple, like say, a forum, but for anything large, advanced features are incredibly useful, and will make sure you're dependent on that specific database.

For instance, it's very desirable to have everything in a stored procedure. Not only this gives a performance benefit, but it also increases security. Building your database this way you can ensure that nobody will ever be able to insert bad data. But the price of that is that converting to a different DB will be really difficult.

Re:MySQL != SQL

[Sheetrock]

Bringing advanced SQL queries into MySQL and moving advanced (My)SQL queries out of MySQL.

In both cases, you want to look before you leap. Do some trials to see how long porting will take before giving a time estimate, test the new system thoroughly (although that's recommended practice for switching RDBs anyway).

That's not to say MySQL is the only platform where you risk lock-in. Database triggers can be hooked to implementation-specific things, for example. Unfortunately as with programming there are trade-offs to be made between optimization and portability and if you're pushing lots of tuples you opt for the former.

Re:MySQL != SQL

[shmlco]

Constraints and validation are one thing, but IMHO it's NOT "very desirable to have everything [sic] in a stored procedure".

Placing extensive amounts of business logic in SPs can lead to fractured code bases and schizophrenic systems, vendor lock-in, reduced scalability, extremely difficult debugging, and porting/cross-platform problems.

If performance is so critical that SPs can make or break a system, then you should probably consider changing the architecture by adding application tiers.

Re:MySQL != SQL

[Bob9113]

It supports most of SQL syntax, ... Instead you should learn and use a bunch of MySQLisms that aren't found anywhere else and do the same thing, much better (faster, safer, bug-free).

AFAIK, the closest thing to pure ANSI SQL compliance is SQL Server, which is pretty limited as far as very large databases go. How is the above different from, say, Oracle? Orachle uses non-standard CLOBs, sequences, connect by, doesn't do "join" syntax (or was join recently added?), etc.

Re:MySQL != SQL

[bailey34]

ANSI joins were certainly present in 9i and 8.1.7 as far as I recall.

Re:MySQL != SQL

[vadim_t]

Well, it depends on the environment, of course.

IMO, for instance, it doesn't make much sense to write code full of page-long SQL queries. Not only it looks ugly in the source, but it also introduces potential problems when you find out somebody is using an ancient version of the application that does something wrong.

If your code to create a client is inside a stored procedure, you have several advantages: SQL is in the database, where it belongs. Any bug fixes can be instantly applied to everybody who uses the database. And if you have requirements like having different groups of people with different overlapping permissions, it's safer to enforce that kind of rules in the database than in the application.

Ideally, you could build such a system that you could allow people to access the database through a SQL interface, and still make it impossible for them to do anything they aren't supposed to be able to.

On performance: We have a stored procedure to calculate an article's price here. I have measured an improvement of approximately 20x better performance by simply rewriting the original code written in VB6 as a stored procedure, and that was raised to about 30x after figuring out the fastest way to call it.

The reason is quite simple: To calculate an item's price data must be retrieved from several tables, but the final result consist in just one thing: The price. A nice improvement comes from just avoiding the wait for the network. More improvement comes from the SQL Server not having to re-parse the query every time. This procedure doesn't take long to execute, but was starting to seriously add up due to needs to calculate the price of hundreds of articles at once.

The additional advantage is that now we have one unique place where the price is calculated. If the mechanism ever needs to be corrected, we can ensure it changes everywhere at once.

Re:MySQL != SQL

[smackjer]

SQL statements belong in neither stored procedures nor in the code.

ORM tools, like Hibernate and TopLink, allow you to map objects to relational tables, and generate the SQL on the fly. (Hand-tuning is still possible, though you would not put hand-written SQL in code, but in some kind of a properties file that is loaded at runtime.) Hibernate, for example, supports a variety of SQL "dialects", which it uses to generate SQL that is compatible with the vendor that you (or your customer) chooses to use. You should be able to swap SQL Server for Oracle for PostgreSQL for MySQL for Firebird for DB2 in minutes.

If ORM tools can't or won't be used for some reason, SQL statements should still NEVER live in the code, and should not be dynamically built in the code. They should be externalized, similar to strings for localization, and they should be prepared statements.

Re:MySQL != SQL

[Anonymous Coward]

It's a relational database, all right

Careful.. MySQL (and PostgreSQL and Oracle) are *SQL* databases. The relational model is quite different from SQL.

Example: in the RM, every value for a given attribute (column) must be of the same type. In SQL, some values can be "NULL" which is a special value, not drawn from the type.

Example: in the RM, attributes are unordered. In SQL, attributes have a consistent left-to-right ordering (this violates the Information Principle which states that all data must be stored explicitly in tuples [rows], not *implied* by the rows).

Example: in the RM, relation values are *sets* (no duplicates allowed). In SQL it's easy to create queries or even *tables* that contain duplicate rows.

Example: the RM depends heavily on relational equality.. SQL has no (easy) way to compare a result with a table, or two tables, or two results.

Example: the RM specifies views, which should be indistinguishable from base relvars (base tables). SQL doesn't specify updateable views (MySQL 5.x has "sometimes" updateable views, depending on how the views have been constructed .. views built with JOINs are not updateable in MySQL 5.x for instance)

So please, don't call SQL databases "relational", it just spreads more and more misinformation and makes people equate "relational theory" with "SQL" and therefore they assume deficiencies in SQL are deficiencies in the RM, which just isn't true. RM is completely general.

Re:MySQL != SQL

[TheLongshot]

SQL statements belong in neither stored procedures nor in the code.

Then, what do you put in stored procedures?

Re:MySQL != SQL

[Anonymous Coward]

allow you to map objects to relational tables

Tables are an *SQL* concept, not a relational concept. The relational equivalent are "relation variables", i.e., variables that hold a single relational value.

Also, with Hibernate et al, objects are usually mapped to *rows*, not to *tables*.

This is necessary because SQL doesn't allow you to place composite values (objects) in a single column, you have to "unpack" their elements into individual columns, which creates all sorts of problems. For example, if you make an ad-hoc query that only returns a subset of the columns, does it still map to the same object class? Another example: suppose you JOIN two tables together: which object class is the result? It contains attributes from two or more tables. Down this road lies madness!

This whole concept is the tail wagging the dog though.. the purpose of the DBMS is to have multiple applications access the same data in a consistent way, NOT to have multiple DBMS's on one app!

Re:MySQL != SQL

[killjoe]

Putting everything is stored procesures makes it harder to maintain the code. In my opinion the database is not the place for business logic.

By the way, although it's extremely common for VB developers to embed SQL into their code it's not considered good programming practice.

Re:MySQL != SQL

[killjoe]

" Bringing advanced SQL queries into MySQL and moving advanced (My)SQL queries out of MySQL."

You should treat your SQL queries as a language. You can use the run of the mill translation libraries that way.

When you think about it that's exactly the problem, translating from one language to another. Lucky for us that problem is common enough to have gotten plenty of attention.

Re:MySQL != SQL

[deicide]

MySQL has had nested queries and everything else you listed for quite some time. Things move ahead - stop using 3 year old reasonings.

Re:MySQL != SQL

[tweek]

As someone who works for a company that uses Hibernate pretty heavily, ORM is not the pancea that everyone claims. I like ORM as much as the next guy but in an effort to write generic SQL, your ORM will usually use a pretty inefficient route.

Hibernate and ActiveRecord don't run EXPLAIN plans on queries. If you've ever looked at some of the SQL generated by hibernate, it can make you cringe. We created indices to match what hibernate was using only to move the logic into an sproc to get the performance we required.

ORMs are nice from the developer side of things but can be a bitch from the DBA side of the house.

Re:MySQL != SQL

[aclarke]

You seem to be approaching the situation with the mindset that database portability is of primary importance across all projects. Why would I add 10% of overhead in abstracting my SQL out when I know there's only a 2% chance that the product will have to move to another DBMS? Then there's the added issue of relying on someone else's decision of how my SQL should be written. I haven't used all the query builder tools in the world but I haven't yet found one that can help me write SQL as fast as I can on my own, or write better SQL than I can on my own, and I'm not even that great a SQL developer really.

Writing code to the "lowest common denominator" of SQL is not the best utilization of the $150k your company just spent on Oracle, for instance. An ORM tool is not likely to be a substitute for a database developer experienced with a specific DBMS. I realize that SQL code only represents a portion of why one would choose a specific database product (scalability, reliability, etc. being other factors) but it is an important factor nonetheless.

Additionally, not all languages support prepared statements. On top of that, prepared statements are generally not as efficient as stored procedures, so again there's a reason to use stored procedures. Writing stored procedures doesn't really mean that the SQL code has to stay "in the database". The SQL to create the stored procedures can stay in a code repository, as I'm sure you know.

I'm not saying your ideas are bad or wrong, per se, but that you are making broad statements about how things are supposed to be and what should never happen. Your point of view represents only a portion of projects out there with solid architecture, and as such are misleading.

Re:MySQL != SQL

[Anonymous Coward]

> > SQL statements belong in neither stored procedures nor in the code.

> Then, what do you put in stored procedures?

In a secret facility outside the US where the CIA can torture them freely.

Re:MySQL != SQL

[Jaime2]

I've done a lot of performance testing and taking batch x by submitting it raw vs. putting it in a procedure have only very small differences in performance. The more processing is done, the smaller the difference. I've even seen cases where preparing and executing ad-hoc SQL is slightly faster than using an existing SP.

The only time SPs are significantly faster than ad-hoc SQL are when the two are different. Every database worth a crap today implements ad-hoc batch caching. That means that SQL blocks from applications execute exactly as if they were pre-compliled SPs.

As for sharing code, that causes as many problems as it fixes. When code is implemented as SPs and called from multiple apps, then a change to an SP can potentially have compatibility problems with any of the applications. Nothing is guaranteed to work. If the business code were in a seperate business layer, then maybe the change wouldn't be instantly effective in all applications, BUT, all application would be guaranteed not to break until you visit them. Test each app and patch. If you haven't figured out how to effectively deploy a middle-tier fix, that's your own fault.

I fight this battle every day at work. We implement WAY too much code in SPs. It takes far longer to build logic into an SP han to put in in compiled code in a good IDE. Almost every developer is mores likely to make an error in SQL than in a procedural language. Also, SQL debugging is never as easy as native debugging.

Architecturally, a good business layer built as Web Services is far superior to a business layer built as SPs. Better passing semantics, better language choices, more portability. If you don't like disconnected processing, pick your favorite three tier technology -- COM+, .Net Remoting, CORBA, J2EE, etc.

In 1996, SPs were invaluable. Today, they are a great tool, but not necessary for either good performance or good security.

Re:MySQL != SQL

[fbg111]

One thing you must remember about, when considering MySQL. It's a relational database, all right, but it doesn't really support SQL.

To be precise, it only partially complies with the relational model, and doesn't really support SQL, which doesn't comply with the relational model either.

Re:MySQL != SQL

[smackjer]

In my experience, stored procedures are too often used when they aren't necessary. I have never run into a problem that couldn't be solved without a stored procedure, and the solution is typically portable.

Stored procedures have a place, but it's not a very big one in the vast majority of projects. I have to question the architecture of any software that makes extensive use of stored procedures.

Re:MySQL != SQL

[smackjer]

I agree that database portability isn't of primary important in all projects. But portability is a common requirement, and there are good reasons besides portability to stay away from stored procedures. Maintenance is a huge one. Scalability is another. Separation of model and business logic is another. I'm not sure where the "10% of overhead" figure comes from... I've never felt my that my productivity suffered by writing portable SQL or using tools to do it for me. The amount of money a company spends on a RDBMS shouldn't persuade a developer into locking himself into that vendor. If a particular RDBMS provides some functionality that their competition does not, and your product absolutely must leverage it, that's a persuasive argument. A personal preference for Oracle's "TO_DATE" function should not be a factor. Modern database systems perform very well. The performance difference between a stored procedure and a prepared statement is negligible if it exists at all. In fact, a prepared statement will most likely scale better. My point was that developers should try to use ORM tools (like Hibernate). Then they don't have to write ANY SQL. Most developers aren't very good at writing SQL, and assuming it works at all, it's probably not optimal to begin with. An ORM tool will produce SQL at least as good as a person can the majority of the time. Bulk updates are the exception.

Re:MySQL != SQL

[smackjer]

The creators of Hibernate are the first to admit that it doesn't solve every problem, and it wasn't intended to. What it does is produces good enough SQL (at least as good as a person) for most problems. Hand-crafted SQL or HQL can still be written (and externalized), and sometimes you just need to go as low-level as possible (JDBC, for ex).

Re:MySQL != SQL

[tweek]

True. We actually do that in some places. We do all operations on the database as dirty reads except in several places where we simply grab a connection from Hibernate and hit the database directly. Saves us the hassle of maintaining two connection pools to the database just to get different isolation levels.

I get the feeling alot of times that many ORMs lull the developer into a false sense of security. I can't count how many times our DBAs have heard "This part of the application is performing slow" only to get statement back from the developer like "I don't know what the query is because Hibernate is generating it" when they ask for a copy of the query to run EXPLAIN against.

It seems almost counter-intutive to development when the DBA team has to go back and create a view or an sproc just to make it perform.

Another gripe I have with Hibernate is the mappings. My understanding is that many of the mappings have to be manually maintained or that hibernate just has problems with some data types? One thing I've grown to love about ActiveRecord is how it handles its mappings. Then again I've not thrown our database at it yet ;)

Re:MySQL != SQL

[Not The Real Me]

http://dev.mysql.com/doc/mysql/en/stored-procedure s.html

Let's see, according to their version 5.0.13 release candidate notes on stored procedures:
MySQL follows the SQL:2003 syntax for stored procedures, which is also used by IBM's DB2.

Re:MySQL != SQL

[brianmf]

Are you reading "Database in Depth" at the moment?

Re:MySQL != SQL

[Anonymous Coward]

Score: 3, Insightful.
For a post containing only a question.
An insightful question, only on Slashdot.

Re:MySQL != SQL

[vadim_t]

As I was saying, it depends on the situation.

There's no middle layer here, and I'd say that I'm quite glad there isn't, because if there was it'd be written in Visual Basic 6, and the language is just far too horrible for that application. Just the error handling is so awfully bad that it'd case more problems than it's worth.

The stored procedures here are almost all insert/update queries, which would have to go somewhere anyway. It's either the application, middle layer or database.

Re:MySQL != SQL

[electroniceric]

Ah, the ever-present in-the-DB versus in-the-app design debate. I guess I just don't understand the tradeoffs that well, because the subject seems pretty straightforward to me:
Keep as much logic in your app as you can get away with.

As I understand Hibernate and ORM's in general (and I'm still relatively new to them), they're basically just an automated way to implement and populate objects within your application that represent the "API" exposed by your database. So if the current database "library" isn't adequate performance-wise, you can just rework the API and hook Hibernate up to views and stored proces instead of tables. In other words, you can put just enough logic in the db to solve your problems. Yes, you're no longer totally compatible with all databases, but you're also not totally dependent on one RDBMS's syntax and features. IMO, translating basic, plain-jane stored procs across DBs isn't all that hard (sorta like going from C to Perl to PHP). When you get to fancy nesting and joining it's a different story altogether....

Re:MySQL != SQL

[smackjer]

Hibernate has an option to turn output/log the SQL statements that it generates. You can also run a profiler on your database (SQL Server's SQL Profiler is nice) to capture everything that happens.

Mappings of every common data type are built into Hibernate, and you can write custom user types for anything weird. I'm not aware of any problems. In fact, Hibernate will figure out what data type a column is by using reflection on the POJO. If column "LASTNAME" is mapped to "lastName", and "getLastName()" returns a String, it's smart enough to know that the column is a varchar, or the equivalent in the SQL dialect you are using.

Re:MySQL != SQL

[musicmaker]

Have you actualy read the SQL Standard - not even Oracle supports SQL 99 fully.

oh and by the way 'interval' is a reserved word in the SQL standard that does something important. It's an abstract function in MySQL.

MySQL does _not_ support the SQL standard, as do few other dbs out there.

Re:MySQL != SQL

[Anonymous Coward]

Nested queries in MySQL doesn't work. Or more specifically: Doesn't always work. It starts spouting errors about "Same field used inside and outside subquery" (or something to that effect) for perfectly legitimate queries.

Re:MySQL != SQL

[Anonymous Coward]

Nested queries in MySQL doesn't work. Or more specifically: Doesn't always work. It starts spouting errors about "Same field used inside and outside subquery" (or something to that effect) for perfectly legitimate quer

Transactions don't work properly - there's no support by default, and you can break support at any time (by accessing a MyISAM table) with no warnings. And you can't hack away this at the source level, either, as you need to access MyISAM tables to be able to implement monotonically increasing sequences (InnoDB tables do not keep the AUTO_INCREMENT counters guaranteed monotonically across a database restart, but reset to max(column)+1).

Foreign keys may finally work with backups; I've not run into any problems recently. They work fairly badly with developing programatically generated SQL - any violation result in an error message with no indication of what constraint was violated.

I personally would prefer that MySQL development totally and suddenly died. This would give a good reason to give to our customers for switching away from MySQL NOW, instead of limping on with that horrible excuse for a database.

o good

[Abstract_Me]

so it only lacks in the java and xml portions? thats great... its not like java and xml are two of the fav buzz words in business or antyhing...

One more bad mark...

[Anonymous Coward]

...is for MySQL AB, the company behind the database. They have recently entered a partnership with SCO and have thereby stabbed the Free Software community in the back.

Obviously it doesn't speak to the technical merits of the software, but one thing we in the FOSS community have learned is that there is more to evaluating software than how well it performs a specific function.

We chose Postgresql

[Ritz_Just_Ritz]

I'm mostly just the digital plumber in my firm, but about a year and a half ago we were in a situation where it was time to migrate our production servers off of SQL Server 7 to "something else." The "something else" needed to be Linux friendly since we were phasing out M$ in our production environment in general. So we hired 2 former Oracle employees and expected them to tell us that Oracle was the answer. After about a month of nosing through our existing code, we were given a menu of options with their preference being postgresql. Mysql didn't make the cut because it lacked "important features" and wasn't "sql compliant", lacked "triggers", and something about "locking" which escapes me at the moment. I don't know a database from a hole in the ground, but that was our experience. We've been using Postgresql with RHEL 3 and RHEL 4 without incident. Very good for us...not so good for Mr. Ellison and Mr. Gates. Cheers,

Re:We chose Postgresql

[vadim_t]

It was a very good decision.

Triggers are queries that are run automatically when the associated table or view is changed. It's generally considered to be a bad practice to overuse them, but they have a few very nice uses.

One is implementing complicated checks. You can make an update query fail if any fields don't meet arbitrary requirements. That's good because you can use that to ensure that everything inserted in the database makes sense.

Another is logging. You can easily make a trigger that inserts the previous content of a row into a different table. Can come really handy for debugging, or when you simply want to easily make sure any changes to some data can be tracked. Also handy if you not only want to deny access to something, but also record somewhere that somebody tried to touch it.

Another use can be gradual redesign. If you're phasing out a column, you can do it in steps: Removing its usage from the latest version of your DB interface, and adding a trigger that ensures it has some value that doesn't confuse older versions. This can be used to provide smoother upgrades.

Locking is a major problem. In a database locks must be placed on data to maintain the consistency. You definitely don't want a database that locks the whole table without a really good reason, because as soon as table locks start happening, your performance goes to hell, as everything else will have to wait.

Re:We chose Postgresql

[MBraynard]

What is RHEL 3 and RHEL 4?

Re:We chose Postgresql

[MrSnivvel]

What is RHEL 3 and RHEL 4?

Red Hat Enterprise Linux 3 and Red Hat Enterprise Linux 4

Re:We chose Postgresql

[Slashcrunch]

Postgresql is an excellent database, and is quite often my personal DB of choice.

One thing prevents me recommending it at places I work is that when I want to do a count(*) on very large datasets (not just entire tables) the response time goes through the roof. This seems to be because table statistics are only updated when the database is vacuumed rather than maintained in an ongoing fashion.

There are various work arounds involving triggers, updating sequences, and estimates based on last statistic update etc, but seriously... are you for real? What year are we in now? This doesn't work well for databases with large tables or on queries that will return large amounts of data. I don't have anything like this problem with MySQL or even MSSQL (neither of which are perfect either of course)

As far as I'm concerned it is a _major_ black mark against Postgres, and a definite hinderance to application development.

Re:We chose Postgresql

[Penguin]

You would experience that in several row level locking engines, e.g. InnoDB and Oracle (thought I might be on thin ice here regarding Oracle)

InnoDB has the same behaviour:
http://dev.mysql.com/doc/mysql/en/innodb-restricti ons.html
"InnoDB does not keep an internal count of rows in a table. (This would actually be somewhat complicated because of multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool."

Row level locking has a bunch of advantages, but in a bunch of web applications (where COUNT(*) seems to be used a lot) table level locking could result in quicker queries.

You can read more about different kinds of locking:
http://dev.mysql.com/doc/mysql/en/table-locking.ht ml

Re:We chose Postgresql

[Slashcrunch]

OK, from memory I have have probably only tried this on MyISAM tables. I haven't tried the InnoDB engine yet so I'll give it a spin just for kicks.

Yes, count(*) does seem to get used a lot in web apps, and web apps are nearly 100% of my work these days. It's a shame because Postgres feels so much more like a 'real' DB than MySQL! I can't tell a client, "Sorry, but we can't run that query in your app... it will just take too long", they are going to ask why, and how I can provide that data they want. I think I'll have to stick with MySQL for a bit longer.

Re:We chose Postgresql

[Ritz_Just_Ritz]

Red Hat Enterprise Linux. For some of our "mission critical" servers, we run RHEL for the paid support. On all other machines, we run CentOS, which is a free "clone" of RHEL.

Cheers,

Re:We chose Postgresql

[Anonymous Coward]

May be completely off base, but I vaguely recall from the latest Postgres release notes that they included an "auto-vacuum" feature that won't require you to explicitly run the vacuum anymore.

Re:We chose Postgresql

[JabberWokky]

That is a wonderful story, and I like it. But what exactly does it have to do with the article and post's point that the brand new MySQL 5 may be ready for some enterprise applications?

I can tell you some nifty stories about FoxPro and dBase, and they are database yarns as well. But this post was about a new version of MySQL... which your comment really had nothing to do with... unless I'm missing something.

--
Avn

Re:We chose Postgresql

[archen]

You're both sort of off. There is an auto-vacuum, but it doesn't do a full vacuum which you'll want to do from time to time. Vacuuming doesn't update the statistics anyway, that is done by ANALYZE which can be done with a vacuum or by itself.

When people ask me about postgresql I'm always up front and tell them that it's a great database BUT you have to vacuum it before a billion transactions... or else! It sucks, but it's something I can live with. I can do a vacuum at night, and a full vacuum on the weekend, that's no problem for me. If I need statistics updated more frequently then use a cron job to ANALYZE [table] as often as needed

Re:We chose Postgresql

[jenkin sear]

This is endemic to postgresql's MVCC (multi-version concurrency control) system- they support row-level locking instead of table or page locking. This means that when a transaction is underway, users inside and users outside the transaction will get very different values of count(*). This makes it hard to cache the results of aggregate queries.

So the tradeoff is execution time for count(*) queries versus accuracy and speed for transactions- if you don't use transactions, you'll probably be happier using MySQL anyway ... Unlike MySQL, you can't degrade the backing store in postgres to support a simpler, but more dangerous approach.

As always, when picking a DB, YMMV- you need to determine which feature set is most important. Me, I tend to value transaction safety and data integrity pretty highly, but there are certainly applications where counting the number of rows in the database is more important...

Re:We chose Postgresql

[MBraynard]

What tools to do you use to manage the PostgreSQL database? I like MS SQL's Query Analyzer and Enterprise Manager. I also use MySQL extensivly and like MyPHPAdmin.

Re:We chose Postgresql

[Sentry21]

Locking is a major problem. In a database locks must be placed on data to maintain the consistency. You definitely don't want a database that locks the whole table without a really good reason, because as soon as table locks start happening, your performance goes to hell, as everything else will have to wait.


My personal favourite was the time one of our programmers (my boss) tried to add an index to a two-million-row MyISAM table which was being INSERT'ed to by a redirect script a few dozen times a second. As a result, any INSERT queries would block until the ALTER TABLE finished with the index. Well apparently, my predecessor at this company set Apache's MaxClients to 2000, so every request that was made spawned a new Apache process. Thus, with every hit on our website, another process was spawned, and within a minute our webserver had crashed.

Suffice to say, table locks are pretty much the worst thing ever. Unavoidable, perhaps, but terrible nonetheless. Remember kids: INSERT DELAYED is your friend!

Re:We chose Postgresql

[Ritz_Just_Ritz]

No idea. Our (former Oracle) DBA guys deal with that stuff. I just put the network together, secure the machines, and deal with ongoing patching.

Re:We chose Postgresql

[MBraynard]

CAn you ask and let me know?

new feature... Hot Copy ?

[layer3switch]

So the perl tool come with MySQL 4.x, mysqlhotcopy isn't "hot copy"? What's so "hot" about the new "hot copy"?

Re:new feature... Hot Copy ?

[Anonymous Crowhead]

mysqlhotcopy is a perl script that basically just locks all tables and makes copies of them using cp. I assume the new hotcopy will be built in.

Re:new feature... Hot Copy ?

[mike.newton]

Is it anything like GTA's "hot coffee?"

Re:I can think of a pretty big plus in the column.

[ProfaneBaby]

Postgres is Free, MySQL is tied to a silly dual license (viral GPL and commercial), neither of which is as Free as the 3-clause BSD.

No.

[Anonymous Coward]

MySQL still sucks. Just a little less.

Re:I can think of a pretty big plus in the column.

[temojen]

Postgres isn't available on 80% of web hosting firms and 90% of off-the shelf web scripts (that require a DBMS). (I wish it was)

innodb and fulltext?

[allanw]

Can you use transactions, and have referential integrity and fulltext indexing on the same table yet?

Re:innodb and fulltext?

[jaiyen]

Not yet apparently, according to http://www.innodb.com/todo.php, but at least it looks like progress is being made.

In progress: Add FULLTEXT indexes on InnoDB tables. A sponsor for this project has been found, and a developer has been hired. Appears probably in 2006.

Re:innodb and fulltext?

[mattwarden]

Jeez, would you like it to make you toast, too? I mean, come on! I bet you're one of those people who wants optimized subqueries, too.

MYSQL & MAXDB

[teaDrunk]

Does anyone know if MAXDB ever helped improve MYSQL in any way ?
Actually, I have wondered if MAXDB will ever have a place, with the way MYSQL has been evolving.

Liked it, but don't use it anymore

[nighty5]

I used to love MySQL back in the hayday, but then they changed their license model, thus it was "good night sweet prince".

Most other decent databases use something similar to LGPL for use of their libraries, thus there is no need to disclose your source code in an application that uses the database. This is rather a critical feature identified by almost all database vendors. Even Microsoft SQL has an LGPL-like license that doesnt mean you have to share your code.

Once MySQL was reaching critical mass, they decided to change the rules and restrict the license. PHP and others revolted and dumped MySQL for SQLite as the default database for PHP 5. Some could argue it was due to library mixup hell, with multiple versions of libraries on the system, but we all know the main reason was behind the license.

MySQL got a bit scared and made this silly license exception to the top 20 FOSS projects (don't quote me on that, recalled from memory) so they could be LPGL.

In the process I moved all my code to PostgreSQL and havent looked back.

Re:Liked it, but don't use it anymore

[bani]

so basically your decision was a religious one and absolutely not an engineering one. nice.

Re:Liked it, but don't use it anymore

[sloanster]

Oh well, freeloaders get bitter when the free ride ends - or when they think it might end? or maybe they don't like the license for some reason? (I guess I'm not sure what the issue is, but I digress)

I use mysql and love it. Not only are the availability and price unbeatable (it ships with my OS, how much easier could it get?) but the performance is truly outstanding. Predictably though, the mysql-bashers who are stuck in 1998 will arise to tell us how mysql can't do subqueries, or has no transactional integrity, or whatever other fairy tales they might dig up at random

Why can't we just admit that mysql is a useful tool in many situations, and move on?

Re:Liked it, but don't use it anymore

[Dwonis]

Of course... because the risks associated with licensing and the law don't have any bearing on building marketable products or services, right?

Re:Liked it, but don't use it anymore

[bani]

PHP and others revolted and dumped MySQL for SQLite as the default database for PHP 5. Some could argue it was due to library mixup hell, with multiple versions of libraries on the system, but we all know the main reason was behind the license.

yeah, they picked SQLite because they had problems with MySQL and postgresql. I think it's very telling that they didn't pick postgresql.

Re:Liked it, but don't use it anymore

[bani]

if it was about licensing and the law then he should have picked sqlite like PHP did, instead of postgresql. the sqlite license is even less restrictive than postgresql's and so there's less risk.

Re:Liked it, but don't use it anymore

[Anonymous Coward]

Maybe the proper term should be ideological, not religious. Or maybe even ethical. And there is nothing wrong in making an ideological or ethical decisions. Efficiency or practicality should not be the only criteria, even though it, granted, is an important criteria.

Re:Liked it, but don't use it anymore

[Anonymous Coward]

Why can't we just admit that mysql is a useful tool in many situations, and move on?

Because there are better tools, such as PostgreSQL. And, no, there is no need to qualify "better." PostgreSQL is better than or equal to MySQL in every respect. Only an ignorant fool would chose MySQL, if he knew what his options were.

Re:Liked it, but don't use it anymore

[Watts Martin]

Did you actually look at PostgreSQL's license before writing this? They use the BSD license, which is very similar to PHP's, and has none of the issues with restriction that MySQL did. Perhaps the choice of SQLite as the default database is telling you that SQLite is very small and very fast, and if you don't need the power of a real RDBMS (which most web applications don't), it's a far more lightweight solution than either PostgreSQL *or* MySQL.

Even in the open source world, sometimes these decisions are actually made for--yes--technical reasons. :)

Re:Liked it, but don't use it anymore

[Dwonis]

Odd, I've never considered sqlite to be an alternative to PostgreSQL. At least not where there is a lot of concurrency. (PostgreSQL, like MySQL, is client/server. sqlite is a library.)

Re:Liked it, but don't use it anymore

[bani]

Yes I did. SQLite has an even less restrictive license than postgresql. SQLite has no advertising clause. postgresql does.

but i was simply responding to OP's chest thumping about how php "ditched" mysql because the license was so evil. and his smug "but we all know the REAL reason they switched, wink wink nudge nudge" comment.

to OP, PHP moving to SQLite can't possibly be due to technical reasons -- it's due to ideological ones. so he's using it to justify his ideological switch to postgresql.

as you pointed out, his reasoning is bogus -- PHP moved to sqlite for technical reasons. the zero-config of SQLite being a major one. not even 5up4r-1337 postgresql can boast that.

Re:Liked it, but don't use it anymore

[davegaramond]

... PostgreSQL is better than or equal to MySQL in every respect. ...

Here are three cases, among others, where I can't use Postgres or where Postgres is not the best solution: a) running database server natively on Win9x; b) embedded database manager; c) non-MVCC operation (e.g. due to high update+delete frequency). While these points can be argued (e.g. embedded is not the safest mode of operation, etc), there are people who want and/or need them.

There are other points/cases like availability of hosting providers, full text indexing (MySQL's is easier and works out of the box), etc. Everything has its place and there's no thing that can be better at other thing in every respect.

Re:Liked it, but don't use it anymore

[Anonymous Coward]

Just to clairfy... the reason they moved *from* my$ql was because of the licensing. the reason they moved *to* sqlite was it was the best fit technically out of the dbs that met the proper licesning requirements.

Re:Liked it, but don't use it anymore

[MemoryDragon]

Ok then send the code to the internet, the MySQL requires you to GPL all the code linked to the DB, which means you have to give the code to the public upon request under GPL... See that as a semi official request now...

Re:Liked it, but don't use it anymore

[MemoryDragon]

The only downside postgresql had at that time was that it was harder to install and no windows version was available at that time, the windows version problem is solved and besides that Postgres is a fast workhorse. I have yet to see a hosed prostgres repo...

Re:Liked it, but don't use it anymore

[Just Some Guy]

the sqlite license is even less restrictive than postgresql's and so there's less risk.

I'll grant you that public domain is freer than the BSD license. Pray tell, however, what the BSD license would prevent you from doing that public domain would allow, short of claiming that you wrote it?

Re:Liked it, but don't use it anymore

[Just Some Guy]

SQLite has no advertising clause. postgresql does.

I call your bluff. Here's the entire, unedited PostgreSQL license (source their website):

PostgreSQL Database Management System (formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2005, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Where's this advertising clause you speak of? Or did you hear "BSD license" and drag out a decade-old complaint that's long since been addressed? That's as bad as people complaining that MySQL doesn't support transactions, except that's true under certain circumstances whereas your criticism is completely unfounded.

Re:Liked it, but don't use it anymore

[Anonymous Coward]

nope

Re:Liked it, but don't use it anymore

[bani]

just because you don't consider it a risk doesn't mean someone else might not. some business models depend on misrepresenting the origin of code :)

Re:Liked it, but don't use it anymore

[bani]

provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

it's not obnoxious like the original bsd advertising clause, but you're still required by the license to toot your horn about ucb.

Re:Liked it, but don't use it anymore

[WilliamSChips]

Everything has its place and there's no thing that can be better at other thing in every respect.

But do not program in COBOL if you can avoid it.

Re:Liked it, but don't use it anymore

[bani]

because it's the latest fad, fashionable hip and trendy to bash mysql for (whatever perceived shortcomings). like bsd'ers bashing linux, it makes you leet. and it's a badge of honor. it doesn't actually matter if mysql is or isn't guilty of the things you accuse it of, as long as you make the accusations. you will be applauded anyway, because bashing mysql is so clever.

Re:Liked it, but don't use it anymore

[Anonymous Coward]

if mysql is the IKEA of databases, sqlite is like using a stolen milk crate for a chair.

Re:I can think of a pretty big plus in the column.

[Anonymous Coward]

If you're just downloading and using the software, BSD and GPL are *identical* (because you can ignore them both). Talk about how un-relational MySQL is, or how it gets in the way of a DBMS' fundamental purpose (data integrity) with it's bizarre misfeatures, but don't spread FUD about the GPL. 'kay?

Moving from one DB to another

[stevewz]

Regarding the compliance of MySQL to SQL standards and how that may affect an eventual migration to another database, all I can comment on is my own experience. In 20 years of software development, I've only been involved with, or seen, two database migrations from one product to another. Both were from SQL-Server to MySQL (on Linux). In my experience, I have found SQL-Server and Oracle to be more "locked in" and proprietary in their implementations than MySQL, and have found more applications and programming languages to support MySQL more than any other RDBMS save only MS-Access. Until 5.0, it's not been what I would consider "enterprise" because of it's lack of stored procedures and triggers, etc. but now that 5 is here, I think it can compete with the big boys.

Re:Moving from one DB to another

[Anonymous Coward]

Both were from SQL-Server to MySQL (on Linux). In my experience, I have found SQL-Server and Oracle to be more "locked in" and proprietary in their implementations than MySQL

Don't equate "more features" with "locked in". Switching from SQL-Server to Oracle, PostgreSQL, or any other real database, and vice-versa, is a hell of a lot easier than switching to a hack job like MySQL. It's not because they've locked you in, it's because MySQL simply doesn't have the same capabilities as all the rest.

Re:Moving from one DB to another

[Doctor Memory]

Amen! You wouldn't believe how many times I've had to actually pull a team's head out of the clouds and point out that they really don't need a generic database abstraction layer, because the product is scheduled to go into production with the company's standard database product (which is almost always Oracle). Ironically, my first Java gig required such a layer, as we were writing a product that had to support both SQL Server and Oracle.

If the dev team wants to develop using a different DB than production (since Oracle DBAs tend to be pretty tight-assed and don't like developers creating their own schemas), then I'd be OK with a generic DB access layer, but I've never been on a team that's tried that.

3 words...

[ninja_assault_kitten]

FULL OUTER JOIN

Hi Ken! That's for the spam.

[Anonymous Coward]

MySQL, especially version 5.0, is popping up on the radar screens of database gurus who built their reputations and book sales using other SQL databases. Ken North, who did those ODBC performance benchmarks for Oracle, Sybase, and DB2, wrote a recent article about MySQL 5.0.

Jesus, dude, if you're going to submit your own stuff at least try to hide it a bit better. This is over the top obvious to anyone with half-a-brain or more, and who's tried to spam Slashdot.

Sigh - build apps, don't be the QA department

[Anonymous Coward]

What boggles my mind is that so many people continue to be the QA department for MySQL. Why not get busy and build your $40m Internet flip project on a database that simply works. Are you a database R&D company or a software products company? You've waited years for MySQL to get features that simply should have been put there in the first place, read ACID. Yes yes, mysql is fast. It can return partial result sets based on silent index corruption faster than any other database. Wonderful.

Where is the story?

[Anonymous Coward]

So the story here is "someone wrote an article about MySQL 5"?

Changing indexes causes entire table copies!

[Anonymous Coward]

Try creating or dropping an index on a large mysql table sometime (a common requirement). It will lock the table, copy all rows to a temporary table, recreate the original and copy them back.

Even with relatively I/O beefy machines this means hours of production outage for tables with just millions of rows. A nightmare for any critical application.

I've used MySQL for a year after Sybase, Oracle and SQL Server and would definitely agree that optimisation for anything but the most trivial queries generally sucks. Personally, I would never choose it for any serious, complex transactional application.

Postgre?

[merlyn]

What in the hell is "postgre"? Do you mean "PostgreSQL"? No such thing as "postgre". It's pronounced "post gres cue ell". Or "Postgres" for short, if you really wanna leave off two letters.

FULL TEXT search still sub-standard in mysql

[Anonymous Coward]

I love mysql, but it is lacking many fulltext searching features found in oracle such as stemming and word proximity just to mention a couple. Oracle is far more advanced than MySQL when it comes to fulltext searching. In addtion the InnoDB engine does not have any fulltext searching capabilities at all.

This is my only gripe about MySQL. Otherwise I love it.

5.0 is Mission Critical, eh?

[xxxJonBoyxxx]

The article profiles mission critical database software and discusses how well MySQL 5.0 fits the profile.

I love MySQL - I've built my technical solutions around it for the past 5 years. However, until it's released for PRODUCTION use you can't call it "MISSION-CRITICAL".

See http://dev.mysql.com/doc/mysql/en/choosing-version .html

Um, no, not really

[Anonymous Coward]

For instance, it's very desirable to have everything in a stored procedure. Not only this gives a performance benefit, but it also increases security. Building your database this way you can ensure that nobody will ever be able to insert bad data.

As somebody who maintains the database abstraction layer for a very complex enterprise application platform that runs in Oracle, SQL Server, and DB2 (the latter of which I was the primary porter), I don't think you're right at all on this point. It is possible to go really, really far by using nothing but SQL standard features, and factoring common features with different syntax into a pretty thin database abstraction layer.

First of all, you're mislabeling what you describe as "security" (which properly refers to controlling access to information); the term you have in mind is data integrity (which refers to protecting data from invalid inserts from authorized parties).

Second, constraints != stored procedures. You can add constraints to a schema without having to write a single stored procedure.

The trick to maintaining cross-database compatibility is to avoid requiring behaviors that are only implemented in one database (you may only use such behaviors optionally), and never ever hardcoding database-specific SQL syntax into any part of your application; instead of the latter, you delegate construction of database-specific SQL statements to a software component that knows how to translate your request to the database you're connected to.

Re:Um, no, not really

[vadim_t]

I know the difference, and those two sentences are supposed to be separate. One is security, the next one is integrity, which I simply didn't mention explicitly. Sorry if it was unclear.

Constraints aren't always good enough. Sometimes you might want to make sure that an operation is always done in a single step. For instance, perhaps I have a table with statistics of some kind, and want to make sure it's always updated in some specific case. A constraint can't force users to always update a second table when they update the first, but it can be kept up to date with a stored procedure or trigger. Granted, it's not a frequent use, but it does come handy sometimes.

To put a more practical example. I can use a trigger or a stored procedure to ensure a column contains the latest date when the price for an article was changed. A constraint can keep me from introducing a negative price, but it can't force me to update the timestamp. Some databases have timestamp columns that are updated automatically, but perhaps I want it to be done only when the price changes.

As an example of a security related use, here all stock changes go through a stored procedure, no other way to do it. The stored procedure makes the change and logs it.

This is general through the whole database. Stored procedures are used to log what was done by who and when. For instance, a stock change is usually done due to a new or changed order row, which is part of an order for a client, done by a specific person on a specific computer.

Re:Um, no, not really

[Anonymous Coward]

Constraints aren't always good enough. Sometimes you might want to make sure that an operation is always done in a single step. For instance, perhaps I have a table with statistics of some kind, and want to make sure it's always updated in some specific case. A constraint can't force users to always update a second table when they update the first, but it can be kept up to date with a stored procedure or trigger. Granted, it's not a frequent use, but it does come handy sometimes.

I would argue that your schema is not normalized, then.

Re:Um, no, not really

[vadim_t]

There's nothing wrong with a bit of denormalization as long as it's not overdone.

For instance, the logging tables are perfectly normalized, except for one thing: The log has a tree structure, and events hang from each other. In order to improve the performance of the log viewer, when an event is attached to another, a bit is set in the parent to indicate it has children.

Now, since logging is done only through a stored procedure, which puts all of that inside a transaction, it's impossible for anything to get out of sync unless I login as an admin into the database and change it myself.

Could I live without that? Sure. But why have the database do more work than is necessary? Without this bit, the log viewer would need to search the table for inexistent branches.

propaganda

[kpharmer]

I've read the article, am familiar with the product, and am also familiar with data warehousing. This is just propaganda.

> MySQL has a demonstrated capacity for managing very large databases. Mytrix,
> Inc. maintains an extensive collection of Internet statistics in a one
> terabyte (1 TB) data warehouse that contains 20 billion rows of data.

MySQL is a horrible product for warehousing: no query parallelism, no partitioning, primitive memory management, primitive optimizer, etc, etc. 20 Billion rows in a single table? What would mysql do with that? Any typical warehousing queries would take hours to come back. More likely either the database owners are just logging data someplace cheap, or they are creating hundreds of much smaller tables.

> It replicates 10-60 gigabytes per day from its master database to a MySQL server farm.

Ah, a 'server farm'. So, the 20 billion rows are probably spread across dozens of mysql servers. This explains it all. Even SQL Server can do better than that.

> The MySQL databases are used to support a shopping application that can accommodate a million fare changes per day.

Yeah, I think db2's most recent benchmark was for something like 3 million transactions a minute.

This is little more than an advertisement for mysql. A little poking around would probably show that he's on the mysql payroll.

Re:propaganda

[Anonymous Coward]

I just gotta say, if the sum total of your data warehouse (ANY data warehouse) is contained in a single table, you have much bigger issues to face than the DBMS choice. Like why the hell you let the application developers or business analysts design the warehouse.

Re:propaganda

[kevinadi]

I agree that it sounds like propaganda. However I doubt he's on MySQL payroll.

Anyway you're right. MySQL actually doesn't do very good if a table has a million entries. It may seem fast at first, but try to do a select on a table with a million entries, joined with itself three times, and do a group by command. It's gonna take a while to finish. Granted, it's minutes instead of the usual seconds, but I would tend to think that commercial databases would do better than that.

IMHO MySQL is fine as long as you don't do anything challenging. As for me, I'm happy that in spite of taking minutes, it's giving me the correct answer. That's all I need and I don't think it's fair to directly compare MySQL to a $20,000 database. Of course it won't perform as good. It's like comparing a Toyota to a Ferrari and complaining that the Toyota won't go as fast.

Re:propaganda

[Anonymous Coward]

You've confusing a server farm with a data warehouse, or have misread the article.
>> Mytrix, Inc. maintains an extensive collection of Internet statistics in a one terabyte (1 TB) data warehouse that contains 20 billion rows of data.

• More likely either the database owners are just logging data someplace cheap, or they are creating hundreds of much smaller tables.

>> It replicates 10-60 gigabytes per day from its master database to a MySQL server farm.

• Ah, a 'server farm'. So, the 20 billion rows are probably spread across dozens of mysql servers. This explains it all.

Not really. You're using stats about the airline application and server farm at Sabre to reach your conclusion about the data warehouse. Different applications, different companies.

Re:propaganda

[Anonymous Coward]

I agree that it sounds like propaganda. However I doubt he's on MySQL payroll.

Most likely not. He's just another open sores zealot who hasn't discovered there are better OS database offerings..

Re:propaganda

[mattcasters]

Sure, how about comparing MySQL to other free & open-source databases.
PostgreSQL, Firebird, MaxDB and Ingres all handle the milions of rows *a lot* better.
Especially PostgreSQL seems to stay on par with Oracle, even offering (primitive) support for table partitioning, bitmap indexes etc.

If I would try to do data warehousing on an open source database, it's probably going to be PostgreSQL.

Re:propaganda

[grazzy]

Anyway you're right. MySQL actually doesn't do very good if a table has a million entries. It may seem fast at first, but try to do a select on a table with a million entries, joined with itself three times, and do a group by command. It's gonna take a while to finish. Granted, it's minutes instead of the
usual seconds, but I would tend to think that commercial databases would do better than that.

I do exactly this on a database that is around 400 mb large (without the indexes). There are no "minutes", theres seconds. Time to upgrade that 400mhz.. I run around ~6 of those queries / seconds on a Opteron 248 atm. And it's at ~25% load at peak.

Re:propaganda

[psbrogna]

I can vouch that mySQL scales unexpectedly better than at least one major commercial db. I had a process get out of control on a low end server that resulted in my coming in to work one day to find a table that accidently grew to 40 million rows. Out of curiosity I ran some queries on it and was pleasantly surprised at the responsiveness. This was a production server that experienced no instability during this time. MS SQL running on a h/w twin server would have gagged and fallen over backwards had the same thing happened. So while I can't speak to the scale refered to in the article, I can say that in the world of low end intel h/w, I get ALOT of bang for my buck with mySQL stability handling relatively large db's.

Re:propaganda

[psbrogna]

Most of us need Toyota's.

Re:propaganda

[kpharmer]

> As for me, I'm happy that in spite of taking minutes, it's giving me the
> correct answer. That's all I need and I don't think it's fair to directly
> compare MySQL to a $20,000 database. Of course it won't perform as good.
> It's like comparing a Toyota to a Ferrari and complaining that the Toyota
> won't go as fast.

Sure - if it only takes a few minutes every now and then that might not be a big deal.

But regarding the cost of the alternatives:
    - postgresql is freer (no way of having to face $600/year charges as with mysql), and has a better optimizer.
    - oracle is only around $1-2k for a small footprint database. this wouldn't
        include partitioning, but at least you'd get a smarter optimizer.
    - db2 is around $700 for a 2-way server. this would provide partitioning
        (via mdc or union-all views), parallelism, and a better optimizer.
    - sqllite is freer (again, no cost), and I suspect has a better optimizer.

So, for $700 once, plus maintenance (18%/year?) you'd have a vastly faster product than MySQL - which can cost you $600/year. And you don't need to talk to a lawyer to figure out whether or not you need to pay for mysql. Or you could get other products that are faster with complex queries and are completely free.

Re:propaganda

[Donny Smith]

>> It replicates 10-60 gigabytes per day from its master database to a MySQL server farm.

>Ah, a 'server farm'. So, the 20 billion rows are probably spread across dozens of mysql servers. This explains it all. Even SQL Server can do better than that.

Yeah, that's hilarious. mySQL should be ashamed of themselves.

And 60GB/day is not much at all - that's about 700 kilobytes per second; two 8-way SQL server servers could replace their "farm" of mySQL servers (judging by the size of replicated data).

mySQL makes me sick.

Re:propaganda

[MemoryDragon]

Heck, once you do subselects joins etc... and use transactions heavily, postgres beats mysql by a mile.... Ah yes, MySQL is enforced 500 Dollars upfront or GPL, Postgres is BSD...

Re:propaganda

[Anonymous Coward]

I do exactly this on a database that is around 400 mb large (without the indexes).

Come back and post again when you have a database that can't (by a factor of 100 or more) fit into main memory.

Re:propaganda

[Anonymous Coward]

That's interesting.

I used to manage a web search database with 50 million rows at about 120 gbytes on SQL Server 7 & 2000 on a dual-cpu 700 mhz HP box. And it ran just fine. Most searches came back in a second.

Now, those searches had to use carefully planned indexes. If the dbms were oracle or db2 I would have let the searches use any column people wanted - and would have still gotten great performance. Still, there was no performance issues.

down with MySQL

[Philodoxx]

MySQL is good for a very limited subset of projects, namely stupid web applications. MyISAM tables don't even support referential integrity! MySQL is also good for getting the hang of SQL queries, but anybody who has used databases for more than a year should "graduate" on to something a little more mature... enter PostgreSQL.

While PostgreSQL doesn't offer the insane (but inherently flawed) performance of MySQL, it is a standards compliant, free as in speech, database system. The windows version is rediculously simple to set up, and it opens the doors to things that MySQL is only promising at this point.

Friends don't let friends use MySQL!

Re:down with MySQL

[linuxhansl]

While PostgreSQL doesn't offer the insane (but inherently flawed) performance of MySQL

I have used and tested both; for *every* query that I tried PostgreSQL is either faster or as fast as MySQL. For some complex self-joins for tree-queries PostgreSQL finished within a few seconds where I stopped MySQL after 30mins (and yes I check all the indices, etc).

Since PostgreSQL has to spawn a new process for each connection - as opposed to MySQL which only spawns a new thread - there extra overhead involved in opening and closing many connection which is typical for web application. However, serious folks leave that up to a connection pool which completely eleminates the problem.

Re:down with MySQL

[Philodoxx]

I was assuming that MySQL was using MyISAM tables when I said it was much faster. If using InnoDB, then MySQL is basically the same speed give or take.

Only Problem

[Master of Transhuman]

As usual, little of MySQL, as well as most other DBMSs, properly supports the relational model, according to Chris Date and others.

It's nice to see MySQL getting stuff other DBMSs have had for years, like triggers and stored procedures, but for uses other than a replacement for file managers like Access and for DB-backed Web sites, go with PostgreSQL or Firebird or others with a little more power. Messing with database designs for mission-critical needs can land you in hot water fast with a product that doesn't support even the basics adequately.

Re:I can think of a pretty big plus in the column.

[NineNine]

Postgres isn't available on 80% of web hosting firms and 90% of off-the shelf web scripts (that require a DBMS). (I wish it was)


I haven't seen a lot of web sites that are on a shared server with shared scripts that needed much more than MySQL already offers. MySQL does fine for blogs, shopping carts, etc. That's all *very* basic stuff, most of which MS Access could handle if done correctly.

not Postgres

[cpeterso]

Actually, "Postgres" is was a precursor to "PostgreSQL". The database started as a university research project called Ingress. A follow-on version was called Postgres (i.e., Post-Ingress). SQL support was added later; thus PostgreSQL (Postgres + SQL).

I think not

[vandan]

MySQL are finally bringing stored procedures, views and triggers to their database server. Cool. I've been using MySQL for 6 years now, and I've very happy with the version I'm currently running ( 4.0.25 ).

Having said that, anyone who says that MySQL are ready for 'prime time' are clearly deluded. You can have a database server with unbelievable speed, features, security and stability, and it doesn't mean a damned thing if you don't have client libraries ready.

MySQL's client libraries are appauling. MyODBC, their ODBC connector, has been one big fuckup after another for the past 2 years. It's a minefield of:

don't use this with that, and certainly don't download this one - we don't know what we were smoking when we released that

Rock up to a MySQL mailing list, and the most common questions is about client libraries and the 'new' authentication system. The problem is that this authentication system is no longer new - it's old. It's many years old. Why haven't the client libraries been updated? The error message suggests that users "upgrade their client libraries", but upgrade to WHAT? Perhaps the error should read:

You are using client libraries from last century. Perhaps you should match them with a server product from last century. Please don't use our newer server products until we have managed to release some client libraries to match

I for one would prefer to see some actual client-side support for 4.1.x before people start declaring 5.0.x 'ready for prime time'. You can't use 5.0.x features with 4.0.x libraries.

Has anyone checked out the GUI admin tools? These are also a long chain of distasters. MySQL seem to spend 18 months getting a GUI looking promising, if a little buggy, and then abandon the project. What happened to mysqlcc? What's happening with Administrator / Query Browser? Critical bugs reported months ago have gone completely untouched. For example, you can't edit tables with a primary key, because Administrator doesn't recognise the primary key, and strips it out of the table when you click 'apply'. Cool! Sounds ready for prime time to me! When will MySQL add support for primary keys to their products?

Yes, I'm stirring here. But none of the above is in the slightest untrue. MySQL have lost their focus. With so much attention being paid to a 5.0.x release, everything else is suffering badly.

Re:I think not

[MemoryDragon]

Ahh and there still is the issue, that the MySQL devs refused to add transactions for years ditto for subselects because you do not need em :-) thing of the past, but that attitude alone was enough not to touch this system.

Re:I think not

[treerex]

MySQL's client libraries are appauling. MyODBC, their ODBC connector, has been one big fuckup after another for the past 2 years.

The horrible JDBC support with MySQL was the final straw for me, and what pushed me to PostgreSQL for all of my new database backed applications. The PostgreSQL JDBC drivers work really well, especially in the presence of Unicode text data. I've heard that more recent MySQL releases have better handling for this, but it's too late for me.

Has anyone checked out the GUI admin tools? These are also a long chain of distasters.

Amen. Whenever I've used MySQL the second thing I install is phpMyAdmin. It just works, and it just works well.

Re:I think not

[Sentry21]

Yes, I'm stirring here. But none of the above is in the slightest untrue.

Except for the part about needing 5.0 client libraries to access a 5.0 server - you can use MySQL 3.23 client libraries to connect to MySQL 5.0 servers if you want. The only caveat is you have to enable old_passwords=1 in the config file, or set your user's password with OLD_PASSWORD() instead of PASSWORD() so you can use the older (less secure) authentication system.

Oh, and except for the part about MySQL not having released updated client libraries - it's just that no binary distributions compile against them, missing out on the more secure authentication mechanism. If you want PHP on Debian to be able to use the more secure protocol, apt-get install the latest 5.0 server and dev libraries, then apt-get -b source php4 and voila! No more error.

As for MySQL Administrator - I've been able to edit tables with primary keys without trouble - did one just now with version 1.1 as well. Perhaps you don't know how to update your applications just like you don't know how to update your client libs? Not sure what the problem is.

And just as an aside, the reason the hot topic on the mailing lists is the authentication protocol is because people are too dumb/lazy/ignorant to read the manual, which tells them everything they need to know (or maybe they just assume that MySQL, like most other OSS apps, has shitty documentation).

You based your whole rant around something which was blatantly untrue. How does that feel?

Re:I think not

[mishehu]

Yeah, I noticed that whole MySQL client lib issue with my distros' (Slackware and Slamd64) builds of QT. I use MythTV, which requires a database, and without a lot of tweaking the QT that had a mysql module built against 4.0.x would simply not work with server 4.1.x... So I found it easier just to rebuild the mysql module and try again. Works fine for me.

And ever since 4.1.x came and so did prepared statements, I've never looked back... And now I'm eager for 5.0.x to stabilize a bit before I switch up to it...

It's a shame that people go on a rant without checking their facts. Then again, if people did, would this actually be Slashdot then? *grin*

Re:I think not

[vandan]

As for MySQL Administrator - I've been able to edit tables with primary keys without trouble - did one just now with version 1.1 as well. Perhaps you don't know how to update your applications just like you don't know how to update your client libs? Not sure what the problem is.

Oh if only it were that simple. Yes I do know what I'm doing. And no it's nothing to do with client libraries. The Administrator and Query Browser packages have their own client libraries included. I submitted a detailed bug report - months ago - and many people have posted 'me too' posts. I will send you a screenshot if you still think that I'm simply incompetent ... but I assure you that I'm not. Seriously - Administrator doesn't recognize primary keys. Perhaps you are only using Windows, which doesn't seem to be affected?

You based your whole rant around something which was blatantly untrue. How does that feel?

Well ... I admitted in my 1st post that I was stirring. However the part about it being blatantly untrue is ... blatantly untrue.

It feels like I've hit a raw nerve with a MySQL newbie who can't handle their favourite project taking some honest criticism.

It feels like there are some pony-boys who have little to no experience with MySQL but are more than ready to stand up and talk about it because they want to fight the nasty trolls who attack all that is good. Read my post again. I use MySQL. But I use it because I'm comfortable with my ability to deal with it's problems. It's not ready for prime time. Cry over it, but deal with it.

It feels like you should check out the bug: http://bugs.mysql.com/bug.php?id=11415 and try to reproduce it and fix it, if you're so fucking knowlegeable.

If feels like you've made a complete ass of yourself.

How does that feel?

One point where you're wrong...

[Anonymous Coward]

I'm agnostic with a preference for Postgres over MySQL but your comment on how vocal the MySQL community is in comparison to the Postgres community is wrong.

Hit the back button a little and take a look for yourself. Any mention of MySQL brings out the Postgres community to talk about MySQL's known short-comings with a passion. It's not as bad as the Linux commentary on the Microsoft articles but it blows the Linux commentary on the BSD articles out of the water!

MySQL Everywhere, doh!

[David Off]

One thing that annoys me about MySQL is the way OS programmers drop it into nearly every project that needs to store something in a structured, but not complicated or relational way. I don't like the new licensing or the SCO deal either.

As an early adopter of Postgresql (I even made a port to Windows back in '97 sometime) Postgresql seems to be the way to go if you want free. It supports a lot of stuff you would expect to be there in a professional RDBMS. We were able to port our app from Oracle to Postgresql and a lot of the stuff mapped over. My boss was happy no longer paying Oracle licenses anyway.

If you are doing Java and want some simple SQL/RDBMS support try HSQLDB.

From personal expereince...

[g_lightyear]

26 million rows = broken MySQL system.

It just doesn't cope. It's fine if you've got no data to speak of; it's great when the sizes of what it's working with is small.

IT TAKES 24 HOURS OF UNWRITABILITY TO MAKE A DAMN BACKUP, FOLKS.

MySQL was the biggest mistake I ever made. I had the option of choosing Postgres on an older version of the software, or MySQL on the latest, and I've been regretting it ever since.

The fact is this: I've used it for stuff when the amounts of data are small, and it's brilliant - but if you need to keep a lot of information, you're screwed - run, don't walk, to the nearest vendor and get something decent, because MySQL just can't cut it. It's missing too much in too many places.

Now, I haven't used 5. I'll have to, because 4 sucks, and 5 can't be worse - I can only hope that 5 gets rid of the worst of my problems; it will probably stay slow and unresponsive, and continue to take an hour to generate a report, but I can at least pray that perhaps, if I'm lucky, I can get a backup out of it without taking down the system.

No matter how good you think it is; no matter how fast you think it might be... don't pretend it scales up to the kinds of loads the commercial vendors can handle. There's a reason the big boys cost big money, and despite popular opinion, it's not all just leeching money out of your pocket. MySQL doesn't do big well.

Re:From personal expereince...

[Just Some Guy]

IT TAKES 24 HOURS OF UNWRITABILITY TO MAKE A DAMN BACKUP, FOLKS.

A FreeBSD method:

# /usr/local/etc/rc.d/mysql-server.sh stop
# dump -0aL /var &
# /usr/local/etc/rc.d/mysql-server.sh start

Total downtime: maybe 10 seconds. The "-L" argument to dump makes a filesystem snapshot, and runs the backup against that snapshot. It's probably not the ideal you were hoping for but it'd certainly help your availability.

History repeats itself:1st linux, now mysql

[Anonymous Coward]

I remember the days of Slackware and Linux version 1.x kernels, everyone said it was a joke and I thought it was a great learning tool. Then came version 2.0,2.2 kernels and everyone said it was a toy and not a real Unix. I felt it needed multitasking and a better filesystem. But in 2000, it was almost ready for primetime. Now its everywhere.

I remember the blah blah blah arguments regarding SGI workstation graphic superiority, and Solaris server reliability.

Now Mysql is still not primetime, but guess what, someone in college can learn it for free and use it for free.

And I always felt that Linux wasn't that big a cost savings over commercial Unix. But Oracle costs big $$$ compared to Mysql. I would definitly use mysql and php for an application one stop below Enterprise level software development. And the tide is rising.

WhatMeWorry

Re:History repeats itself:1st linux, now mysql

[Anonymous Coward]

Now Mysql is still not primetime, but guess what, someone in college can learn it for free and use it for free.

MS SQL Server, DB2, and even Oracle have free versions for learning.

Not to mention PostgreSQL and FireBird.

An honest question.

[jez9999]

Why is PostgreSQL not as well-known/popular as mySQL? Whenever you hear of an OSS database, it's almost always mySQL, mySQL, mySQL. Especially on Slashdot. Hoenstly, I don't get it, it's not very good now, it's been even worse in the past; why is it so popular if it's that much worse than PostgreSQL? DB lock-in, momentum? Seriously???

Re:An honest question.

[Anonymous Coward]

In a word, marketing.

Totally wrong scale

[Anonymous Coward]

400MB, 1 million records does not make a data warehouse project. 400MB can run 100% in a servers memory and hence doesn't need any complex logic to run fast. Sequential scans on an Opteron could easily rip through 400MB in microseconds -- any DB server could process that even without indexes.

When people say data warehouse, they usually mean 100GB minimum, often in the terabytes, and billions of rows. When dealing with data in this scale, the query optimizer becomes paramount because there's no way you're fitting the entire DB in memory.

Re:Totally wrong scale

[grazzy]

Ofcourse, but fitting a index for 1 million rows in any servers main index is a no brainer. I know perfectly well (thank you) that my entire index and data fits into the ram. That how my system is designed.

The GP complains about 1 million rows, I dont see how that a index of that row cannot fit RAM.

Re:I can think of a pretty big plus in the column.

[petermgreen]

mysql basically tries to insist (with a few special exceptions for things like php that are so big they don't dare fuck them over) that if you build your app to use mysql you either havfe to GPL it or pay them.

they do this by making the client access libraries GPL. whether linking against a GPL lib dynamically brings your app under the gpl is not very clear but there are enough people saying that it does that most would rather not take the risk.

theres also the issue of what distribution is, is distributing within a company enough to count?

It's simple

[jocknerd]

MySQL is owned by a company, MySQL AB. PostgreSQL is an all-volunteer organization, sort of like Debian is to Linux. Sure, there are a few companies that work with PostgreSQL, such as Command Prompt, but they don't control the direction of PostgreSQL. MySQL AB controls all aspects of the MySQL database. Plus, being a company, they have the money to promote it.

This is one of the reasons why it is so much more popular than PostgreSQL. Another reason is that around the time of PHP taking off, 1998 or 1999, MySQL was emerging, while PostgreSQL was still in version 6.x. PostgreSQL was going through a complete code cleanup and rewrite so it was optimized at all. Therefore it performed much slower than MySQL. It has since closed that gap, while being a more robust database. PostgreSQL and MySQL actually took different development routes. PostgreSQL wanted to add the features to make it a world class database and optimize it and add speed later, while MySQL went for speed first and is now trying to add the features.

MySQL has always reminded me of how Microsoft works. Make it just good enough for the masses and then try to add enough to it to please the experts in the field.

MySQL AB = Proud SCOX business partner

[walterbyrd]

At first I thought that scox was just spinning, as scox so often does. I figured scox was calling basic mysql being supported on scox's OS a business relationship.

Then I see the announcement of the business partnership proudly displayed on mysql ab's website.

Disgraceful.

One more thing

[jocknerd]

Let me also add that MySQL had support for Windows long before PostgreSQL. This is another reason for its success over PostgreSQL.

WTH, I like MySQL so flame me

[neelm]

Always the same, MySQL sucks because it lacks the feature, or doesn't do well with a brazillion records. Unless you work for NASA or some such, why the hell do you have a table that big? STOP LOGGING APACHE WITH MYSQL. Oh wait, I've done that with no trouble using MySQL.

I'm a good programmer. I unit test, I regression test. I alpha, I beta, I gamma. I dig hip MVCrack and n-teir joints. I believe in a world where my data layer hold only data, and logic live in it's own house.

So why does everyone tell me I should put logic in my data layer? Even the idea of transactions... letting my data layer tell me it's all okay, makes me shiver. How does it know it's all okay? Because I sent it a float, string and zipcode? I get the same feeling in my tummy when I see perl code using a regex to verify user input.

Am I the only one who sees a select joining a table 3 times over, a sub-select, a stored procedure and a group by all in one statment and thinks, we should look at the schema and code and see where we can fix this?

Don't get me wrong, I think Oracle is pretty neat. I just think we have these academics arguing their vision of a database to actual developers, and we live on different planets. (academics are like life on mars, we think they are living things, but have no proof yet).

Re:WTH, I like MySQL so flame me

[Anonymous Coward]

So why does everyone tell me I should put logic in my data layer? Even the idea of transactions... letting my data layer tell me it's all okay, makes me shiver. How does it know it's all okay? Because I sent it a float, string and zipcode?

Sounds like you haven't a clue as to what transactions, referential integrity, and all that "stuff" are for and why they are frequently of paramount importance in a database - to the point that where those things are at least as important as the data itself. Here's a hint: data is worthless if it isn't taken into context properly (you have no referential integrity).

Anybody use SQLite? How does it compare?

[walterbyrd]

I haven't tried sqlite myself. But I think it looks promising.

What about the JDBC side of things?

[kalirion]

When the use of a PreparedStatement in a loop is noticeably slower than a Statement, you know something is wrong.

Re:I can think of a pretty big plus in the column.

[archen]

You might want to read up on the GPL which only affects you if you distribute the code in question. You can take a GPL app and screw with it until the cows come home within your own company. Distribution referrs to the level where the development happens. If I do it as an individual developer, I cannot give it to other individuals without the GPL. As a company, I cannot give it to those outside the company. If you're a company that simply wants to _use_ GPL applications, you are pretty much unrestricted in what you can do with them. I think if more companies realized this, they'd be more friendly to the licence.

Re:I can think of a pretty big plus in the column.

[arkanes]

theres also the issue of what distribution is, is distributing within a company enough to count?

The traditional GPL response is no, but this is not in line with copyright law. Quick, buy software on a corporate credit card and give copies to everyone in your agency.

The "internal distribution" distinction is in the GPL commentary, but not in the GPL itself. The FSF would be estopped by it from going after people who distribute internally, but as far as I know nobody else who distributes under the GPL would be, as long as they didn't make a similiar statement.

Don't Forget The Bottom Line

[stan_freedom]

Every time a MySQL post appears on /., the DB purists gleefully dump cold water (or boiling oil) all over the uninformed masses who continue to use MySQL despite the sage advice of the technical elite. I am the sole IS guy for a small wholesale business (25 heads, $10M sales). I have used homegrown LAMP apps for the bulk of our business processes since arriving at the company 5 years ago. MySQL has been the backend since day one, and has performed flawlessly.

"But you only have a few GB of data" the purists decry. To them I reply, "That insignificant amount of data fuels $10 million a year for the economy and makes a paycheck for 25 families, all for the low cost of nothing".

Your customer doesn't care what DB you use. The only thing they care about is the cost/delivery/quality of your company's end product. Yes, MySQL is the bicycle of DBs, but if all you need is a bicycle, why force your company to buy a car. If your requirements grow... get a fleet of bicycles.

The moral of the story is that the bottom line IS the bottom line.

Re:Don't Forget The Bottom Line

[Aldric]

I can't figure the database purists out. I'm an application programmer and I can't say I really care what database I use as long as the client libraries are fairly easy to use. MySQL works well, the C API is easy, so I use that. I can't stand stored procedures/triggers because I want my logic in the application. Now, I can understand if they are DBAs, DBAs have lots of time to argue about different databases when they are pretending to work.

Re:Don't Forget The Bottom Line

[musicmaker]

Not counting the $600/year you have to PAY to MySQL to license it compared to the $0/year for Postgresql .

After all, it is the bottom line.

Oh My

[Anonymous Coward]

Yeah to Sum up : small websites (small data), great - mySQL is easy and quick (Linux Apache MysQL and PhP works a treat) Anything mission critical .. nah. prefer the nice warm MS management tools, finer grained locking, easy backup / restore, parrallelism... yada yada.. worth spending the money on ..really it is.

less headaches with a proper RDBMS i tell yas

I'm not thinking about a fork of the origianl code

[h15n]

Do you know about relations between GNOME and Sun? I think this could be something like that one. If Sun choose PostgreSQL, it will help it to grow and become better, because they don't want to be far from ``mainstream''.

Saving $40 million has some benefits

[Jamesday]

>> is never going to be cheaper than

Sabre according to the MySQL site: "estimated a 40% TCO savings when budgeting for the $100 million project. In many cases, the ongoing TCO savings are expected to be up to 80% - twice what was estimated".

Any corporate IT manager who isn't interested in the prospect of saving $40 million in costs and improving results through open source seems quite likely to find their company out-competed by those who do.

My own background is for a little place which is now serving about one in every thousand web pages viewed in Alexa's sample; and with some database servers down still showed billion query per day capability on a handful of MySQL database servers. About 3,500 pages per second (bad Slashdotting is about 600 pages per second). A few hundred gigabytes of compressed data, in the terrabyte range without.

The worlds in the process of changing and those who want to keep up need to be moving fast.

Re:Saving $40 million has some benefits

[kpharmer]

> Sabre according to the MySQL site: "estimated a 40% TCO savings when budgeting for the $100 million
> project. In many cases, the ongoing TCO savings are expected to be up to 80% - twice what was estimated".

Yeah, but keep in mind that a case study from technical staff in the trenches almost always looks different than a case study from senior management: in which case the motivation is always political. This is why you never heard senior managers admitting mistakes in these "case studies": they're simply propaganda. Often the TCO includes the benefits of a new system - which every vendor involved tries to reinterpret as savings caused by their involvement.

This isn't much different than case studies in which linux turned out to be far cheaper than solaris, etc. But it turned out that they compared nothing besides the cost of hardware and software: the actual labor costs actually increased costs rather than reduced them (since more servers were required).

> My own background is for a little place which is now serving about one in every thousand web pages
> viewed in Alexa's sample; and with some database servers down still showed billion query per day
> capability on a handful of MySQL database servers. About 3,500 pages per second (bad Slashdotting is
> about 600 pages per second). A few hundred gigabytes of compressed data, in the terrabyte range
> without.

Sounds great. Still not even nearly record-breaking: db2's most recent benchmark was for 3 million *transactions* per minute. And that's on a single database that won't have any problems with data synchronization or bizarre problems with data quality.

The fact that a load-balanced array of mysql servers can provide substantial read-mostly content is great - and has some interesting applications. Of course, you could plug *any* database into the same architecture with similar success. And of course, it has its own limitations: i recently looked at doing this for a massive reporting environment - but it was too much of a pain to keep all server 100% in synch. Plus, the scalability wasn't as good as if I just spread the data across the individual nodes beowulf-fashion - which is built-into db2.

> The worlds in the process of changing and those who want to keep up need to be moving fast.

yep, and they need to know more than just what a single vendor has to say - whether that vendor is microsoft, oracle, or mysql; and they also need to know what the best practices are for their technology - not just the favored vendor work-arounds of the day.

Well, that's 50% of Enterprise out the window

[ACORN_USER]

...except for Java and XML integration

Unless the vulcans can see some logic in this, I'm not sure that the Enterprise will be MySql'ing for a while. That said, I've often advocated and used MySQL in large organisations. It's something which you can get away with for anicilary applications - and to be honest, I've used MySQL through Java, without any issues. Further, shouldn't the XML layer be higher up the hierarchy, being interfaced with the DB further down your code tree? Perhaps I'm still flying around with Captain Archer and don't really get why MySQL is poor on Java and XML integration?