Forum .LRN Q&A: can .LRN support MYSQL database?
MySQL's SQL is very limited and there was no server-side programming language and no views about two years ago - probably some other issues as well and MySQL enthusiasts will tell you that either those features are implemented already or real soon now.
The two main differences between PG and MySQL in my opinion:
* PostgreSQL is an open source project, MySQL is a product which is given away for free.
* PostgreSQL is about 20 years old, MySQL is substantially younger.
Whenever a new feature is added in RDBMS A, someone has to port it and all future updates to RDBMS B, C and D. There's a huge complexity cost associated with this.
Most developers do their work with a single RDBMS. They might do the porting to others RDBMSes, but as far as I can tell they're not set up for cross platform testing. Folks working on the PG side might not notice this as much because that's where a lot of the new development seems to be. But it's very visible on the Oracle side. Pretty much every time I sit down to work on some new code, I have to debug the Oracle queries.
http://sql-info.de/mysql/gotchas.html lists a whole slew of things that would be unthinkable behavior in relational databases, but yet are documented behavior in MySQL. Things like NULLs not really being NULLs, the server "guessing" what kind of type something should be casted to implicitly, etc.
The above list of "gotchas" alone would make supporting MySQL extremely hard since the person would have to keep on top of development to watch for new gotchas or change of behavior of existing gotchas.
MySQL also currently lacks a reliable especification for stored procedures, which OpenACS and dotLRN rely on heavily. My understanding is that this is still something that is being worked on.
The currently stable version of MySQL (4.0), referred to as "Generally Available" on MySQL's web site (http://dev.mysql.com/downloads/index.html) lacks crucial features that are havily used by dotLRN and OpenACS: Sub-SELECTs, Stored procedures, Triggers, Cursors, Views.
Supposedly some of these features are available in development versions of MySQL. Which to use would be up to the person(s) doing the port to MySQL. However, VIEWs, Stored procedures and Triggers are only slated for MySQL 5.0.
After visiting MySQL's booth at a LinuxWorld and seeing their development roadmap, I concocted this phrase that I like to use: "MySQL: By next year supporting 15% of the critical features other databases supported 10 years ago"
Another issue to keep in mind is that MySQL users in general just tend to be less intelligent. Its userbase is usually formed of a mass of people who don't really know what a database is supposed to be, don't really know what to do about web programming, or programming in general, have only heard about some of these things but don't want to take the time to study and learn about the issues involved, and so on. Obviously not every MySQL user falls into that category, but most of them do.
Everything else Dirk, Andrew, and Roberto said above seems to be spot on.
From the simplest, most naive point of view, MySQL vs. PostgreSQL more or less boils down to:
- Q: Does PostgreSQL have any advantages over MySQL (at least for uses like OpenACS)?
- A: Yes, it has many advantages over MySQL. I could go on and on... RTFW.
- Q: Does MySQL have any advantages over PostgreSQL (at least for uses like OpenACS)?
- A: Basically, no, it doesn't.
EVERY autocommitted statement is executed within its own implicit transaction. That means that an INSERT or UPDATE is guaranteed to execute to completion or to roll back automatically (say, if your machine crashes). With the original MySQL backend, atomicity at the statement level was not guaranteed.
This misunderstanding of the role of transactions has been very common in the MySQL world ... we know better here :)
Having said that, the InnoDB back end for MySQL is fully transactional and AFAIK guarantees atomicity for individual statements as well as explicit transactions. Today, the major issue would be the still relatively weak support for more complex SQL constructs in MySQL.
For our project, the really BIG issue is the expense involved in maintaining multiple databases. We can't, as a project, really afford to maintain more than one open source RDBMS. Postgres gets the job done, and is much closer to Oracle in its extension set and follows the standard closely, plus we already support it so it wins by default.
The original (and default) MySQL backend is the MyISAM table type. With MyISAM there is no atomicity guarantee whatsoever. There's a utility that tries to repair broken databases with MyISAM.
You only have some atomicity with the InnoDB and BDB table types.
Personally, I've never yet seen a single convincing example of why so-called "Object Relational Mapping" would ever be of any real use at all.