26 October 2009, 8:13

Hi Penny Leach,

Your contribution in Mahara and Moodle is great and I admit the fact that your knowledge on this topic is greater than mine. I only have user's perspective on this subject.

If you say MySQL does not support  "transactional DDL statements" then it must be true. As far as my experiance with OSS is concerned most softwares (Moodle, Drupal, Joomla, PhpBB) does not have a simmilar probelm with MySQL. We are currently maintaining over 3000 Moodle installations and no user ever reported any simmilar issue with Moodle.

Even if for some reason (network level or something else) Moodle upgrades break it always stops on the error, it shows the query which failed so one could troubleshoot further. Moodle always is able to continue once the issue is fixed and we never had to dump the database or never had any lost data on servers. It might be that Moodle does not relay on "transactional DDL statements" or it does not send mixture of schema and data statements rather it sends simple small DB query one by one.

26 October 2009, 10:34

Sorry, but that is just not true.

Firstly, Moodle and Mahara upgrade in almost exactly the same way. The underlying database abstraction layer is derived from the same code.  Mahara does wrap its upgrade in a transaction, but I can assure you that both of them are a mixture of DDL and DML, and both of them are many many single queries.

The Upgrading page in the Moodle docs page clearly states that it is imperitive to back up your data before upgrading.  This is because if the upgrade fails, your database will be left in an inconsistent state.

Moodle has savepoints during the upgrade, from which it can continue.  However, if your upgrade fails in between any of these two savepoints, it will retry from the most recent one, repeating the same statements that have already occurred.  In reality , while you could set a savepoint after every single database query, Moodle doesn't do this. There are 101 calls to upgrade_main_savepoint in the main Moodle upgrade routine, from 3191lines of code.  There are none in any of the core modules, enrolment plugins, question types, or blocks.  This means that you will end up with your database in an inconsistent state if the upgrade of any of those plugins break, or in between savepoints in the main upgrade routine.

Furthermore, I have had many Moodle upgrades over the years where SQL statements had caused errors (especially in the early days), and Moodle still happily informed me that the tables had been successfully upgraded.   I can even write code for you, to demonstrate this, if you would like.  The success of an upgrade call in Moodle is determined by the return value of the upgrade function, not on whether an upgrade has actually failed or succeeded.

I can easily write code that says

 function some_upgrade($oldversion) {

    // some code that doesn't work

    return true;

}

And Moodle will report success.  Mahara on the other hand, acutally checks whether the upgrade statement has worked or not, by throwing exceptions, and where supported (eg, on Postgres) using a transaction.

When upgrading big Moodle sites, we have in some cases, tried the upgrade mutliple times, on a test site based on production, and tweaked it until it worked.  Then ran the upgrade on the production server, in some cases, again multiple times, until it worked.  Each time we were obliged to backup and restore.

Hope that helps.

26 October 2009, 11:03

Hi Jai,

I suspect the reason you have had trouble with Mahara and not other OSS software when failing mid-installation is that you are lucky, or that the software has not detected the problem. When doing a series of independent database structure changes, it is unavoidable that the software can't cope with this failing mid upgrade...

You can see in the mysql manual, this can't be supported with mysql transactions.

26 October 2009, 11:14

Actually Dan makes a good point.

Mahara is very careful about detecting broken upgrades and complaining, while much other software might tell you it's successfully upgraded, where in reality your database is only half upgraded.

26 October 2009, 11:39

Dan, Penny thanks for the insight. My viewpoint was only based on my observation. Let us see what other administrators using MySQL have to say.

 PS: At present we are using Mahara with PostgreSQL and have no problem so far.

27 October 2009, 5:52

MySQL was the first database system I started with (it was 3.something or 4.0 at a Slackware server, oh what a wonderful days at the uni...) - just because it seemed just everybody from PHP world used it. I switched to Postgre SQL 7.4 one day and never looked back.

I personaly blame MySQL for one thing - it teaches you bad habbits. Nigel already listed some of them - not using foreign keys (btw Moodle already does that Nigel, at least at the XMLDB level) and not using transactions. And I will never forget MySQL the fact that you can write a query allowing items in the SELECT list which are not in the GROUP BY list; as the MySQL documentation cautions, the result is undefined. WTF! Database returning undefined results without any warning.That just smells.

I think Dan made a very good point and Penny already explained. The fact that Moodle does not complain does not mean it does not have problems.

27 November 2009, 11:25

I have signed up on this site for the sole purpose of registering dissent in this very thread, and not because I disagree with quite reasonable assertion that Postgres is technically superior.  There is a fundamental problem that you need to address before you will see significant take up for Postgres.

Wednesday was my third attempt to get Postgres running for a web application for both Mahara and another application.  Both times I wasted around 4 hours trying to get the application to connect to Postgres.  I thought it was about time to try again.

About 3 hours into the Postgres configuration, I started channelling Malcolm Tucker.  I'm fine now, because after 4 hours, I stopped working on Postgres and configured MySQL.  It took 32 minutes, including 3 unrelated phone calls, a rant to a collegue and googling for "How to reset the Mysql root password".  Lemon-squeezy, and Mahara 1.2 looks great.  Same intuitive interface I'd come to expect with Moodle and a couple of hours later, I've got two views ready to submit for my postgrad training modules.

On a CentOS 5 box, I'd managed install postgres from the repository and login to a psql> prompt.  I'd been trying all sorts of possiblities in the pg_hba.conf file and then remembering to reload it after every change, but I constantly faced the Mahara installation telling me that it couldn't connect to the database with these credentials.  STFW was not as fruitful as usual because I think people developing on postgres have forgotten what it took to get set up in the first place.  It's like you're inviting me to this really happening party, but the door is locked and I can't get in.

If it helps, I can offer a virtual host so someone knowledgable can do a clean install and together we can write up some good instructions.

/rant

28 November 2009, 12:20

Maybe this little document I've just writting could help you

Installing and configuring Postgresql on CentOS 5 for Mahara

Have a look at it and tell me how it goes.

Saludos,
Iñaki.

04 December 2009, 14:13

Hola Iñaki,

The instructions are good, nice and clear.  I cut and pasted them and they work as expected (but I'd also gotten this far on my previous attempts)  Then I tried to change the  config.php file to

$cfg->dbtype   = 'postgres8';

with the correct user,password and db and I get this 

The error received was:
postgres7 error: [-1: Database connection failed] in CONNECT(localhost, 'mahara', '****', portfolio)

 

That "postgres7 error" might be a clue that CentOS systems haven't got everything up to version 8.  Last time, I suspected that it was something to do with the  pg_hba.conf and tried changing the entry to get it to work (no luck).

It's the end of the day, so I'll get back to this next week, but I'll leave you with the versions that I can find right now.

yum info postgres* reports that everything is version 8.1.18 (except odbc and jdbc which are other versions of 8

 

Gracias 

04 December 2009, 16:35

It looks like your CentOS system is running SELinux in enforcing mode. In this mode, apache is prevented from connecting to postgresql socket.

I'm not a SELinux expert, but this Red Hat bug report https://bugzilla.redhat.com/show_bug.cgi?id=186364 seems to have useful information.

Saludos,
Iñaki.