27 October 2011, 4:47

I ask this reluctantly, but... I am now up to three significant sites that started out in the earlier days of Mahara and have been upgraded in numerous steps up to 1.2.something and now won't go any further. At least not without a lot of drama. They are all MySQL sites.

A cursory inspection reveals that to a greater or lesser degree their schemas have drifted apart from the schema of a 'clean' install of the same version. In most cases it's just constraints missing but some actually have completely missing tables!!

Even fixing this doesn't get me out of the woods as I then have horrible problems (documented elsewhere) getting the site to upgrade without silently 'crapping out' at some point. 

My nervousness is that MySQL is not, or hasn't been, or won't be well supported or tested. New sites are going on PostgreSQL but I'd really like someone to give me an 'honest' (and I don't mean this in a bitchy way) opinion of whether I should be doing whatever it takes to get these older sites over to PostgreSQL?

I don't want to even think about the politics as, in some cases, we had a struggle to get the Microsoft houses to use MySQL :)

27 October 2011, 6:12

Hi Howard,

From my personal experience of being a core mahara developer, MySQL has been a bit of a second class citizen. Most of us don't develop on MySQL though we do all try and test any patches with it.

In the past, MySQL support has been patchy and we always have, and probably always will, recommend people use Postgres over MySQL. MySQL just doesn't cut the mustard.

That said, we've made quite a few changes to our processes recently which should help to fix these kinds of issues. For starters, we've moved to a new review system using gerrit. If you take a look at https://reviews.mahara.org you can see it in action.

For any proposed change to Mahara to be accepted it needs to be peer reviewed by another developer. As part of this, we're also trying to make sure that any change is tested on both Postgres and MySQL. I'd say that this is working pretty well and is really improving the quality of Mahara code.

As part of this, we also run a number of tests using a system called Jenkins. Every change to mahara is pushed through a series of basic tests and can't be committed if these tests fail.

Another change which is already partially implemented is the addition of unit tests for Mahara. We've now got the core functionality in place to write and run unit tests. These allow us to test Mahara functionality and ensure that we don't introduce regressions of any bug. I still need to modify the mahara release process to handle these, but once this is done the intention is to do the following for every change to Mahara:

• install a clean copy of mahara on Postgres and run all unit tests; then
• install a clean copy of mahara on MySQL and run all unit tests.

In addition to this, I plan to test for any database changes (e.g. change to a db/upgrade.php file), and to install a clean copy of the previous version of mahara, test the upgrade, run all unit tests. This will again be done on both Postgres and MySQL.

As part of this, I'm also considering the best way of comparing the upgraded database with the freshly installed database. This should highlight any changes which lead to databases differing between upgraded instances of mahara, and freshly installed instances.

My hope is that we'll be able to run all of these tests for every commit to mahara using the Jenkins framework. This will probably be pretty heavy-weight, but what a clock cycles for ;)

If you want to get involved and to help improve the MySQL support, you could also take part in the review process - it's open to anyone and there are instructions available at https://wiki.mahara.org/index.php/Developer_Area/How_to_Review_Code. If you review a change and find a bug, you can reject the change and add a comment to say that it doesn't work for you ;)

Hopefully, with these changes, we'll be able to avoid repeats of these past issues.

Andrew

27 October 2011, 15:59

Howard, I agree with Andrew.  It's always been a bit of a second class citizen.  But most of us have MySQL development instances, and we do test new code in MySQL to make sure it works.

But as far as I know, no one in the core development team is responsible for running a large MySQL site, so the upgrades get a lot less attention than they should, and there has historically been a pattern of MySQL upgrade problems in these forums soon after every major release.  We could really use some help in this area.

Having said that, I suspect the schema differences you mention are there in Postgres too, and that your problems with missing tables are more likely to be due to those sites having started out from old unstable branches, as you mentioned on the other thread.

04 November 2011, 6:11

Thanks for the replies. 

Actually, the worst site is one that (I'm quite sure) has never been near an unstable branch.

I'm not in any way wedded to MySQL. Basically, new sites are going on Postgres but it's knowing what to do for the best with the older sites. As I was saying, where the client is running the database service it's a big deal to have to say "can you just switch from MySQL to PostgreSQL please!".