MySQL Readies Release Candidate For 5.1

Anonymous Dolphin writes "MySQL has released plans for a final RC for the MySQL 5.1 server. Monty Widenius, the CTO and founder of MySQL, has put up a request for more feedback from the community. You can get the latest RC here. Please help with the testing of 5.1 and report your bugs here."

Comments

Do people trust this project anymore?

[Anonymous Coward]

With fears of a license change and close-sourcing, why shouldn't I buy some PostgreSQL documentation and start learning to work with the other major project?

Re:Do people trust this project anymore?

[Majik Sheff]

I was going to say something to the effect of "In B4 PostgreSQL troll". Alas..

Every time an article mentions MySQL some asshat chimes in to support his/her favorite pet DB. Postgres seems to have the most vocal zealots, and I'm tired of it.

Please give it a rest.

Re:Do people trust this project anymore?

[Bill, Shooter of Bul]

You haven't kept up. Sun stated that nothing was going to change with the license. The "closed source" portion had already been released under the gpl and Sun said it would stay that way. In Fact they just moved from the closed source bitkeeper to bazaar for source code control, allowing anyone to track their progress.

PostgreSQL is a fine Database as well. MySql just seems to be used more in web environments.

Re:Do people trust this project anymore?

[Anonymous Coward]

An overrated mod is only available because it makes people feel less childish than marking "-1: I disagree with this" or "-1: This offended me".

Well your original post was overrated, when it had a score of +2 Insightful.

I can't see anything Insightful about your original post, just whining. Let the moderation system do its work and stop bitching about it.

Re:Do people trust this project anymore?

[Anonymous Coward]

When you see children playing in the street, almost getting hit by cars, pointing out their behavior is not zealotry.

Most PostgreSQL "zealots" are actually educating people that superior options exists; especially considering PostgreSQL's progress over the last couple of years.

So please, stop playing in the street and get your own ballpark known as PostgreSQL. ...and get off my lawn! ;P

Re:Do people trust this project anymore?

[outZider]

I think the only reason people post is because of how big of a deal people make MySQL... even though it's slow, unreliable, difficult to scale, and yet, people flock to it.

Yay, community, I guess.

Re:Do people trust this project anymore?

[tinkertim]

It was interesting to see Sun's reaction.

Apparently, MySQL AB (prior to purchase) were the ones contemplating making the move to more proprietary tools. It was set in motion and left on the table, then Sun purchased them.

Sun basically said "We have no need to put this in play, we don't make our money from a single product like MySQL AB did .."

A lot of people Criticized Sun for the idea, however the idea was the brainstorm of MySQL AB, not Sun.

Re:Do people trust this project anymore?

[Anonymous Coward]

That's because MySQL switched from LGPL to GPL.
So there is a void to fill to use an open-source db in a proprietary app.

MySQL is in the same boat as QT.

Re:Do people trust this project anymore?

[Majik Sheff]

Your response was condescending and presumptive. I never said I favored MySQL or any other DBMS for that matter. Your snotty little comment about playing in the street only reinforces my already negative view of Postgres zealots.

It doesn't matter how right you are about whatever it is. Like it or not, how you deliver your message is as important or more important than the message itself. This applies to just about anything: databases, operating systems, which end of a softboiled egg you eat first, and of course, politics.

You zealots have a lot in common with Ron Paul supporters. Any merit your cause might carry is completely nullified by the overwhelming nature of your enthusiasm.

I'm done now. I will never comment on this subject again, mainly because I'm tired of burning karma on ACs, but also because I know that nothing I say matters to anyone on either side.

Re:Do people trust this project anymore?

[Anonymous Coward]

I was going to say something to the effect of "In B4 Windows troll". Alas..

Every time an article mentions Windows some asshat chimes in to support his/her favorite pet OS. Linux seems to have the most vocal zealots, and I'm tired of it.

Please give it a rest.

Re:Do people trust this project anymore?

[Anonymous Coward]

YHBT YHL HAND

Re:Do people trust this project anymore?

[GooberToo]

Your response was condescending and presumptive.

Your response is paranoid and childish. Sometimes you have to hear things you don't want to hear. The AC's response is actually funny and accurate. By in large, most MySQL users are children when it comes to relational databases. You don't have to be a PostgreSQL zealot to recognize that fact. Simple fact is, MySQL is the low rung on the SQL ladder. All DBAs worth respecting understand this simple fact. In other words, what you assume to be zealotry can just as likely be a factual statement by a knowledgeable person.

Just because you heard something you didn't want to hear doesn't mean it was delivered by a zealot. The fact that you're so easily confused by such a fact is a significant indicator the AC's comment was correctly targeted (you're too close) at you. Your response also implies you are in fact a MySQL zealot. Otherwise, why so easily offended by a comment which was obviously presented with levity, by an AC, in a trollish manner. Getting upset about that is just plain silly.

At the end of the day, with so many excellent relational databases available at zero or little cost, choosing MySQL as your database speaks poorly of you. Just about any database is better than MySQL. Imagine a friend bringing home a cheap Chinese made, Yamaha reproduction and declaring they are tired of "zealots" pointing out that better bikes exist. Well, your friend might be tired of hearing it, but it doesn't change the facts. It doesn't take a zealot to point out that bike is a complete PoS; and without regard to zealotry, better options exist. At least with a bike, you can defend such a purchase from a cost perspective. No such hand hold exists when it comes to the field of freely available databases; almost all of which are better than MySQL.

Lastly, please don't forget that one need only be knowledgeable about relational databases to dislike MySQL. Zealotry need not be a factor.

Re:Do people trust this project anymore?

[aesiamun]

The same thing happens when a mac os x or windows story comes along. Someone feels it's necessary to voice that linux is better, or works for them, or their grandmother.

Re:Do people trust this project anymore?

[gravyface]

At the end of the day, with so many excellent relational databases available at zero or little cost, choosing MySQL as your database speaks poorly of you. Just about any database is better than MySQL.

That's a bit harsh and unrealistic.

In Real Life (tm), you don't always have that choice: sometimes you take what's given to you and roll with it, sometimes you have to use what you know best to get the job done.

I use both nowadays: I do feel more comfortable with MySQL (mainly the tools, auth. mechanisms) because for many years, it was the only option available: host didn't provide Postgres support, xyz application didn't support it, or I personally couldn't justify the risk/learning curve for a tight project.

Postgres may be "superior" to MySQL, but that doesn't mean it's the right choice all the time.

Re:Do people trust this project anymore?

[GooberToo]

choosing MySQL as your database speaks poorly of you. Just about any database is better than MySQL.

That's a bit harsh and unrealistic.

In Real Life (tm), you don't always have that choice: sometimes you take what's given to you and roll with it, sometimes you have to use what you know best to get the job done.</I>

If you take what is given to you then it wasn't your choice. In that case, that statement does not apply.

<I>Postgres may be "superior" to MySQL, but that doesn't mean it's the right choice all the time.</I>

I completely agree with you. PostgreSQL is NOT the right choice all the time. In fact, I believe if you re-read my original post you'll find that my position is just about any choice is better than MySQL; not that PostgreSQL is always the right choice.

Re:Do people trust this project anymore?

[QuandraX]

Yeah? Well, that's just, like, you know, your opinion, man...

Re:Do people trust this project anymore?

[Anonymous Coward]

Your response is paranoid and childish. Sometimes you have to hear things you don't want to hear. The AC's response is actually funny and accurate. By in large, most MySQL users are children when it comes to relational databases.

I use MySQL and it does what I need it to do.
Perhaps that makes me a "child" in your little world but in real life people choose the tool for the job, not the other way around.

Re:Do people trust this project anymore?

[Lodragandraoidh]

There is a YUM package for a MicroSloth product? Will the wonders never cease?

Re:Do people trust this project anymore?

[tthomas48]

Also your view of MySQL is a couple years old. MySQL has made leaps and bounds on Postgres. Now that mysql has things like PL/SQL and Foreign Keys the differences between it and postgres have dwindled.

Disclaimer: I have a strong Oracle, Postgres, and MySQL background. I find them all to be excellent tools.

Re:Do people trust this project anymore?

[MikeBabcock]

I'd love some proof on that 'children' comment. MySQL is a low rung in price, but there are much lower rungs to be had -- SQLite comes to mind even.

The fact that MySQL is easy to configure and easy to use should not be used against it -- and it is for those reasons that so many people with very little SQL skill have been introduced to a SQL server. Had Postgres' developers made it half as easy to install and manage on my machines 10 years ago, many people would now be using it instead.

As it stands today, MySQL is an excellent platform for relational database development. It has limitations, and there are better products, but not everyone is writing (for a random example) VISA's payment processing system.

Your bigotry is apparent, and its just bigotry.

Re:Do people trust this project anymore?

[Fweeky]

The move to bzr was also a MySQL AB thing; if Sun had anything to do with it they'd probably have gone with Mercurial.

After being acquired by Sun, we learned that Sun will standardise on Mercurial. However, our decision on Bazaar was in the works already before MySQL was acquired and wont affect Suns policy.

Re:Do people trust this project anymore?

[tj111]

I see no reason at this point to distrust the project, rumors are only rumors. Plus Sun is already struggling some, and the last thing a struggling business wants to do is piss off its customer base. And if they do close the source, its not like we can't just fork the last free version they made available.

Re:Do people trust this project anymore?

[Deagol]

As someone who supports a small shop, I agree with you in theory. But in practice, many of the packages people use don't support anything other than MySQL, or don't support Postgresql. Take FogBugz for example. Sad, but true. I'm sure I could find suitable replacements for most of our apps that will accommodate Postgresql and then migrate them, but there are only so many billable hours to go around.

For custom programming, sure -- go with the best DB from the get-go. But for canned apps that are cheap to deploy, sometimes the lowest common denominator wins.

Re:Do people trust this project anymore?

[Christopher B. Brown]

th respecting understand this simple fact.At the end of the day, with so many excellent relational databases available at zero or little cost, choosing MySQL as your database speaks poorly of you. Just about any database is better than MySQL.

True though that may be, there are quite a lot of people running databases not because they want to develop a new application, in which case, yes, indeed, "choosing worst options speaks poorly of them," but rather because they want to run Application X, where someone else (whom we might speak poorly of!) wrote Application X to only be compatible with the "worse options."

Re:Do people trust this project anymore?

[Dhalka226]

All DBAs worth respecting understand this simple fact.

Damn those Google idiots and their inability to hire competent employees. It's a good thing they only use it for Adwords and not something important to their business.

Meh, doesn't matter, I just won't respect them. They're clearly just a bunch of paranoid children over there anyway. I'll take my cues from randoms on Slashdot, thank-you-very-much.

Re:Do people trust this project anymore?

[GooberToo]

Your bigotry is apparent, and its just bigotry.

Being a bigot does not invalidate the facts. And for the record I am as bigoted as anyone that has knowledge of the subject matter and has made an educated decision.

<I>The fact that MySQL is easy to configure and easy to use should not be used against it -</I>

It's not. Being easy to install is hardly an issue. Just the same, being easy to install has nothing to do with its SQL engine. Your red herring is easy to ignore.

<I>Had Postgres' developers made it half as easy to install and manage on my machines 10 years ago, many people would now be using it instead.</I>

Again, you're using a metric which has nothing to do with the top at hand. I guess you really love fish. If PostgreSQL's historical lack of Windows support is what keeps you from using it today - well, I can't help but smile at how irrational you are. Obviously current metrics are ignored by you.

<I>As it stands today, MySQL is an excellent platform for relational database development.</I>

Of course its an excellent platform - so long as you love to learn to do things poorly, slowly, tediously, and don't have serious data concerns.

<I>Your bigotry is apparent, and its just bigotry.</I>

Yes, I always associate knowledge with bigotry. Your red herrings and ignorance on the topic hardly furthers your cause.

<I>not everyone is writing (for a random example) VISA's payment processing system.</I>

Agreed; but that hardly changes the fact that better solutions exist.

Re:Do people trust this project anymore?

[GooberToo]

We seem to be chasing tails here. If you must have application X and equivalence for X with another database can not be found, then by process of elimination, you have no choice.

Re:Do people trust this project anymore?

[GooberToo]

Go bother to learn more on the topic. The MySQL that Google uses has nothing in common with what you use because they have their own back-ends (plural) for it.

Google is in a position to fully understand the vast limitations and short comings of MySQL; which they have mitigated by using their own custom-purposed back-ends. But hey, I'm sure things like facts won't deter you.

In the grand scheme of things, no one cares if a blog entry is lost. Does that mean MySQL is a good database? Hardly. It actually means, for their purpose, it is one of the easiest databases to swap out back-ends on to satisfy custom requirements; albeit with understood limitations. And for those uses where they choose not to use a custom back-end, they obviously decided the data is simply not important enough to care.

You're hostile attitude clearly implies the original comments squarely hit home. I strongly suggest you learn more about what is available. Many, many, many freely available and superior options exist. Next, try to learn and use them; or one of them. Once you've done so for real applications, you'll quickly laugh at your comments and pat your self on your back that you allowed yourself to grow. That may sound nasty, but it's true.

Ultimately, your decision to use MySQL is very simple. You can make that decision by asking three simple questions. Is YOUR data important? If the answer is yes, then you should not be using MySQL. There exists no shortage of technical reasons, which are widely documented, to support this factual statement. Most reasons are provided in almost all MySQL threads.

Next, is YOUR time valuable? If the answer is yes, then you should not be using MySQL. There exists no shortage of technical reasons, which are widely documented, to support this factual statement. Most reasons are provided in almost all MySQL threads.

Next, is scalability important to YOU? If the answer is yes, then you should not be using MySQL. There exists no shortage of technical reasons, which are widely documented, to support this factual statement. Most reasons are provided in almost all MySQL threads.

Now then, if you insist of using MySQL, by all means, do so. But your decision to use MySQL doesn't change the fact that better solutions exist.

Before you reply, please read the various postings here and other anti-MySQL postings. You'll find, with little effort, the details I purposely left off in my above retort.

Re:Do people trust this project anymore?

[MikeBabcock]

Being a bigot can often invalidate one's argument. Perhaps not one's point, but one's argument for sure. Your point may be valid, but arguing it as a simple bigot makes you sound like you have no valid reasoning skills behind your viewpoint.

My 'red herring' was not one, as you obviously can't figure out that the ease of installation of MySQL is the very reason so many new and unprofessional SQL users choose the software. As a result, a great number of MySQL users are of this caliber. This does not reflect poorly on the product's quality, such as your original comment implied, but positively on its ease of use.

Being able to actually argue a point with actual refutable reasons gives one a reason to be listened to. Making wild accusations about quality and maturity of a project or its users with no actual facts on which to base them makes one a simple bigot. Perhaps a bigoted simpleton. And while I accept that many knowledgeable persons can become bigoted as a result of their knowledge, an inability to communicate their own reasoning which led to that bigotry does not implicitly make them right anywhere but in their own minds.

Re:Do people trust this project anymore?

[tinkertim]

I've grown rather fond of Mercurial in the last year. I also like Bazaar, still wrapping my head around Git.

One thing is for sure, people are as adamant about their choice in a DRCS as they are about their choice in database servers.

I'm just happy to see a DRCS (no matter which one) in use. It makes things so much easier.

I'm going to go hide from the Subversion fans now.

Re:Do people trust this project anymore?

[Anonymous Coward]

You're completely off topic and irrational. Furthermore, you seem to have reading comprehension issues; otherwise it would be clear I'm not a bigot. Your red herring statements are just that. They are irrational and off topic, serving only to detract from your other irrational and uninformed comments.

According to your definition of bigot, everyone that knows more than you is a bigot. According to your definition of bigot, everyone that disagrees with your position is a bigot. Thankfully for the rest of the world, we all ignore your irrational definition.

Ease of installation certainly does explain the size of the MySQL installation base; as does the ease of jumping in feet first. But, as I pointed out, this is a red herring because it has nothing to do with the topic at hand. MySQL's early adoption was never a topic I directly addressed, other than to dismiss it as a red herring. As I previously stated, and you seemingly missed, ease of installation is not a factor in any way, shape, or form, for describing MySQL's technical capabilities, or lack thereof, as a RDBMS.

So unless you can provide something other than "poo-poo head" red herring comments, I believe this exchange is done.

As a closing comment, I will add that I find it most amusing the terms of which you are ready to convict me seem more aptly applied to you. Perhaps one is projecting in order to feel validated in your preferred choice of database? That's a rhetorical question. No response is required.

Re:Do people trust this project anymore?

[GooberToo]

I use MySQL and it does what I need it to do.

Look at the alternatives and you'll soon find that you are likely spending more time and learning poor SQL habits to work around MySQL's SQL limitations.

So while MySQL may do what you need it to do, it likely is doing it poorly and you just don't have enough experience to realize it. Seriously, pick one of the many alternative solutions and take some time to take a look at it. Very likely, if you approach it with an open mind, you'll find yourself saying, "cool, I can use that!", as you examine features which are simply unavailable to MySQL databse users. Or heck, you might just do some reading to discover just how poor MySQL actually protects your data; from the mouths of MySQL core developers. No joke.

My personal preference is PostgreSQL. Many other solid options exist.

Please, do yourself a favor and take a look at what other people are raving about. It won't take too much effort to realize just how limited and unprotected your data really is when using MySQL.

Ask yourself, is you data important? Ask yourself, is your time important? Ask yourself, is scalability important? If you answered, "yes" to any one of those questions, you are likely far better off with a database solution other than MySQL.

If you take the time to look at some of your options, I feel very strongly you'll soon be thanking yourself.

Re:Do people trust this project anymore?

[buddyglass]

I work exclusively with PostgreSQL (8.2) at work. That said, having read this post, I'd very much like to have the "scheduled events" and "log to tables" features. Haven't researched it heavily, but does Postgres have anything resembling either of those?

Re:Do people trust this project anymore?

[Fallen Andy]

Aha, the new Emacs vs. Vi debate. But of course there *is* another major project which always gets forgotten on slashdot - thats Firebird.

(Around 1989-90 I evaluated most of the commercial SQL DB's for Unix for a company here in Greece. None were particular good back then - we would have *killed* for any of the current crop). You youngsters are *spoilt* I tell you!

Andy

Re:Do people trust this project anymore?

[Thundersnatch]

Now that mysql has things like PL/SQL and Foreign Keys the differences between it and postgres have dwindled.

The fact that DRI ("foreign keys") is a noteworthy feature in MySQL speaks volumes about its quality. Every commercial datase has had that feature at introduction, going back to the 1970s. It is simply a requirement for a relational database, not a feature.

I haven't used it in about a year, but thank you for saving me some research this year: MySQL has always been - and will always be - a fucking toy.

Hosting providers

[Aminion]

Whenever I read about a new MySQL version, I think about all of the hosting out there that are still running 4.x. I understand that you can't simply upgrade to the latest version as it would mess up customers' applications, but how about offering customers different versions of MySQL? Is it really that hard to do? A growing collection of well designed web applications require MySQL 5.x and it sucks to miss out om them simply because your hosting provider isn't database flexible enough.

Re:Hosting providers

[Anonymous Coward]

You sure can upgrade in most cases, just make sure old_passwords is on.

Re:Hosting providers

[gfxguy]

Yeah... I dunno... I mean, same server, different port? It's nice to use the default port for your applications. Or different servers?

Remember back when we had to do http://www.someplace.com:8080?

Pretty annoying. Of course, it's just one variable or setting in a decent app.

My first concern, when I read this, was about the license. Wasn't there some kerfuffle about Sun buying them?

Re:Hosting providers

[Bill, Shooter of Bul]

Some do. Bluehost asked me when I signed up what versions of popular web tools I wanted MySql 4 vs 5 was one of the options.

Re:Hosting providers

[mattmcm]

1and1 allow you to choose both 4 and 5. You choose the version when creating a database on their management page. I don't know about other webhosts, though.

Re:Hosting providers

[xiaomai]

This isn't really true of an upgrade from Mysql 4.x -> 5.x. MySQL changed some things (notably their JOIN syntax) to make them more compliant with the ANSI standards. So assuming you're dealing w/ PHP/MySQL programmers that only knew the MySQL way to do joins, their applications may break on upgrade.

For more information, see the section entitled "Join Processing Changes" here:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Re:Hosting providers

[alienpeach]

4.x? What about 3.x?

Re:Hosting providers

[smellotron]

assuming you're dealing w/ PHP/MySQL programmers that only knew the MySQL way to do joins, their applications may break on upgrade.

That's great!

Re:Hosting providers

[tinkertim]

Hosting providers that are worth their weight will typically use external MySQL (clusters) and offer various versions. They've learned the painful lesson that running a bunch of over-allocating services that are open to the world on one box only leads to customers canceling due to down time.

For instance, the $20 I pay a month gets me access to 4.x and 5.x, each version being its own shared cluster.

Re:Hosting providers

[Anonymous Coward]

Yes, and that also means the applications are old and probably don't work with PHP5. They may also have security holes. Force upgrade.

Re:Hosting providers

[SamSim]

I think about all of the hosting out there that are still running 4.x.

Hah. I recently discovered that my workplace was actually running MySQL 3.x, which is such an old release that it doesn't even support structured queries. "MyQL", I call it.

Re:Hosting providers

[pixr99]

A growing collection of well designed web applications require MySQL 5.x

Let me start by saying that I agree with you. It would be great if hosting providers could give us a bit more choice.

Now I'd like to disagree with just the bit of your statement that I've quoted. By definition, a "well designed" web application can never *require* MySQL 5.x. Well designed web application have abstraction layers and don't care whether you use MySQL, PostgreSQL, SQLite and so on. If web programmers built a proper abstraction layer into their apps, they could support MySQL x.y along with all the other versions of MySQL with little added effort.

Re:Hosting providers

[kv9]

Whenever I read about a new MySQL version, I think about all of the hosting out there that are still running 4.x.

most of our customer sites have 3.x/4.x/5.x available. this has never been a problem. maybe your experiences differ from mine.

Re:Hosting providers

[lawpoop]

Now I'd like to disagree with just the bit of your statement that I've quoted. By definition, a "well designed" web application can never *require* MySQL 5.x.

Well, I think that's kind of silly. The whole reason of using a later version is to take advantage of features that aren't available in earlier versions. That means that you don't have to code those features into the app -- for instance, you don't have to write a complex query into your app; you can just select from a view in MySQL*. But if you're going to allow usage of earlier versions, you will have to write that complex query in code anyway.

If you're going that far, why not just write a complete, self-contained storage back-end into your web-app, so you don't have to require MySQL at all?

* I'm not sure that views aren't supported in 4.x, I was just using a good example.

Re:Hosting providers

[h4rm0ny]

1and1 had the worst support and worst financial services department I have ever experienced. I had an extremly bad experience with them and I warn people of them at every opportunity. You're okay with them so long as everything goes by the book. Once you pick up the phone it goes to Hell in a big way. I'm with Fasthosts now who offer MySQL 5.0 by default and then of course there's getting your own server if you really need it. It's pretty cheap as a business cost.

Re:Hosting providers

[MikeBabcock]

Lots of things were introduced in 5.x that would require its use over 4.x. As for writing your own storage back-end, there are a lot of those already too. SQLite comes to mind, along with Zope's object storage which I use extensively in some applications and simple BDB file structures as well.

Re:Hosting providers

[jfmiller]

Ah, but for your same $20 a month, I get my own virtual server that I can configure in anyway I want. I can upgrade software when it is best for me. I also have guaranteed access to memory and processor time.

Shared hosting is quickly becoming an an option only for the lowest of the low end.

Re:Hosting providers

[tinkertim]

I also have a VPS (Xen) on the same network that can connect to both database servers.

I fully agree, shared hosting is rapidly becoming a dinosaur. You just can't predict usage. One site on the front page of Slashdot, Digg .. or in the scope of stumblers makes 500 other sites very unhappy.

A good host will isolate everything, so that its very easy for you to move things around as needed. I'll park domains on a shared server, then move them to a vm when I get around to developing them.

But either way, kind of getting off topic. The fact remains is that most good hosts have absolutely no issues with differences in MySQL version nuances.

XML Functions

[weston]

First release with native XML functions. If there's indexing behind some of the XPath, this could be a very interesting release indeed.

I'd definitely be interested to hear what it's also missing that more XML aware databases include, though.

Re:XML Functions

[larry bagina]

well, since this is MySQl, the xml functions are probably missing atomicity, consistency, isolation, and durability :-)

Sigh. Forgot Link.

[weston]

MySQL XML Functions.

So will Postgres ever catch MySQL?

[Slashdot Parent]

I'm still waiting for data partitioning from Postgres. Maybe once they get it it will be a real database. ;)

Re:So will Postgres ever catch MySQL?

[T-Ranger]

Ill call that bluf. You mean the partitioning that MySQL has had for a couple of years? That partitioning?
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Re:So will Postgres ever catch MySQL?

[pembo13]

Guess speaking ill of Postgres is not welcome here.

Re:So will Postgres ever catch MySQL?

[Slashdot Parent]

I guess not. Oh well, at least I amuse myself. And apparently there is one mod out there somewhere who thinks I'm funny.

Re:So will Postgres ever catch MySQL?

[landonf]

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

Re:So will Postgres ever catch MySQL?

[stoolpigeon]

It was funny, but I would so dearly love to see partitioning in Postgres. Your post just made me smile, then feel sad.

Re:So will Postgres ever catch MySQL?

[stoolpigeon]

Stuff like that is why, as much as I love Postgres, it isn't replacing Oracle any time soon. Not when things like partitioning are called for.

Re:So will Postgres ever catch MySQL?

[EvilRyry]

Care to explain? I've found PostgreSQL's partitioning to work quite well for every situation I've run into.

Re:So will Postgres ever catch MySQL?

[stoolpigeon]

It's a bunch of different tables with contstraints and triggers that need to be created and managed manually. I thought it was self-explanatory.

Re:So will Postgres ever catch MySQL?

[GooberToo]

Stuff like that is why, as much as I love Postgres, it isn't replacing Oracle any time soon. Not when things like partitioning are called for.

Hate to burst your bubble but PostgreSQL is displacing Oracle on the low end. Maybe not in droves but it is happening. Also, the author didn't say that partitioning is required to support his application. Partitioning can make for significant performance gains. After all, why move data you don't need 90% of the time. And if your query planner is smart enough to understand this fact, it makes for a win/win; smarter query plans and potentially huge wins on I/O. Both of which translate to better scaling databases/applications.

You seem to imply Oracle is better because you're wasting I/O moving data you rarely need. That position doesn't make sense to me.

Re:So will Postgres ever catch MySQL?

[Slashdot Parent]

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

Yes, it is radically different. MySQL has partitioning, PostgreSQL does not.

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

That's not partitioning, that's an ugly hack that will save you some I/O.

Re:So will Postgres ever catch MySQL?

[stoolpigeon]

It's 2 sentences. And I thought they were pretty clear - but I guess this proves that not to be the case. So I'll see if I can do a better job this time.
 
When partitioning is required, and there are many cases I can think of where this is so, Postgres is not a suitable replacement for Oracle.
 
I do not disagree that PostgreSQL is a good replacement for Oracle on the low end - or even in the middle. Lots of products can do that.
 
As for what the author said about what his app requires - that's irrelevant. When I said "stuff like that" - I'm talking about his links to how to do 'partitioning' in PostgreSQL. So if you would like to argue that partitioning in PostgreSQL is on par with partitioning in Oracle (or MS SQL Server for that matter) then you would be responding to my one and only point.
 
On a completely unrelated note, I'm also very skeptical that there is any PostgreSQL equivalent to RAC. Once again, this is a technology that really wont see wide spread use. But when you do need it, there aren't many places to go.

Re:So will Postgres ever catch MySQL?

[landonf]

I'm unfamiliar with MySQL's partitioning -- is it radically different from postgresql's partitioning?

Yes, it is radically different. MySQL has partitioning, PostgreSQL does not.

I'm using inheritance to implement table partitioning with a rather large (50+ gig) PostgreSQL/PostGIS database. Constraint exclusion allows the query planner to use CHECK constraints to avoid even looking at tables where conditions contradict the constraints.

That's not partitioning, that's an ugly hack that will save you some I/O.

Reading the MySQL documentation, it sounds like PostgreSQL might benefit from higher-level DDL for partitioning (rather than specification of triggers and inherited tables), but it looks like PostgreSQL's actual functionality is a strict superset of MySQL's.

PostgreSQL requires the use of constraints and triggers to provide data partitioning across the tables, but that method allows the use of any type of partitioning that can be expressed in PL/PGSQL, whereas MySQL defines a specific set of supported partition types.

Anything I'm missing?

Re:So will Postgres ever catch MySQL?

[landonf]

Stuff like that is why, as much as I love Postgres, it isn't replacing Oracle any time soon. Not when things like partitioning are called for.

There are a number features in Oracle Spatial I'd love to have, such as support for topology, and geographic raster image data

However, I can't afford Oracle (and Oracle Spatial). Where I to lash my business to the Good Ship Oracle, I'd be signing up for years of heavy licensing fees as our requirements grow.

PostgreSQL (and PostGIS) seem like a worthwhile investment. They're improving at a rapid pace -- but not sacrificing correctness for features. The features they lack we're still able to work-around, with the anticipation that the up-front cost of missing features constitutes an investment in the product that will be paid off as the features are added, for considerably less than the cost of licensing Oracle Spatial. (That, and contributing what we can to the projects)

Of course, your mileage may vary -- For the problems we're trying to solve, I see PostgreSQL as a good investment at most scales.

Re:So will Postgres ever catch MySQL?

[stoolpigeon]

Don't get me wrong. I've used PostgreSQL. I love it. And I want it to be competitive with Oracle in every way. It's just that it isn't, and that is just the fact of the matter.
 
Do most people need a lot of that stuff? Not really. And we haven't even talked about apps. If you want to run Peoplesoft, Siebel or a number of other enterprise type apps, for which there are no open alternatives to my knowledge - then you wont be running any open rdbms either.
 
I'm a dba - I'd love to see FOSS catch up with the closed source folks because then I could do whatever I want with some great software. But they aren't quite there yet.

I've hacked a million systems...

[NonSequor]

...and I rocked 'em all!

Re:I've hacked a million systems...

[NonSequor]

God damn it. How did I post that to the wrong article?

Re:I've hacked a million systems...

[Anonymous Coward]

God damn it. How did I post that to the wrong article?

Cause your a noob poster. :-)

Re:I've hacked a million systems...

[mixmatch]

God damn it. How did I post that to the wrong article?

Tabbed browsing strikes again!

Re:I've hacked a million systems...

[tinkertim]

God damn it. How did I post that to the wrong article?

Tabbed browsing strikes again!

And beer had nothing to do with it? :)

Re:I've hacked a million systems...

[NonSequor]

Actually it was a combination of bourbon and tabbed browsing.

nice feature set

[RelliK]

Traditionally, that is to say, up until MySQL 5.1.22, InnoDB handled newly inserted records into an InnoDB table with an AUTO_INCREMENT column by using a global counter which held the last value for the auto-incrementing column. A lock would be placed on this counter for the duration of the SQL statement which did the inserting...
The new server variable, innodb_autoinc_lock_mode controls how InnoDB treats statements which insert rows into an InnoDB table with an AUTO_INCREMENT column. Depending on your environment â" specifically, whether you are using the binlog for replication or recovery purposes and whether you are executing "batched insert" statementsâ" you can set this variable to 0, 1, or 2. 0 corresponds to the traditional mode, and is not recommended except for very specific scenarios (see the doc link above). 1 represents "consecutive mode" and is the default. In this mode, only statements where InnoDB cannot determine the number of rows to be inserted will use the global auto-increment lock. All other "simple insert" statements (even those inserting multiple records in batch mode) will use a faster, lighter locking mechanism, which results in significant scalability increases. The final setting, 2, represents an "interleaved" mode and has even greater scalability improvements, but cannot be used in scenarios where the binary log is being used for recovery or statement-based replication.

So now mysql can handle two concurrent inserts? Nice! Except for the fact that this new amazing option is incompatible with replication. MySQL is going to become a real database. Any time now...

Re:nice feature set

[Bill, Shooter of Bul]

Not exactly. 5.1 introduces row based replication as opposed to the statement based replication that is incompatible with the new behavior. Statement based replication has the slaves execute the exact same statement on the slave. Row based just passes the new values of the modification to the slave.

Re:nice feature set

[mattwarden]

Is row replication still logical replication or is physical replication now an option as well?

Re:nice feature set

[Bill, Shooter of Bul]

I must admit, I'm not familiar with that terminology. Row based replication still uses a log file. more info here

Re:nice feature set

[Anonymous Coward]

How about we fix the obvious too? This bug makes it impossible to have an Insert trigger and an update trigger both updating a table. Trying to do so triggers database duplicate keys because there isn't a good lock on the auto-inc value.

A bug, marked as serious and yet left pending since Feb'07 !

Re:nice feature set

[vegiVamp]

I Like.

Interleaved autoincrement + row(value)-based replication == multimaster write.

Just interleave your autoincrements by the number of masters (still a max of 2?), and have master 1 start at 1, master 2 start at 2 and so on.

Re:nice feature set

[Anonymous Coward]

A bug, marked as serious and yet left pending since Feb'07 !

Perhaps they can't magically fix all serious bugs simultaneously by doing a fucking raindance, and just maybe there is more than 18 months worth of work involved in fixing all the serious bugs that are more serious than your pet bug.

Re:nice feature set

[MindStalker]

"Consecutive mode" sounds like it does the trick though, it determines how many rows you are inserting and makes room for them so other insertions can be made at the same time can be inserted early and be marked as row # after the mass insert. This essentially allows multiple inserts at the same time because it only has to make space for the insert via adding 1 to the increment for the next insert. As long as the slaves support the same feature everything will be fine as the results are deterministic. Only the interleave system is non deterministic but it warned you, this is not for master/slave systems. Its not set by default to interleave and generally not a recommended setup unless you have just a single DB and you want the best speed possible.

Re:nice feature set

[mattwarden]

I reviewed the link but still can't tell. It sounds like it is logical replication, meaning that row A in the master looks like row A in the slave, although it may be stored in a completely different relative location on disk. Physical replication means the disk locations are the same, the partition tables are the same, etc. Oracle has both options; I was trying to determine if MySQL is chasing some of the replication features of the bigger guys.

Anyway, thanks for the link.

Re:nice feature set

[Bill, Shooter of Bul]

No, its a physically different disk. There is the federation engine which does reference a table on another server. So if it was a normal innodb table on server A and a federated table on server B that was pointed at the table on server A. That's somewhat similar to what you are talking about, but there is increased overhead as the federated table actually queries the server the table lives on. You could are other ways to tell mysql where a table is stored, but having two servers access the same table on the same disk is not safe.

Re:nice feature set

[mattwarden]

No, I think I wasn't clear. It is not replication on the same disk. It means that there is byte for byte replication and the way the data is stored on master is the same way it is stored on slave. But master and slave could be thousands of miles from each other.

Re:nice feature set

[Bill, Shooter of Bul]

Ok, that makes more sense. I would think then that row level replication is physical replication, but its tough to say. The two databases could use completely different storage engines in mysql. The master could be using innodb tables, and the slave could be csv. So the data would be the same, but it wouldn't be stored byte for byte in the same manner.

WTF?

[Slashdot Parent]

Read grandparent post again, then decide whether or not your reply makes any sense at all.

Methinks you are too used to seeing Postgres trolls in the MySQL posts to catch the joke. Apparently the mods are, too.

Latest MySQL GA "Community" Source Tarballs

[Anonymous Coward]

The links to the MySQL GA "Community" source on MySQL Developer Zone are still stuck at 5.0.51b. I understand that MySQL is no longer providing binaries of the "Community Edition". But the least they can do is provide an easy to find and accessible link to the latest source tarball (now at 5.0.62).

Re:Latest MySQL GA "Community" Source Tarballs

[headLITE]

No, that's not true. Sun provides binaries of MySQL *5.0* every half year. 5.0.51a/b was in January so I'd expect the next set pretty soon now.

Sun has also been providing binaries of 5.1 for quite some time now.

Re:SLASHDOT SUX0RZ

[Anonymous Coward]

download newest goatse release candidate here

Frankly, this release is not an improvement. The 'goatsex' around the outside rim of the image is an interesting idea. However, doing away with the original goatse images of giver and reciever, in favour of simple ASCII art, is an innovation too far in my humble opinion.

I'd file a bug report, on the site you posted, but am worried that it'll fall out again. Also, where's the change log? It seems it might have been a rather big one, plenty of people would like to see it.

This is the goatse equivalent of KDE4.0: incomplete, full of gaping holes, and not worth the download (just joking, I love KDE).

MySQL payments - anyone forced yet?

[Anonymous Coward]

Does anyone know of anyone whom MySQL has forced to pay them for their database?

Re:MySQL payments - anyone forced yet?

[headLITE]

How would that work, seeing as it is explicitly allowed to use the free version for any purpose?

Re:MySQL payments - anyone forced yet?

[Jellybob]

No one who's been forced to, but we have a support contract for our primary DB server.

That gets us binaries compiled with Intel's compiler (about a 20% performance boost I think), and a shoulder to cry on should anything go wrong.

Re:MySQL payments - anyone forced yet?

[Anonymous Coward]

There is no 'free' version.
For Open Source Projects:

        * If you are developing and distributing open source applications under the GPL License, then you are free to use MySQL under the GPL License. More Info Â
        * If you are developing and distributing open source applications under an OSI-Approved License, but not the GPL, MySQL provides the GPL License with a FLOSS Exception. More Info Â

Now, I know of some people who are not licencing their code in an open source way - therefore they should be paying. Any yet, have not - making the same mistake as headLITE.

Re:MySQL payments - anyone forced yet?

[headLITE]

You're quoting from a page that tries to sell subscriptions. I'll agree that it is misleading, but I know for a fact that it's not meant in the way you're reading it.

Re:MySQL payments - anyone forced yet?

[Anonymous Coward]

Huh. So who should people believe? The web site of the owners of the code, or some person who's headLITE?

From the COPYING that comes with the code.

You should also get your employer (if you work as a programmer) or your
school, if any, to sign a "copyright disclaimer" for the program, if
necessary. Here is a sample; alter the names:

          Yoyodyne, Inc., hereby disclaims all copyright interest in the program
          `Gnomovision' (which makes passes at compilers) written by James Hacker.

          SIGNATURE OF TY COON, 1 April 1989
          Ty Coon, President of Vice

This General Public License does not permit incorporating your program
into proprietary programs. If your program is a subroutine library,
you may consider it more useful to permit linking proprietary
applications with the library. If this is what you want to do, use the
GNU Library General Public License instead of this License.

So again, looks like you were wrong.

Re:MySQL payments - anyone forced yet?

[headLITE]

The COPYING file is just the standard GNU General Public License, version 2. I don't see what you're trying to prove quoting from it.

Re:MySQL payments - anyone forced yet?

[Anonymous Coward]

but I know for a fact that it's not meant in the way you're reading it.

Ok, please provide proof for your claim of factual knowledge.

Re:MySQL payments - anyone forced yet?

[headLITE]

Feel free to e-mail me about it at df at sun dot com.

Yeah, but does it have sub second Timestamps?

[level4]

I would like to use MySQL instead of Postgres - it's easier for me to install, maintain, and just plain understand. I don't like how PG does things a lot of the time and find it needlessly complex. But because MySQL lacks the seemingly basic ability to store a timestamp with better than second accuracy, I can't, because I have to store log events which are often more than one a second - much more - and I need to know exactly when. Milliseconds would be fine, microseconds would be great.

MySQL currently recommends some ridiculous hack where you strip the sub-second information from the time you send it and store it in another column, then write some kind of view which combines them back. What? I am not doing that to implement what I consider to be basic functionality! Do you remember how my motivation for switching is because I want things to be simple? Writing weird multi-column time recombination hacks is not my idea of simple.

Replication improvements, XML parsing, great features all - but please just give us timestamps with accuracy better than a second? A lot can and does happen in less than a second and I need to be able to log it with accuracy!

Re:Yeah, but does it have sub second Timestamps?

[Anonymous Coward]

Use a double and store the timestamp yourself?

Re:Yeah, but does it have sub second Timestamps?

[level4]

Use a double and store the timestamp yourself?

Yeah, great solution. So now I have to implement readers and writers in every application that accesses that DB. Want to search in a date range? I need to write something to generate a query using the custom time storage, and then to re-convert the results on data return .. basically I'm writing my own driver. In multiple languages. That makes the custom view seem like an elegant, simple solution.

Or, MySQL could just support subsecond timestamps in the common format like everyone else. How hard can it be?!

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

If you're going to switch databases over the issue, you might as well consider other options, like Firebird: it's also free, I do believe the timestamps have better-than-second precision (at the very least it insists on showing me 4 extra digits I never use for anything), and it's certainly easier to install, setup, and admin than PostgreSQL (IMO). It has limitations, of course, and you should be careful to read the fine print, as you would with any product selection. I would worry that you're using some particularly esoteric features of PostgreSQL that won't translate well to Firebird, but if MySQL is even an option for you, that's highly unlikely.

Slashdot declined to carry the story I posted on it (yeah, yeah, grousing...), but Firebird 2.1 (release) came out three months ago, with some really nifty features like on-commit triggers that let you enforce constraints no other database will help you enforce (that I've seen -- Oracle certainly won't.) It rocks.

Your mileage WILL vary, but I'd recommend at least checking it out. Either http://www.ibphoenix.com/ or http://www.firebirdsql.org/.

Re:Yeah, but does it have sub second Timestamps?

[rebootconrad]

Have you not considered using a double with unix timestamps (with microseconds) generated by whatever script is inserting the data? It's essentially the same thing as a native timestamp assuming you don't need anything pre-1970..

Re:Yeah, but does it have sub second Timestamps?

[level4]

I have checked out Firebird in the past and it looks great - but there's a huge chicken and egg problem. Basically, to adopt a DB requires that it be supported in the languages I use - and for scripting this kind of thing I use Ruby. I can't find any firebird support, native or otherwise, for Ruby, let alone support in the more high-level libraries like ActiveRecord or DataMapper.

I'm not trying to put down the project - it looks great. But I can't possibly afford the time or resources to develop all my own libraries from scratch. That might sound selfish, like I'm all take and no give, and expect everything to be packaged nicely just for me - but I don't think so, I contribute where I can. I just can't do that kind of thing alone. I have deadlines and a budget, and to try to trailblaze a new path supporting a newish, as-yet pretty obscure DB would be suicidal.

If firebird really pans out and becomes a (widely accepted) viable alternative to the "majors" I will be there contributing patches and doing what I can. But I can't be the lone crusader who starts down that path. To be brutally honest, judging from past experience, it will probably have to be the developers themselves who start that ball rolling.

For now, the path of least resistance for me (and MANY others) is to stick with PGSQL. Changing DBs is not the big deal you might think, since I have access to all the above-mentioned tools - I could change to MySQL in just a few hours of work (and then a few more hours for the imports to finish/propogate!), just like I switched to PGSQL when MySQL's timestamp limitation became a big problem. No big deal, and I generally keep everything DB-agnostic. Switching to an unsupported (by my favourite libraries) database would be a very big deal, however, and I just don't have the resources, or - frankly - a good enough reason.

Thanks for the suggestion though and I will definitely keep an eye on the project.

Re:Yeah, but does it have sub second Timestamps?

[Simon (S2)]

features like on-commit triggers that let you enforce constraints no other database will help you enforce (that I've seen -- Oracle certainly won't.)

Oracle has deferrable constraints, that are checked on commit, and you can do almost everything you would do with an on commit trigger with deferrable constraints.
Postgresql has deferrable triggers, that trigger on commit, and that's probably the same feature you call "on commit trigger" in firebird.

Re:Yeah, but does it have sub second Timestamps?

[level4]

Yeah, I've considered that and even played around with it a bit. But my goal here is to reduce complexity, and implementing that kind of solution (for multiple apps in multiple languages) would have the exact opposite effect. It's far easier to simply stick with PGSQL.

My enemy is complexity and vendor lock-in, in any form. Implementing the custom MySQL view would be 1 step forward (ease of administration) and two steps back (custom config). Doing what you suggest would be one step forward, 10 steps back and would require pervasive code and library changes!

I'm all for hacking around problems I find and then sharing them for others to use - I've released many patches and how-tos to allow Ruby on Rails to use UUIDs as its primary keys, for example. But there's also the consideration of whether something *should* be hacked around and papered over. I am personally of the opinion that nobody, including me, should be forced to come up with low-level workarounds to what I consider to be basic functionality holes in MySQL. The work of supporting sub-second timestamps is by all rights MySQL's job. Everyone else does it and I can't for the life of me understand why they haven't.

So that's my reasoning - I am sure some will disagree, but I hope I've made a fairly rational argument.

Re:Yeah, but does it have sub second Timestamps?

[Anonymous Coward]

Can't you use a generic database interface layer like unixODBC or equivalent?

Re:Yeah, but does it have sub second Timestamps?

[Anonymous Coward]

Basically, to adopt a DB requires that it be supported in the languages I use - and for scripting this kind of thing I use Ruby.

Whoa, stop! That's your problem, right there.

Re:Yeah, but does it have sub second Timestamps?

[stoolpigeon]

triggers are straight from hell. and there isn't anything firebird has that you can't get in oracle. i spend a good part of my day hating oracle sometimes, but it isn't because the product lacks features, or options.

Re:Yeah, but does it have sub second Timestamps?

[kv9]

Use a double and store the timestamp yourself?

Yeah, great solution. So now I have to implement readers and writers in every application that accesses that DB.

couldn't you write a stored procedure for all that?

Re:Yeah, but does it have sub second Timestamps?

[Anonymous Coward]

someone already did

Re:Yeah, but does it have sub second Timestamps?

[jazzkat]

"I would like to use MySQL instead of Postgres - it's easier for me to install, maintain, and just plain understand. I don't like how PG does things a lot of the time and find it needlessly complex."

Can you elaborate a bit on this? What, specifically, is harder in Postgres than in MySQL? In what environment are you trying to install?

Under Windows, installing PG is a no-brainer with the installer. Under RHEL or CentOS, you just need to install a handful of RPM's and go.

Maintenance isn't that difficult, either, if you're using PGAdmin III, which is bundled as part of the Windows installer.

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

Again, the request was for a simple solution to a simple problem.

Good support for data types in a database is crucial to ease of use.

The 3 most important data types (in my opinion) are text, integers, and points in time. There are other useful data types, but it's particularly painful to be without at least those 3. MySQL should get this right and have a higher-precision timestamp.

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

nifty features like on-commit triggers that let you enforce constraints no other database will help you enforce

Can you be more specific? PostgreSQL offers something called "constraint triggers", which can be deferred until commit time (using the same semantics as deferred FK checks).

http://www.postgresql.org/docs/8.3/static/sql-createconstraint.html

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

"I hope I've made a fairly rational argument."

Yes: you know how to work around a problem like this (probably 5 different ways), but you don't want to jump through hoops to solve something that should not be a problem in the first place.

It's not like you're asking for a date type based on the Mars days and years. These are Earth timestamps that you're talking about here.

Re:Yeah, but does it have sub second Timestamps?

[TheLink]

Actually if you want to have a better idea of the order events happened, maybe you should use another column where the number keeps incrementing. This is true no matter what DB you use.

Then you do select * from logs where ... order by time_column, seq

You say milliseconds would be fine. But maybe 5 years from now when you have a faster system they won't be good enough.

Whereas having a separate column to keep track of the order might be fine, as long as you and the database does it right, and you only have one thing inserting the logs to the DB.

If you have concurrent log insertion, it can get ambiguous when something really did happen. Sometimes you might never really know...

For example if some event happens and because of that A, B, C all log that they are doing some stuff. A might log before it does stuff, and B might log after it does stuff, and C might be written in a faster language, so actually do stuff later, but end up logging before the rest.

It gets more fun - if A, B, C are all far away so that posting the logs over the network takes significant time, you might then have to add another column.

So you end up with: sequence ID, logserver time, logclient time.

Whoopee.

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

Touchy, a bit? If triggers aren't your thing, that's fine; how about domains, and the ability to set domains on parameters to stored procedures? (example: a stored procedure that takes an integer, where the integer is restricted to being between 3 and 7) Last time I checked the Oracle docs, that wasn't available. It's not a big deal -- but you should be careful with blanket statements. Every product has its little features, which matter to different people. Maybe you'd be more interested in CTE's (common table expression, syntax is 'with [recursive] xxx as (select ...), [...], select ... from xxx'? Sure, Oracle has connect-by, which I find more obvious for simple cases, but it doesn't have CTE's which Firebird and MSSQL both have, and are more generic (not just for recursion), and which I use the hell out of. I have no interest in converting an Oracle user, or anyone for that matter -- only in notifying you that it might be something to check out.

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

Thanks for the link, I hadn't seen those. (I did specify "that I know of", right?)

Looking at the docs, yes, they could be used for some of the same things as on-commit triggers, in the case of enforcing constraints. You could probably hack them into doing what you needed for on-commit triggers in the general sense, so long as you can find a table somewhere that will always be touched during the session. (Firebird has an on-start-transaction trigger as well, which could help with that, but we're talking about PG -- so I don't know what you'd do to ensure that.)

In the case of verifying a relationship like "every master row must have between 5 and 7 child rows", either system would require you to jump through a few hoops, but it looks entirely doable: (I haven't seen an example yet of an Oracle deferred CHECK constraint with the ability to run a sub-select against another table, only ever simple foreign keys, but maybe that works as well?)

Firebird: create a global temporary table of master rows (just PK's) that need to be checked; triggers on master and child tables both put the master's PK into the table; a procedure exists which can check the master and remove it from the list if all is well; an on-commit trigger runs the procedure for each row in the temporary table (this lets you force the constraint to be checked whenever you like, explicitly, by calling the procedure when you're ready.)

Postgres: you could put the deferred-trigger-constraint on the master table, ensure that all changes to a child 'update' (no-op, such as PK=PK) the parent; I'm not sure how you would go about forcing a check early though, if you needed it.

(I'm thinking of migration triggers I've written where I have such a constraint that I can check often, but not immediately; I'd still prefer to check it as soon as possible and throw an exception early, rather than wait until the entire batch import finishes, possibly far later, and with less opportunity for debugging.)

I'm also using on-commit triggers for a form of lazy-evaluation (not exactly constraint-checking); variables are marked as 'invalid', and recalculated as needed (not immediately, to prevent recalculating the same one multiple times), but I want them recalculated by the end of the transaction, too. Looks like I'd have to rethink that process entirely with PG, unless maybe a constraint-trigger is also allowed to modify data, thus "making" it valid, effectively? Again, if you can make sure there's always at least a one column table somewhere you can slap a constraint trigger on, I suppose you could use them that way.

As to Oracle, I've seen some people try to use triggers on materialized views to perform on-commit operations, but as I recall that's not foolproof.

Re:Yeah, but does it have sub second Timestamps?

[stoolpigeon]

your right, i was a little hopped up this morning. and i should have been more mellow. sorry about that.

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

"every master row must have between 5 and 7 child rows"

You realize that you can't allow concurrent inserts if you have a check like that, right?

"unless maybe a constraint-trigger is also allowed to modify data"

Yeah, they can. They can't modify the rows before they are inserted, or anything like that, but you can, e.g., execute a separate INSERT statement.

I think that both databases offer a lot of tools to developers. I don't know much about FirebirdSQL, but it seems like a pretty good RDBMS to me.

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

You realize that you can't allow concurrent inserts if you have a check like that, right?
Sadly, very much so, yes. Currently the only constraints that can be verified across transaction boundaries (that is, the constraint can "see" more than the transaction itself can) are primary, unique, and foreign key constraints, and that's mostly thanks to voodoo in the indexing components of the database system. At the risk of being proven wrong again-ish (and this would be a good thing!) I know of no RDBMS that allows you to write arbitrary (check) constraints that are verified at commit time, but against both your transaction and any prepared-or-committed transactions nearby, ensuring that at the end of the day, when everyone's committed what they're going to commit, the database is in a safe state.

(Debate: should we want it to allow transactions to commit when they can't tell, from looking just at the data visible to them, primarily when running in serializable mode, that everything is going to be okay when they commit? This would allow you to insert a row referencing another row, which is being inserted by another transaction, so long as the other one commits [prepare is sufficient] first; should constraints require that both the transaction itself, and the database as a whole, both validate?)

Until we get that feature (what a day!), all such complex constraints are going to involve some locking, or else suffer from local-to-transaction-only constraints which can be violated by any two transactions working in tandem, ill-intended or not.

The main risk I see in implementing such a feature would be that the check constraints could be malicious, doing more than just preventing the transaction from committing -- such as reveleaing data from another transaction (not so bad -- if you've written a work-around for serializable mode, it's your own fault) or modifying data (should be preventable, given other areas where this is necessary.)

Maybe that's still in our short-term future. But given how easily people give up on writing good, correct constraints, there doesn't seem to be much push in the database community for even more constraints -- they'd rather be writing the stored procedures and views that get the business people off their backs, and just trust that their code, fate, etc. align to keep the database sane. It seems much cheaper.

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

"Currently the only constraints that can be verified across transaction boundaries ... are primary, unique, and foreign key constraints, and that's mostly thanks to voodoo in the indexing components of the database system."

It's not that they can see other data, it's that other transactions take out locks. Transactions aren't isolated from the locks of other transactions, obviously. UNIQUE is the only constraint in PostgreSQL that uses "index voodoo", that is, the locks required for a UNIQUE constraint don't last for the entire transaction.

"so long as the other one commits [prepare is sufficient] "

Prepare isn't sufficient for the feature you describe. The point of prepare is that you can still do "rollback prepared", but if other transactions already think you committed, it's wrong to rollback.

What you're talking about is not really feasible in the near future. The locking would need to be extremely intelligent: it would need to understand your constraint, and understand what kinds of things will cause a violation if the commits happen in some certain order, and then decide which transactions to block, which to allow through unimpeded, and which need to be aborted.

Currently, there's somewhat of a mix of strategies in use. It's easy to tell exactly what will cause a violation of the unique constraint, so it's easy to answer this for the specific case of UNIQUE: block if there's a conflicting transaction in progress, let it through if there is no conflict at all, and abort if there's a committed conflicting tuple.

Also, instead of trying to determine which tuples will violate your constraint, we often just determine when it might. This leads to the over-aggressive locking (which you're trying to avoid), but it works.

I am working on (still in design stages) a "non-overlapping" constraint for PostgreSQL for my temporal user-defined type "period". The non-overlapping constraint is important for the concept of temporal keys, but can't be implemented with UNIQUE alone. I can solve it in much the same way as UNIQUE is solved: by treating it as a special-case constraint where I know in advance what the conflicts are. I think I can make it into an infrastructure that will allow people to define any constraint with that property (can determine conflicts ahead of time).

Re:Yeah, but does it have sub second Timestamps?

[DragonWriter]

Under Windows, installing PG is a no-brainer with the installer.

Its still more work than installing MySQL on Windows, though its not a lot more and its work that shouldn't be a big deal in a production database server.

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

I think I disagree on 'rollback prepared'; PREPARE signals intent to COMMIT, and a successful PREPARE is a promise that nothing will impede a later COMMIT. It's a right-of-way issue; whoever PREPAREs first wins the game of "chicken", all other transactions will be forced to yield if database constraints can't be verified for them. Someone has to give, and it won't be whoever has already PREPAREd. It's similar to a lock -- just because someone rolls back a transaction that was holding a lock doesn't mean they're "wrong" for having held the lock in the first place and prevented you from performing your activity, it's just a natural part of the system. The worst case scenario is that another transaction attempts to commit, gets stuck in limbo, you can't commit because between the two of you, if you both succeeded, database constraints would be violated, so you're not allowed to commit; they later rollback from limbo, and assuming you're still around, you can now commit (they're no longer prepared, so the constraints won't have a problem with what you're doing, at least not because of the other guy.) That's not so bad, I think.

Yes, guessing what constraints need to be checked is certainly a problem; the way I'm using Firebird's on-commit trigger is specifically that: I create my own (carefully-crafted) list of things to check, and the trigger runs procedures to go through and verify all those items. I still have to do all of that work, but I have an opportunity to do so. Providing the ability for those constraints to see across transaction boundaries would mean it could be even more correct than it is; the changes made by other prepared/committed transactions no longer need to be verified, as their on-commit triggers have already succeeded, I only need to check my own, but I can now see both my modified data and everybody else's just for a moment, right before I commit. It doesn't require the database to be any smarter about constraints and what can cause them to fail; so long as you have the ability to run read-only code with visibility locally to all prepared/committed data, as part of the PREPARE action (and therefore no other transactions can PREPARE while this code is running), where it can raise an exception if anything's wrong ... I think you're good to go. It'll still be a pain to setup temporary tables, lists of things to check, etc. but at least it'd be doable. And if nothing else, it could reverify "everything" (no list of things to check, just run a query joining between tables, looking for anything wrong). Not necessarily the most efficient method, but I'm only worrying about correctness today.

Are you free to elaborate on your overlap constraint implementation? Even finding overlaps isn't a terribly efficient operation, unless you're also using a form of spatial indexing on your custom datatype (date ranges are just 1-d "boxes", and can be treated as rectangles in a euclidean space, thus k-d trees and other such indexing methods should be helpful.)

Re:Yeah, but does it have sub second Timestamps?

[jadavis]

I think I disagree on 'rollback prepared'; PREPARE signals intent to COMMIT, and a successful PREPARE is a promise that nothing will impede a later COMMIT.

If you have a table "employee" with a FK "department_name" referencing department, and you have two transactions:
1) INSERT INTO department VALUES ('accounting', ...);
2) INSERT INTO employee VALUES('joe', ..., 'accounting');

Your proposal is to allow #2 to commit so long as #1 has prepared. But what do people see in the window in between when #2 commits and when #1 commits? They would see an employee without a department.

Are you free to elaborate on your overlap constraint implementation?

Here's my original proposal:

http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php

Even finding overlaps isn't a terribly efficient operation, unless you're also using a form of spatial indexing on your custom datatype

I am.

My reply is somewhat brief because I am on my way out the door.

Re:Yeah, but does it have sub second Timestamps?

[Unordained]

Ah, I see what you mean, though it's more specific than that; what we'd like to say is:
- if the changes you're trying to commit conflict with changes already prepared elsewhere, you must fail;
- if the changes you're trying to commit rely on changes that have been prepared but not commited, you must still fail.

Therefore, the rule is that deferred constraints checked during PREPARE must succeed:
- within the context of your transaction, plus all committed transactions (that is, even if the other prepared transaction rolls back, yours can still succeed, standing on its own), and
- within the context of your transaction, plus all committed transactions, plus all prepared transaction (that is, if you all commit, the sum of your changes is still valid)

I believe that satisfies both of our requirements.

Thanks for talking through this, it's been constructive (at least for me). Good luck with getting the range constraint implemented, sounds useful. Spatial indexing is certainly an area FB falls behind PG on.

Re:List of demands

[Bill, Shooter of Bul]

hmm.. Yeah, After looking at the MERGE INTO issue I understand what you are talking about. There really isn't anything equivalent in MySql, despite the wikipedia article's claim to the contrary. The MySql alternative listed there INSERT .. ON DUPLICATE KEY UPDATE only works if a duplicate key would be created. There are situations I am dealing with right now that show the limitations of that approach. I'll talk with my people and see if I can't come up with an equivalent solution.

On the other hand, the MERGE INTO can lead to so complex of a statement, it seems like a magnet for deadlocks, if not properly used. Foreign Key's are going Storage Engine Independent in 6.0. I know what you're referring to with the stored procedures/ triggers thing. I'm of the anti stored procedure school this month anyway, but if I weren't I could see how that could be an issue.

Sorry I switched to BSD licensed PostgreSQL

[Anonymous Coward]

Sorry I switched to BSD licensed PostgreSQL.

Triggers

[iron-kurton]

The only thing that I look forward to in 5.1 is the addition of triggers for non-root users. I've fought many a battles with hosting providers wanting to charge me upwards of $120/hr to put my triggers in place as root because MySQL didn't allow regular users to run it.

Now, finding a hosting service willing to upgrade to 5.1 within a year after it's released is going to be a new bat

Re:Triggers

[gbjbaanb]

If you're that much of a "power user", you might just get yourself a virtual or dedicated server and do whatever you want without the hassle.

Re:Triggers

[Jellybob]

He could, but then he's also got to manage that server, instead of doing his real job.

A managed server is probably the best bet, but they cost big money for anything useful, and you'll probably get some salesman trying to sell you an entire "solution", instead of just what you wanted.

Re:Triggers

[Anonymous Coward]

Or he could simply use a shared server that gives each user its own database instance, so he has access to the root user account. Mine costs an astronomical £5 a month...

Re:Triggers

[gbjbaanb]

In life, you have choices:

a. get what you're given
b. get what you pay for.

choose one, and never complain because you can't mix and match.

Re:Triggers

[encoderer]

Since when does using a fricken trigger make one a "power user."

Honestly, if I were running a hosting provider, I wouldn't allow triggers just because they slow everything down so much.

I mean, I use them often, but it's very easy to murder server performance by trying to do too much in a trigger (or doing it poorly).

IIRC, the MySQL docs say that the most simple trigger adds a 10% overhead.

Re:Triggers

[gbjbaanb]

a power user is defined as someone who wants to achieve more than the general system readily allows for. So, in this case the OP is a power user - a normal user would accept that root-user triggers are not available, as he wants that power, he's a power user. (in a shared hosting environment that is, I wouldn't call him that if he wanted to add triggers to his own Oracle DB!)

version names

[Aggrav8d]

Bah! I misread as "for a final RC for the MySQL 5.1 server, Monty Widenius" and thought it was the latest version name, like Hardy Heron or Fallacious Ferret or Mr. Ed or something.

Thanks for the correction

[msimm]

And anyone who likes to bitch about MySQL deserves an Oracle bill. MySQL (et al) might not be perfect but they are open (improve it) and free (yay I can afford to pay for support *and* still pay for hardware and development!).

Re:Thanks for the correction

[Tim C]

And anyone who likes to bitch about MySQL deserves an Oracle bill.

Or they could use Postgres...

Re:Thanks for the correction

[stoolpigeon]

yeah - that's asinine, jumping from mysql to oracle. and frankly, there are some things that only a product like oracle can do. but between mysql and oracle there are many open and closed solutions.

Re:Thanks for the correction

[MikeBabcock]

Which has easy to use multi-server replication now?

Re:Thanks for the correction

[msimm]

Or they could bitch about Postgres...

There, fixed that context for you.

Flavor trolls. The subject (and my personal current interest) being MySQL I figured you kids would be smart enough to read the et al as an indication that MySQL is one of a number but I guess that wouldn't get you modded informative.

Re:Thanks for the correction

[msimm]

Read better.

Re:Thanks for the correction

[stoolpigeon]

And anyone who likes to bitch about MySQL deserves an Oracle bill.
 
What did I miss? MySQL has huge problems that are solved by many products that don't cost anything near what enterprise Oracle costs. And in fact, many are free.

Fascinating

[hvm2hvm]

They released a new version, woo!

Re:SLASHDOT SUX0RZ

[vegiVamp]

Rip, indeed.

BTW, here are the 3+ year old bug reports

[level4]

"Official" one from Feb 2005:

http://bugs.mysql.com/bug.php?id=8523

And here's another one going back to Nov 2003, which was strangely marked as a dupe of the above:

http://bugs.mysql.com/bug.php?id=1764

Should have put those in the original comment; apologies for my laziness.

Actually...

[encoderer]

...The only thing that really "breaks" from 4 > 5 is database permissions.

And most (all?) shared hosting are handling permissions at their admin level by necessity.

The first time I did this upgrade, probably 2005 or so, I was genuinely surprised at how painless it is.

And the pain-points that are left are SO worth it. MySQL 4 is a toy. It's worse than Access.

And we're not just talking about the lack of "advanced" features like triggers, sprocs, udf's. We're talking about no support for things like nested SELECT's. It's atrocious. The query optimizer is absurd. IIRC, there is actually a performance difference if you join in the WHERE clause opposed to an explicit join in the FROM clause. Now, I'm all for "proper" sql, meaning joins SHOULD be explicit. But the fact is that this query:

select u.fullname, p.phonenumber from users u inner join phonenumbers p on u.userid = p.userid

is logically identical to:

select u.fullname, p.phonenumber from users u, phonenumbers p where u.userid = p.userid

and for a query optimizer to create a different query plan for them is just inexcusable.

MySQL 4 is the reason so many people think poorly of the DB.

circular logic

[msimm]

many products have huge problems that are solved by MySQL that don't cost anything near what enterprise Oracle costs. Anyway: MySQL (et al). You miss the implied others and seem to jump directly into providing consultation (there are some things that only a product like oracle can do) and explicit (there are many open and closed solutions) purchase advice.

Bravo.

SQL Server

[Anonymous Coward]

There's databases other than SQL Server still around? Geez, die already.

Actually, ease of installation matters a LOT

[CurtMonash]

As I previously stated, and you seemingly missed, ease of installation is not a factor in any way, shape, or form, for describing MySQL's technical capabilities, or lack thereof, as a RDBMS.

Two of the most important characteristics software can have are ease of installation and ease of use.

Curt Monash

Re:Actually, ease of installation matters a LOT

[GooberToo]

Two of the most important characteristics software can have are ease of installation and ease of use.

Which is not topical in the least. If you bothered to follow the thread it would be completely obvious. Lots and lots of fish lovers here. Even if it were topical, and it's not in the least, difficulty of installation from 5+ years ago, assuming you are attempting to Win32-PostgreSQL bash, which has long been addressed, is simply not a factor. Ease of installation on Unix/Linux platforms has always been easy.

To be absolutely clear, installation has ZERO to do with technical capabilities and lack of ANSI compliance, which are the sole reasons knowledgeable people dislike MySQL. This has been made very clear in several posts in this thread.

If on the other and, MySQL users truly believe installation is the sole reason they are stuck using an inferior product, then you guys need only do a couple of mouse clicks to be educated. MySQL is far from being the only easy to install, freely available RDBMS. If this is really the case, MySQL users come off even more irrational and ignorant than even. As this is unlikely to be the case and installation issues are not a concern when contrasting the competition, there does not exist a valid reason to mention the topic again unless one wishes prove they are unworthy to even discuss the topic at hand by hammering home one's lack of comprehension skills.

Re:Actually, ease of installation matters a LOT

[CurtMonash]

To be absolutely clear, installation has ZERO to do with technical capabilities

I just wanted to separate that quote out from the rest, so that I could marvel at the pureness of its stupidity.

Incidentally, I happen to share your evident opinion of the relative merits of Postgres and MySQL. Even so, your understanding of reasonable software product evaluation criteria seems to be exceeded only by the civility of your discourse.

CAM

Re:Actually, ease of installation matters a LOT

[GooberToo]

I just wanted to separate that quote out from the rest, so that I could marvel at the pureness of its stupidity.

I'm sorry, but I can't help but laugh. Wow! Your comment is staggering. Seriously, your comments speaks volumes about you. So according to your um, "unique logic", an application which is difficult to install lacks technical capability. Likewise, the inverse is also true; applications which are easy to install have every technical capability. To be absolutely clear, your position is ease of installation is proportional to technical capability.

Applying your charming logic to other domains, houses which are difficult to build make for extremely poor houses. I'm sure ever mansion owner in the world agrees with your um, logic. I'm sure the military agrees with you on plane building too. Shesh. Wait...waiting...that's a good laugh. Hehehe. That's funny. That's some great insight you have there! Please, tell us more.

Considering they are completely orthogonal concepts yet you insist they are closely related, it is completely obvious why you can't grasp simple concepts. It's obvious why you like MySQL.

For everyone's sake, it best that you endorse MySQL so people wake up and begin to distance themselves from both you and that product. So please, loudly continue to voice your pearls of wisdom for all to hear so we all understand MySQL as the best RDMBS in the world.

That's too funny but I'm sure you won't see the humor. Trust me, normal people thank you're funny...very...

Re:Actually, ease of installation matters a LOT

[HAWAT.THUFIR]

To be absolutely clear, installation has ZERO to do with technical capabilities and lack of ANSI compliance, which are the sole reasons knowledgeable people dislike MySQL.

if you want ANSI, what's wrong with: http://dev.mysql.com/doc/refman/5.0/en/ansi-mode.html ? -Thufir

And that's why my blogs run on MySQL

[CurtMonash]

th respecting understand this simple fact.At the end of the day, with so many excellent relational databases available at zero or little cost, choosing MySQL as your database speaks poorly of you. Just about any database is better than MySQL.

True though that may be, there are quite a lot of people running databases not because they want to develop a new application, in which case, yes, indeed, "choosing worst options speaks poorly of them," but rather because they want to run Application X, where someone else (whom we might speak poorly of!) wrote Application X to only be compatible with the "worse options."

I'd dearly love WordPress to run on Postgres. But it doesn't. And so I'm a MySQL user at multiple sites.

Curt Monash