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.

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.

-Roberto

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.

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.