It's a shame that the core team still feels this way. In all likelihood, PostgreSQL is mostly used on Linux, FreeBSD, Solaris and AIX. All of those have good threading support.
It would be nice if PostgreSQL could adopt a model where each query still gets its own process, but that process can spawn threads to handle parallelizable operations. Many aggregate functions are easy to parallelize, and there have been documented parallel hash-join algorithms since the early '80s. Assuming you have enough I/O capacity, such a setup would be a lot faster than today's PostgreSQL.
"When Apple releases some good FOSS code (either new stuff, or improvements to existing stuff), then some complimentary Slashdot articles will be appropriate."
I forgot to mention that Sun also supports SunFreeware, a volunteer project which packages oss apps for Solaris. They've also given some support to Blastwave, a similar project.
Sun did most of the HIG testing for GNOME. They open-sourced OpenOffice. They developed NetBeans. They've developed an open-source XACML processing engine (http://sunxacml.sourceforge.net/). They developed an open-source connector for Evolution and their Java Calendar Server. They open-sourced Looking Glass, the Java 3D API and JXTA. Their grid computing system, Sun Grid Engine, is open-source.
Further, they've involved in several smaller projects. Check out http://www.sunsource.net/ for more information. Oh, and they're a member of the Open Source Development Lab.
Is that good enough for you?
Further, Sun has developed several technologies which have been widely adopted by other Unix vendors, such as NFS and PAM.
While Sun doesn't get a lot of media attention for their open-source work, they do contribute a lot.
UDP traffic is pretty lightweight. ZeroConf is basically just some ICMP traffic (when the nodes are assigning themselves addresses) and then DNS. Apple's implementation will aggressively cache query results, and the devices incremementally scale back their announcements.
Another nice feature is that nodes can cache the results of other nodes' queries. Since all of the DNS traffic is mulitcast on the local subnet, every node sees every query and every response. Apple's code expolits this to further reduce the need for duplicate queries. It's a pretty nice setup.
Rendezvous is actually three technologies: link-local IP addressing (which lets machines assign themselves IPv4 addresses without a DHCP server (IPv6 already has this ability)), multicast DNS to find other services on a network, and DNS Service Records to reterieve information about those services.
Apple's implementation of ZeroConf only works on a single subnet. The current draft of the link-local RFC requires that ZeroConf-aware IP stacks reject any IP packet with a link-local address and a hop-count greater than 1.
But, the other 2 parts, multicast DNS and DNS Service Records, can cross subnet boundaries. So, it's possible that you could create a self-updating directory of machines on a multi-layered network, perhaps by using Dynamic DNS along with the other parts of ZeroConf.
Actually, Rendezvous/ZeroConf works by using mutlicast DNS on the local subnet. It also extensively caches responses and uses incremental falloff of queries and announcements. It's pretty network-effecient. Check out the ZeroConf website for the draft RFCs.
I suggest you check out Blastwave. They've created a Debian-esque wrapper around Sun's package format and have a network-aware installer. So, to install, say, PostgreSQL, you just do `sudo pkg-get install postgresql` and it will connect to a repository, fetch pgsql and its dependecies. You can also upgrade all of your Blastwave packages by doing a `sudo pkg-get upgrade'. It's pretty nice. They've got a decent amount of packages available.
Sun has announced that GNOME will be their new default desktop. In fact, I believe they are porting Java Desktop (which is GNOME with a Sun theme) to Solaris.
Regarding speed, have you checked out Solaris 10? It's a lot faster than 8 and 9. Sun is making the betas of 10 available for free - check out Solaris Express.
Also, an Ultra 5 is hardly an ideal system to use. It's about 7 years old, and even then was extremely low-end. I used to use one as a Kerberos server. It worked fine as a lightweight server, but I'd never use it for interactive work. Linux is probably faster than Solaris on it, but Solaris is hardly optimized for that level of system.
The big difference here is that the Linux developers themselves consider 2.6 to be the stable, current production version. The MySQL developers do not consider 4.1 to be stable or production-quality. Their own web page says this.
How do you figure that Firebird is "way ahead" of PostgreSQL? It's better than MySQL sure, and it has some abilties that PG doesn't (a Windows version and it can be embedded), but I wouldn't say that it's "way ahead."
Why do so many MySQL zealots keep claiming that 4.1 and 5.0 are ready for production? Even MySQL AB doesn't make such a claim. Their web site clearly states:
Production 4.0.18 Development 4.1.1 Preview 5.0.0
So saying that "new installations should use 4.1.x" is like saying that everyone should have run Linux 2.5.x, or the latest CVS version of Apache.
"Probably for the same reason that they don't switch to PostSQL -- massive investment of time in learning MySQL (we're talking years, here)"
But I thought MySQL's claim to fame was how easy it was to use?
Why not just use PostgreSQL and avoid all of this nonsense? It's BSD licensed, free for everyone, there's a very good user and developer community built up around it, the docs are excellent and it just plain works.
This is a trial rollout of Napster. It's only open to students living in the dorms. That's about 15,000 students. When you take that under consideration, it's not that bad. Also, the service only "officially" launched 2.5 days ago, and it's the first week of classes. Give it a little more time before you go passing judgement.
"The problem is PSU has not lifted the bandwidth restrictions for Napster. So, it's essentially better to steal the song once than to stream it twice. I know you can download them in DRM form, but streaming makes more sense if you can't burn them."
It's actually pretty unlikely that you'll eat up any sizable portion of your off-campus bandwidth with Napster. Napster has installed a 1 terabyte caching server on the Penn State network. This server has been pre-warmed with 90% of Napster's songs. The cache is adaptive, so as songs' popularity change over the semester, the cache's contents will change to reflect that. This server will handle the majority of requests. You only use off-campus bandwidth if you request a song not in the cache.
It is you, sir, who insist on making this a religious argument, not I.
I never said MySQL was "evil," or referred to the employees of MySQL AB as "evil overlords." All I have said (many, many, many times) is that MySQL has many flaws. These flaws, taken together, are so severe that it's not a good idea to use it. It's great that it's easy to use and setup, but if it doesn't protect your data, then it's not worth using.
Secondly, as I have said before, just because a lot of people use MySQL doesn't mean that it's a good product. Lots of people use Windows, lots of people smoke cigarettes, lots of people do lots of unwise things. It isn't a valid line of argument to say "lots of people do X, therefore X is acceptable."
Further, as I have said before, the fact that you have never seen MySQL print an error message does not mean that it's a good product. MySQL tries very very hard to never print an error message. It will even lie to the user before it will print an error message. If you've somehow managed to avoid being affected by the issues outlined on the MySQL Gotchas page, good for you. But it's been my experience, and that of a great many other people (not all of whom use PostgreSQL) that concocting workarounds to avoid the Gotchas is not worth it.
And I never said that "here's no possible way that all those millions of people could actually be using MySQL in their applications and products" - only that it's is monumentally unwise to do so. That so many do is only indicative of the poor state of relational database education in the industry. And before you again accuse me of being some elitist snob in my ivory tower of relational purity, realize that I have had to deal with far too many clueless MySQL "DBAs" who eventually end up asking me stupid questions like "do primary keys have to be unique" (that is a real world example). With uninformed admins like this, it's no wonder that so many are so easily duped by MySQL AB's marketing campaign against "advanced" features (which are considered basic features in every other DBMS, even ones like SQL Lite).
This isn't some puritanical stance based on "religious" beliefs. This is how relational databases are supposed to work. See work by E.F. Codd, Hugh Darwen, Chris Date or Fabian Pascal for more information. In particular, I recommend Fabian Pascal's book _Practical Issues in Database Management_. Yes, it's possible to setup a database without referential integrity or transactions. But doing so is asking for trouble, and it's been my experience that nearly every database done so will eventually have serious problems because of it. Again, that's a real-world concern, not some abstract, academic one.
I have no ire with MySQL users, nor do I think they are all stupid or evil. Nor do I claim that all PostgreSQL users are wonderful people. My problem is with the undereducated masses who think MySQL is the greatest thing since sliced bread. I have no problem with other DBMSes. My ire is reserved for MySQL, since I've been forced to deal with its brokenness far too much.
No doubt you will post yet another post claiming that I'm some rabid PostgreSQL "righteous religious" zealot who foams at the mouth at the mere mention of MySQL. Do as you wish. Your blather will henceforth fall on deaf ears.
I'm going to respond one final time and then I give up.
This is not a religious argument, and I am sick to death of people trying to portray it as such.
Just because the MySQL daemon process does not crash does not mean that MySQL is a reliable datastore. I simply do not trust MySQL to reliably store data, since it performs so many silent modifications to both the table schema and to input data itself. These design flaws are well documented in the MySQL Gotchas page. You claim that you've used MySQL for several years without incident. I've also used it, both 3.23 and 4.0.1x, for over a year and have had nothing but problems with it. (As an aside, I don't know how you're claiming that PostgreSQL has had stability problems "until recently" -- I've run several 7.1, 7.2, 7.3 and 7.4 databases for about 4 years with no stability or performnce problems at all.)
If a DBMS can't do exactly what I instructed it to do, it should raise an error and abort. It should not silently modify data or make a random guess about what to do. This is not desirable behavior. Frankly, this is a lesson that every programmer is taught in their first compsci class. The problem is that MySQL wants to be newbie-friendly, so it silently passes errors. This statement does not make me some "purist" who's out of touch with the real world, it's just how SQL is designed.
SQL is not HTML and it should not be parsed as such. In HTML, if your browser comes across a tag that it doesn't understand, then it skips it, and there's no harm done. The HTML language is designed to behave this way. SQL is not. In SQL, if your DBMS comes across a SQL command that it cannot handle, it must immediately abort. The problem is that MySQL doesn't. So if you're using MyISAM tables, and you issue a START TRANSACTION command, MySQL will happily print "OK" and continue on its merry way. Same if you try to define a foreign key, etc. This is not desirable behavior; it is clearly a design flaw. Anal-retentive error checking is absolutely appropriate for a DBMS.
When I read "MySQL attempts to make reasonable assumptions in situations where different clients may have varying ways of making a query", I nearly fell out of my chair. MySQL's handling of default values and NULLs is so incorrect that it's not even funny. MySQL seems confused about the value 0, the empty string "", the current timestamp and a NULL (which is not a value). Again, see the MySQL Gotchas page for examples. Its habit of (silently and automatically) creating default values for every column effectively "castrates" any NOT NULL constraint. This behavior also makes writing portable applications very difficult - since no other DBMS takes such liberties with its inputs. Of course, other DBMS also don't lie about performing operations...
I'll end with one final example of silent error passing in MySQL 4. Say you're try to create an InnoDB table. If you have "skip-innodb" enabled in my.cnf, then MySQL 4 can't create an InnoDB table. But, if you do a CREATE TABLE foo(blah) TYPE=InnoDB; MySQL won't bother to print an error message. It won't tell you "Hey, I can't create an InnoDB table, b/c you disabled support." No, it will instead print "OK" and then go and create a MyISAM table. So, you asked to get a table with one set of behaviors, but you ended up getting one with entirely different characteristics. I encountered this bug at work a few months ago on a MySQL database I inherited from a previous "DBA": I started wondering why certain operations weren't failing, and eventually traced down the problem. (This, by the way, is yet more evidence of MySQL's questionable design of having different table types which behave differently. The hallmark of relational databases was supposed to be independence from physical storage methods; MySQL has gone and reintroduced these dependencies).
You started this thread by saying that you question what niggling problems remain in PostgreSQL because some random Slashdot post mad
This isn't being childish at all. MySQL isn't standards-compliant. They've put out a web page saying that at some point in the future, they *might* be standards-compliant if it doesn't slow down the DBMS too much. That's not a committment to standardization. The RDBMS market is already fragmented enough with proprietary SQL syntax - MySQL doesn't need to contibute to that mess.
As for the rest of your post, I have to disagree. Yes, a lot of people use MySQL. So? You can make MySQL sort-of work, but you have to write a lot of extra application code to do it, and you still lose a lot of capabilities as well as safety. If you're going to the trouble to install and configure MySQL and design a database, clearly you care about your data. Why use a DBMS that has extremely limited data integerity abilties? This isn't some pie-in-the-sky academic argument; it's very much "real-world".
Unfortunately, most databases today are installed and run by people who aren't familiar with database management. Often, they're setup by programmers or sysadmins who have only read a HOWTO on setting up a DBMS. This is unfortunate, since it often means that they miss out on more advanced features which make their lives easier. Yes, I said it. Advanced features (can) make life easier. They aren't just there to satisify elitest database snobs sitting in their university offices. Doing referential integrity checking in the database itself is much, much easier than doing it in your application code. Doing input validation in your database can save you from corrupted data, which can save you a lot of time in the future. It's better to do just a little more work upfront and not have to worry about a whole range of problems later.
They don't realize that they are putting their data at risk by not using foreign keys and transactions. Add to this the fact that MySQL silenty alters data and table definitions, and silently passes errors, and you have a disaster waiting to happen. I've seen MySQL databases which contain corrupted data, but the DBA adamantly claims that the database is fine because "MySQL has never printed an error." Of course it hasn't - it never does!
Your argument that MySQL is OK because it's popular just doesn't hold up. As an analogy, consider filesystems. The FAT filesystem is probably the most popular fs in use, but I'd argue that it's a toy FS compared to any Unix FS, and that it certainly shouldn't be used in nearly ever case where it is.
What's sad is that database design isn't some black art. Relational databases were designed for use in the real world. In the late '70s, researchers at IBM were unsatisified with the crappy database products on the market then and invented relational technology to address those problems. It has never been some ivory tower, academic technology. Further, the basics of sound database design aren't difficult to learn. Like I said in a previous post, SQL for Dummies covers everything you need to know for designing and running a basic database.
My gripe with MySQL is that it doesn't implement these things properly. Foreign keys and transactions just don't work right in MySQL. For years, MySQL AB claimed that it didn't need "advanced" features like transactions and referential integrity, and they hyped up ease of installation and speed. Now that they have finally realized that they do need these capabilites, they're not implemented correctly. I fail to see why so many people defend this software, especially when there are other open source databases (PostgreSQL and Firebird come to mind) that have had mature, working "advanced" features for years! Why wait for MySQL 4.1 or 5.0 to stabilize (which could take years), when you can have these capabilities today with other free software?!
MySQL AB have not eschewed compliance with the SQL standards, as you can see for yourself from their documentation. To quote: "We aim toward full compliance with SQL-92/SQL-99; there are no features we plan not to implement."
Oh, I'm sorry. I thought standards-compliance meant actually implementing code that conforms to the published SQL standards, not just saying that you will do so in some mytical, unreleased future version. My mistake.
. People who say that MySQL is not a real database, or that it's just a toy, or that it can't be trusted with anything - this is just wrong.
No, it's the truth. MySQL lacks any data integrity features. It silently alters table definitions, silently truncates input, performs no range checking on inputs, etc. Just because MySQL doesn't give you an error when you INSERT new data doesn't mean that that data was stored correctly.
It works very well. If you don't like it because of certain features lacking, that's fine - you're perfectly free to dislike it.
My dislike of MySQL has nothing to do with it not implementing certain features. It has to do with it implementing things incorrectly. MySQL doesn't do transactions correctly, it botches up foreign keys, its NULL handling is horrible, etc.
Incidentally, why should it be a "disservice" to the database community to "dumb down" a database? Should databases remain the pure hallowed domain of database wizards who understand normalization and relational theory?
Um, becuaase you can't dumb down everything. I think it's great the MySQL is easy to use. But the problem is that you do need to understand a few basic concepts before you starting writing SQL. You need to understand normalization. You should understand what a foreign key is. You should have a basic idea of how transactions work. None of these concepts are terribly difficult to grasp. Hell, SQL for Dummies covers them all!
MySQL AB haven't "created a mindset" based purely on speed
Oh they absolutely have! Monty has said numerous times that he values speed above all else, since that seems to be MySQL's major selling point. He's said that he'll try and implement standards-compliant features, but if they interfere with the speed of the DBMS, then he won't. And there are droves of brain-washed MySQL "DBAs" who don't want foreign keys, stored procedures, etc to be added to MySQL because they fear the DBMS will slow down.
You may find the features you list essential, but realize that other people do not. Doesn't mean they are uneducated yokels, just that they don't need it.
No, that's exactly what it means. If developers would take the time to become familiar with the basics of database admnistration, then they would realize that referential integrity and transactions are essential to a relational database. The problem with these fly-by-night "DBAs" (whose only exposure to databases is reaading the MySQL section of the PHP manual) is that they think learning a few SQL commands makes them database experts. It doesn't. This isn't elitism. This isn't snobbery. It's the truth.
Funny that you seem so intent on bashing these mythical PostgreSQL advocates, yet you ignore all of the pure BS that's been spewn from MySQL advocates and from MySQL.com itself. They have posted clearly skewed benchmarks between their product and PG and have outright lied in claiming that their database doesn't need "high-end" features like transactions, referential integrity, validity checking on inputs, etc.
Secondly, I wouldn't say that MySQL is making "fantastic progress." Yes, v4 is better that 3.23, but a lot of the new features (transaction, foreign keys, etc) are half-assed implementations and are trivially easy to disable or bypass. This is in addition to its numerous design flaws, which are well documented on the MySQL Gotchas page. Yes, every program has bugs, but the items listed on MySQL Gotchas go beyond mere bugs - they are fundamental design flaws. It is exactly these flaws which show no sign of being fixed.
Finally, I have to say that perhaps the biggest disservice that MySQL AB (the company) has done to the database commuity as a whole is to dumb it down. They have created a mindset where raw speed is seen as the most important factor in choosing a DBMS, and further, that the DBMS should be a dumb, raw datastore, with any data processing (no matter how basic) done in the application. They have eschewed compliance with the SQL standards. They have managed to convince a large number of developers that anything beyond SELECT, INSERT, DELETE and UPDATE are "high-end" features. This is simply not true. Foreign keys, referential integrity constraints, CHECK constraints, datatypes that work (like a good DATE datatype) matter.
"If you re-read my post, you'll realize that my point was not to target specific features of PostgreSQL, but rather to bring attention to some examples that were glossed over by the PostgreSQL zealots a couple of years ago"
Actually, the 8K row limit was well-documented. Checking archives of the PostgreSQL mailing list confirms that there were many posts about it, prior to PostgreSQL 7.1 (released in early 2001) which removed the limit. In fact, there is a web page, PostgreSQL Limits, located just 2 clicks off the main PostgreSQL site, which spelled out these limits. This page has been there for years, as verified by the Wayback Machine.
Further, the need to reguarly vacuum databases hasn't (and isn't not) glossed over. It's well-documented, listed in numerous FAQs and tuning guides, and comes up all the time in #postgresql on openproject.net.
I'm really not sure where you got the idea that these aspect of PG (one of which was removed over 2 years ago) were "glossed over." Nor do I see what relevance they have today.
Stored procedures are a very common part of large systems and adding this functionality to MySQL will go a long ways in promoting MySQL use in bigger companies.
True, but there are so many other barriers to using MySQL in "bigger companies" - like its near total lack of integrity checking, its penchant for silently altering table schema and types and for silently altering data. And, frankly, I'd rather not trust anything mission-critical to some brand-new, untested implementation of stored procedures in MySQL, when other databases (PostgreSQL, Phoenix, etc) have had these abilities for years.
Slashdot Mirror
It's a shame that the core team still feels this way. In all likelihood, PostgreSQL is mostly used on Linux, FreeBSD, Solaris and AIX. All of those have good threading support.
It would be nice if PostgreSQL could adopt a model where each query still gets its own process, but that process can spawn threads to handle parallelizable operations. Many aggregate functions are easy to parallelize, and there have been documented parallel hash-join algorithms since the early '80s. Assuming you have enough I/O capacity, such a setup would be a lot faster than today's PostgreSQL.
"That is, indexes just don't cut it when you need to crunch a million rows spread throughout another twenty million."
What about partial indexes? PostgreSQL has support for them.
"When Apple releases some good FOSS code (either new stuff, or improvements to existing stuff), then some complimentary Slashdot articles will be appropriate."
Um. They have.
I forgot to mention that Sun also supports SunFreeware, a volunteer project which packages oss apps for Solaris. They've also given some support to Blastwave, a similar project.
Further, they've involved in several smaller projects. Check out http://www.sunsource.net/ for more information. Oh, and they're a member of the Open Source Development Lab.
Is that good enough for you?
Further, Sun has developed several technologies which have been widely adopted by other Unix vendors, such as NFS and PAM.
While Sun doesn't get a lot of media attention for their open-source work, they do contribute a lot.
UDP traffic is pretty lightweight. ZeroConf is basically just some ICMP traffic (when the nodes are assigning themselves addresses) and then DNS. Apple's implementation will aggressively cache query results, and the devices incremementally scale back their announcements.
Another nice feature is that nodes can cache the results of other nodes' queries. Since all of the DNS traffic is mulitcast on the local subnet, every node sees every query and every response. Apple's code expolits this to further reduce the need for duplicate queries. It's a pretty nice setup.
Rendezvous is actually three technologies: link-local IP addressing (which lets machines assign themselves IPv4
addresses without a DHCP server (IPv6 already has this ability)), multicast DNS to find other services on a network, and DNS Service Records to reterieve information about those services.
Apple's implementation of ZeroConf only works on a single subnet. The current draft of the link-local RFC requires that ZeroConf-aware IP stacks reject any IP packet with a link-local address and a hop-count greater than 1.
But, the other 2 parts, multicast DNS and DNS Service Records, can cross subnet boundaries. So, it's possible that you could create a self-updating directory of machines on a multi-layered network, perhaps by using Dynamic DNS along with the other parts of ZeroConf.
Actually, Rendezvous/ZeroConf works by using mutlicast DNS on the local subnet. It also extensively caches responses and uses incremental falloff of queries and announcements. It's pretty network-effecient. Check out the ZeroConf website for the draft RFCs.
I suggest you check out Blastwave. They've created a Debian-esque wrapper around Sun's package format and have a network-aware installer. So, to install, say, PostgreSQL, you just do `sudo pkg-get install postgresql` and it will connect to a repository, fetch pgsql and its dependecies. You can also upgrade all of your Blastwave packages by doing a `sudo pkg-get upgrade'. It's pretty nice. They've got a decent amount of packages available.
Sun has announced that GNOME will be their new default desktop. In fact, I believe they are porting Java Desktop (which is GNOME with a Sun theme) to Solaris.
Regarding speed, have you checked out Solaris 10? It's a lot faster than 8 and 9. Sun is making the betas of 10 available for free - check out Solaris Express.
Also, an Ultra 5 is hardly an ideal system to use. It's about 7 years old, and even then was extremely low-end. I used to use one as a Kerberos server. It worked fine as a lightweight server, but I'd never use it for interactive work. Linux is probably faster than Solaris on it, but Solaris is hardly optimized for that level of system.
The big difference here is that the Linux developers themselves consider 2.6 to be the stable, current production version. The MySQL developers do not consider 4.1 to be stable or production-quality. Their own web page says this.
How do you figure that Firebird is "way ahead" of PostgreSQL? It's better than MySQL sure, and it has some abilties that PG doesn't (a Windows version and it can be embedded), but I wouldn't say that it's "way ahead."
Why do so many MySQL zealots keep claiming that 4.1 and 5.0 are ready for production? Even MySQL AB doesn't make such a claim. Their web site clearly states:
Production 4.0.18
Development 4.1.1
Preview 5.0.0
So saying that "new installations should use 4.1.x" is like saying that everyone should have run Linux 2.5.x, or the latest CVS version of Apache.
"Probably for the same reason that they don't switch to PostSQL -- massive investment of time in learning MySQL (we're talking years, here)"
But I thought MySQL's claim to fame was how easy it was to use?
Why not just use PostgreSQL and avoid all of this nonsense? It's BSD licensed, free for everyone, there's a very good user and developer community built up around it, the docs are excellent and it just plain works.
Check out pgAdmin III - http://www.pgadmin.org/pgadmin3/index.php
It's a very nice, cross-platform GUI for PG.
" I think Sun is adding auto-parallelization to future compilers."
They already have this. Do -xautopar when using cc.
This is a trial rollout of Napster. It's only open to students living in the dorms. That's about 15,000 students. When you take that under consideration, it's not that bad. Also, the service only "officially" launched 2.5 days ago, and it's the first week of classes. Give it a little more time before you go passing judgement.
"The problem is PSU has not lifted the bandwidth restrictions for Napster. So, it's essentially better to steal the song once than to stream it twice. I know you can download them in DRM form, but streaming makes more sense if you can't burn them."
It's actually pretty unlikely that you'll eat up any sizable portion of your off-campus bandwidth with Napster. Napster has installed a 1 terabyte caching server on the Penn State network. This server has been pre-warmed with 90% of Napster's songs. The cache is adaptive, so as songs' popularity change over the semester, the cache's contents will change to reflect that. This server will handle the majority of requests. You only use off-campus bandwidth if you request a song not in the cache.
It is you, sir, who insist on making this a religious argument, not I.
I never said MySQL was "evil," or referred to the employees of MySQL AB as "evil overlords." All I have said (many, many, many times) is that MySQL has many flaws. These flaws, taken together, are so severe that it's not a good idea to use it. It's great that it's easy to use and setup, but if it doesn't protect your data, then it's not worth using.
Secondly, as I have said before, just because a lot of people use MySQL doesn't mean that it's a good product. Lots of people use Windows, lots of people smoke cigarettes, lots of people do lots of unwise things. It isn't a valid line of argument to say "lots of people do X, therefore X is acceptable."
Further, as I have said before, the fact that you have never seen MySQL print an error message does not mean that it's a good product. MySQL tries very very hard to never print an error message. It will even lie to the user before it will print an error message. If you've somehow managed to avoid being affected by the issues outlined on the MySQL Gotchas page, good for you. But it's been my experience, and that of a great many other people (not all of whom use PostgreSQL) that concocting workarounds to avoid the Gotchas is not worth it.
And I never said that "here's no possible way that all those millions of people could actually be using MySQL in their applications and products" - only that it's is monumentally unwise to do so. That so many do is only indicative of the poor state of relational database education in the industry. And before you again accuse me of being some elitist snob in my ivory tower of relational purity, realize that I have had to deal with far too many clueless MySQL "DBAs" who eventually end up asking me stupid questions like "do primary keys have to be unique" (that is a real world example). With uninformed admins like this, it's no wonder that so many are so easily duped by MySQL AB's marketing campaign against "advanced" features (which are considered basic features in every other DBMS, even ones like SQL Lite).
This isn't some puritanical stance based on "religious" beliefs. This is how relational databases are supposed to work. See work by E.F. Codd, Hugh Darwen, Chris Date or Fabian Pascal for more information. In particular, I recommend Fabian Pascal's book _Practical Issues in Database Management_. Yes, it's possible to setup a database without referential integrity or transactions. But doing so is asking for trouble, and it's been my experience that nearly every database done so will eventually have serious problems because of it. Again, that's a real-world concern, not some abstract, academic one.
I have no ire with MySQL users, nor do I think they are all stupid or evil. Nor do I claim that all PostgreSQL users are wonderful people. My problem is with the undereducated masses who think MySQL is the greatest thing since sliced bread. I have no problem with other DBMSes. My ire is reserved for MySQL, since I've been forced to deal with its brokenness far too much.
No doubt you will post yet another post claiming that I'm some rabid PostgreSQL "righteous religious" zealot who foams at the mouth at the mere mention of MySQL. Do as you wish. Your blather will henceforth fall on deaf ears.
I'm going to respond one final time and then I give up.
This is not a religious argument, and I am sick to death of people trying to portray it as such.
Just because the MySQL daemon process does not crash does not mean that MySQL is a reliable datastore. I simply do not trust MySQL to reliably store data, since it performs so many silent modifications to both the table schema and to input data itself. These design flaws are well documented in the MySQL Gotchas page. You claim that you've used MySQL for several years without incident. I've also used it, both 3.23 and 4.0.1x, for over a year and have had nothing but problems with it. (As an aside, I don't know how you're claiming that PostgreSQL has had stability problems "until recently" -- I've run several 7.1, 7.2, 7.3 and 7.4 databases for about 4 years with no stability or performnce problems at all.)
If a DBMS can't do exactly what I instructed it to do, it should raise an error and abort. It should not silently modify data or make a random guess about what to do. This is not desirable behavior. Frankly, this is a lesson that every programmer is taught in their first compsci class. The problem is that MySQL wants to be newbie-friendly, so it silently passes errors. This statement does not make me some "purist" who's out of touch with the real world, it's just how SQL is designed.
SQL is not HTML and it should not be parsed as such. In HTML, if your browser comes across a tag that it doesn't understand, then it skips it, and there's no harm done. The HTML language is designed to behave this way. SQL is not. In SQL, if your DBMS comes across a SQL command that it cannot handle, it must immediately abort. The problem is that MySQL doesn't. So if you're using MyISAM tables, and you issue a START TRANSACTION command, MySQL will happily print "OK" and continue on its merry way. Same if you try to define a foreign key, etc. This is not desirable behavior; it is clearly a design flaw. Anal-retentive error checking is absolutely appropriate for a DBMS.
When I read "MySQL attempts to make reasonable assumptions in situations where different clients may have varying ways of making a query", I nearly fell out of my chair. MySQL's handling of default values and NULLs is so incorrect that it's not even funny. MySQL seems confused about the value 0, the empty string "", the current timestamp and a NULL (which is not a value). Again, see the MySQL Gotchas page for examples. Its habit of (silently and automatically) creating default values for every column effectively "castrates" any NOT NULL constraint. This behavior also makes writing portable applications very difficult - since no other DBMS takes such liberties with its inputs. Of course, other DBMS also don't lie about performing operations...
I'll end with one final example of silent error passing in MySQL 4. Say you're try to create an InnoDB table. If you have "skip-innodb" enabled in my.cnf, then MySQL 4 can't create an InnoDB table. But, if you do a CREATE TABLE foo(blah) TYPE=InnoDB; MySQL won't bother to print an error message. It won't tell you "Hey, I can't create an InnoDB table, b/c you disabled support." No, it will instead print "OK" and then go and create a MyISAM table. So, you asked to get a table with one set of behaviors, but you ended up getting one with entirely different characteristics. I encountered this bug at work a few months ago on a MySQL database I inherited from a previous "DBA": I started wondering why certain operations weren't failing, and eventually traced down the problem. (This, by the way, is yet more evidence of MySQL's questionable design of having different table types which behave differently. The hallmark of relational databases was supposed to be independence from physical storage methods; MySQL has gone and reintroduced these dependencies).
You started this thread by saying that you question what niggling problems remain in PostgreSQL because some random Slashdot post mad
This isn't being childish at all. MySQL isn't standards-compliant. They've put out a web page saying that at some point in the future, they *might* be standards-compliant if it doesn't slow down the DBMS too much. That's not a committment to standardization. The RDBMS market is already fragmented enough with proprietary SQL syntax - MySQL doesn't need to contibute to that mess.
As for the rest of your post, I have to disagree. Yes, a lot of people use MySQL. So? You can make MySQL sort-of work, but you have to write a lot of extra application code to do it, and you still lose a lot of capabilities as well as safety. If you're going to the trouble to install and configure MySQL and design a database, clearly you care about your data. Why use a DBMS that has extremely limited data integerity abilties? This isn't some pie-in-the-sky academic argument; it's very much "real-world".
Unfortunately, most databases today are installed and run by people who aren't familiar with database management. Often, they're setup by programmers or sysadmins who have only read a HOWTO on setting up a DBMS. This is unfortunate, since it often means that they miss out on more advanced features which make their lives easier. Yes, I said it. Advanced features (can) make life easier. They aren't just there to satisify elitest database snobs sitting in their university offices. Doing referential integrity checking in the database itself is much, much easier than doing it in your application code. Doing input validation in your database can save you from corrupted data, which can save you a lot of time in the future. It's better to do just a little more work upfront and not have to worry about a whole range of problems later.
They don't realize that they are putting their data at risk by not using foreign keys and transactions. Add to this the fact that MySQL silenty alters data and table definitions, and silently passes errors, and you have a disaster waiting to happen. I've seen MySQL databases which contain corrupted data, but the DBA adamantly claims that the database is fine because "MySQL has never printed an error." Of course it hasn't - it never does!
Your argument that MySQL is OK because it's popular just doesn't hold up. As an analogy, consider filesystems. The FAT filesystem is probably the most popular fs in use, but I'd argue that it's a toy FS compared to any Unix FS, and that it certainly shouldn't be used in nearly ever case where it is.
What's sad is that database design isn't some black art. Relational databases were designed for use in the real world. In the late '70s, researchers at IBM were unsatisified with the crappy database products on the market then and invented relational technology to address those problems. It has never been some ivory tower, academic technology. Further, the basics of sound database design aren't difficult to learn. Like I said in a previous post, SQL for Dummies covers everything you need to know for designing and running a basic database.
My gripe with MySQL is that it doesn't implement these things properly. Foreign keys and transactions just don't work right in MySQL. For years, MySQL AB claimed that it didn't need "advanced" features like transactions and referential integrity, and they hyped up ease of installation and speed. Now that they have finally realized that they do need these capabilites, they're not implemented correctly. I fail to see why so many people defend this software, especially when there are other open source databases (PostgreSQL and Firebird come to mind) that have had mature, working "advanced" features for years! Why wait for MySQL 4.1 or 5.0 to stabilize (which could take years), when you can have these capabilities today with other free software?!
Yes, I agree, this could be the worst technology of 2003. Too much hype, and it still doesn't have the reliability of SCSI.
MySQL AB have not eschewed compliance with the SQL standards, as you can see for yourself from their documentation. To quote: "We aim toward full compliance with SQL-92/SQL-99; there are no features we plan not to implement."
Oh, I'm sorry. I thought standards-compliance meant actually implementing code that conforms to the published SQL standards, not just saying that you will do so in some mytical, unreleased future version. My mistake.
. People who say that MySQL is not a real database, or that it's just a toy, or that it can't be trusted with anything - this is just wrong.
No, it's the truth. MySQL lacks any data integrity features. It silently alters table definitions, silently truncates input, performs no range checking on inputs, etc. Just because MySQL doesn't give you an error when you INSERT new data doesn't mean that that data was stored correctly.
It works very well. If you don't like it because of certain features lacking, that's fine - you're perfectly free to dislike it.
My dislike of MySQL has nothing to do with it not implementing certain features. It has to do with it implementing things incorrectly. MySQL doesn't do transactions correctly, it botches up foreign keys, its NULL handling is horrible, etc.
Incidentally, why should it be a "disservice" to the database community to "dumb down" a database? Should databases remain the pure hallowed domain of database wizards who understand normalization and relational theory?
Um, becuaase you can't dumb down everything. I think it's great the MySQL is easy to use. But the problem is that you do need to understand a few basic concepts before you starting writing SQL. You need to understand normalization. You should understand what a foreign key is. You should have a basic idea of how transactions work. None of these concepts are terribly difficult to grasp. Hell, SQL for Dummies covers them all!
MySQL AB haven't "created a mindset" based purely on speed
Oh they absolutely have! Monty has said numerous times that he values speed above all else, since that seems to be MySQL's major selling point. He's said that he'll try and implement standards-compliant features, but if they interfere with the speed of the DBMS, then he won't. And there are droves of brain-washed MySQL "DBAs" who don't want foreign keys, stored procedures, etc to be added to MySQL because they fear the DBMS will slow down.
You may find the features you list essential, but realize that other people do not. Doesn't mean they are uneducated yokels, just that they don't need it.
No, that's exactly what it means. If developers would take the time to become familiar with the basics of database admnistration, then they would realize that referential integrity and transactions are essential to a relational database. The problem with these fly-by-night "DBAs" (whose only exposure to databases is reaading the MySQL section of the PHP manual) is that they think learning a few SQL commands makes them database experts. It doesn't. This isn't elitism. This isn't snobbery. It's the truth.
Funny that you seem so intent on bashing these mythical PostgreSQL advocates, yet you ignore all of the pure BS that's been spewn from MySQL advocates and from MySQL.com itself. They have posted clearly skewed benchmarks between their product and PG and have outright lied in claiming that their database doesn't need "high-end" features like transactions, referential integrity, validity checking on inputs, etc.
Secondly, I wouldn't say that MySQL is making "fantastic progress." Yes, v4 is better that 3.23, but a lot of the new features (transaction, foreign keys, etc) are half-assed implementations and are trivially easy to disable or bypass. This is in addition to its numerous design flaws, which are well documented on the MySQL Gotchas page. Yes, every program has bugs, but the items listed on MySQL Gotchas go beyond mere bugs - they are fundamental design flaws. It is exactly these flaws which show no sign of being fixed.
Finally, I have to say that perhaps the biggest disservice that MySQL AB (the company) has done to the database commuity as a whole is to dumb it down. They have created a mindset where raw speed is seen as the most important factor in choosing a DBMS, and further, that the DBMS should be a dumb, raw datastore, with any data processing (no matter how basic) done in the application. They have eschewed compliance with the SQL standards. They have managed to convince a large number of developers that anything beyond SELECT, INSERT, DELETE and UPDATE are "high-end" features. This is simply not true. Foreign keys, referential integrity constraints, CHECK constraints, datatypes that work (like a good DATE datatype) matter.
"If you re-read my post, you'll realize that my point was not to target specific features of PostgreSQL, but rather to bring attention to some examples that were glossed over by the PostgreSQL zealots a couple of years ago"
Actually, the 8K row limit was well-documented. Checking archives of the PostgreSQL mailing list confirms that there were many posts about it, prior to PostgreSQL 7.1 (released in early 2001) which removed the limit. In fact, there is a web page, PostgreSQL Limits, located just 2 clicks off the main PostgreSQL site, which spelled out these limits. This page has been there for years, as verified by the Wayback Machine.
Further, the need to reguarly vacuum databases hasn't (and isn't not) glossed over. It's well-documented, listed in numerous FAQs and tuning guides, and comes up all the time in #postgresql on openproject.net.
I'm really not sure where you got the idea that these aspect of PG (one of which was removed over 2 years ago) were "glossed over." Nor do I see what relevance they have today.
Stored procedures are a very common part of large systems and adding this functionality to MySQL will go a long ways in promoting MySQL use in bigger companies.
True, but there are so many other barriers to using MySQL in "bigger companies" - like its near total lack of integrity checking, its penchant for silently altering table schema and types and for silently altering data. And, frankly, I'd rather not trust anything mission-critical to some brand-new, untested implementation of stored procedures in MySQL, when other databases (PostgreSQL, Phoenix, etc) have had these abilities for years.