Forum .LRN Q&A: can .LRN support MYSQL database?

Collapse
Posted by fei ah on
can .LRN support MYSQL database?
Collapse
Posted by Rocael Hernández Rizzardini on
I think aolserver has a driver for mysql, so an openacs / .LRN installation should be able to access mysql, but .LRN itself can only be installed on postgres or oracle, .LRN is too sophisticated for the dummy mysql =)
Collapse
Posted by Bruce Spear on
I'm wondering of there is a good question here: what precisely is the advantage of using postgres to mysql for Dotlrn?  My understanding is that postgre does transactions and so insures data integrity in a way that mysql can not.  Might others remind us of other reasons?  And for that matter, what is the advantage of going to the expense of using Oracle?  Has anyone written this up in a way that might help those involve in setting up our systems and marketing?  In advance, Thanks!

Bruce

Collapse
Posted by Dirk Gomez on
In my opinion the "transaction thing" doesn't hold water. We use PG and Oracle (almost always) in autocommit mode - that is just like we were emulating MySQL's behaviours: (o transactions simply means committing every statement immediately.

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.

Collapse
Posted by Andrew Grumet on
To add to what Dirk wrote, it's important to remember that supporting multiple databases is very expensive.

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.

Collapse
Posted by Roberto Mello on
MySQL is hard to support. It deviates heavily and gratuitously from the SQL standard, for reasons that are IMNSHO, stupid and lazy.

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

Collapse
Posted by Andrew Piskorski on
Dirk, your comment on transactions is just plain wrong. There is most definitely extensive use of explicit "begin transaction" statements in both the OpenACS core and in plenty of custom user code. Just how do you think db_transaction works, after all? It's not in the toolkit just for decoration. In fact, a quick recursive grep of a CVS checkout of OpenACS shows 1075 separate occurrences of the string "db_transaction" in *.tcl files. Also, the fact that a lot of code commits after every statement isn't relevent, even that code needs proper transaction support from the RDBMS.

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.
Collapse
Posted by Don Baccus on
Let me expand on Andrew's comment, Dirk:

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.

Collapse
Posted by Nick Carroll on
Hypothetically speaking, if OpenACS was implemented in Java, would Object Relational Mapping (ORM) be advantageous in this situation?  Open source projects like Hibernate (www.hibernate.org) provides persistence for Java objects, by mapping Java objects to database tables.  They claim you will never have to write a line of SQL again... although if you read the fine print you will have to use HQL (Hibernate Query Language) for complex queries.
Collapse
Posted by Dirk Gomez on
Don, do I understand this correctly? If a MySQL process crashes while a SQL DML statement is being executed, some pieces of data remain changed while others don't? Now that is some surprising news that would rule out MySQL for almost any application...
Collapse
Posted by Roberto Mello on
As Don said: "With the original MySQL backend, atomicity at the statement level was not guaranteed."

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.

-Roberto

Collapse
12: Object Relational Mapping (response to 9)
Posted by Andrew Piskorski on
Nick, the later Java versions of the ACS did have Object Relational Mapping. Others here we actually used it could comment better, but from what I heard, it sounded like a crock. Look into Red Hat CCM, if you're interested in that.

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.

Collapse
Posted by Patrick Giagnocavo on
Just a note to Andrew and others, the ACS Java/Red Hat CCM code is now called "Byline" and is at http://byline.objectweb.org/ .