Ideas….

If you’re not interested in Moodle or troubleshooting MySQL problems, than you probably don’t want to read this post.  I however wanted to write some notes for myself and need it to be portable.  Since that’s technically the purpose of this blog, I figured I should put it here.

Recently Nagios started informing me that HTTPS was going down infrequently (but started to become more frequent).

***** Nagios  *****
Notification Type: PROBLEM
Service: HTTPS
Host: Moodle Server
Address: 123.456.78.912
State: CRITICAL
Date/Time: Fri Aug 28 01:06:50 EDT 2009
Additional Info:
CRITICAL – Socket timeout after 10 seconds

What was strange was that HTTP wasn’t going down.  I felt like Nagios was lying to me so I figured I would have to wake up late at night (early in the morning?) and catch it lying to me.  To do this I decided I’d try to figure out if my cellphone had an email to associate to text messaging.  Mine wasn’t working so I had to talk to Sprint about it.

In the mean time though, it was still going down.  Luckily last night at 4am I randomly woke up. I ran downstairs (imagine kid at Christmas…but like a reverse Christmas) and fired up the laptop.  Sure enough when I went to the Moodle homepage I saw this:

Error: Database connection failed.

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php

Now in hindsight, there were so many things I could have done at this point.  But it was 4am and my house was freezing so I made some mistakes.  The first thing I did was restart Apache.  It appeared to be running and the restart didn’t throw any errors, but it didn’t fix the problem either.  Next I “turned off SSL”.  I was able to get to the error page without the HTTPS so I determined at this point that Nagios clearly was lying to me, something else was wrong (p.s. i am thankful to Nagios for telling me that something was wrong).  So then I decided that maybe it was a database problem.  I went into MySQL and was able to access the Moodle database and do a “show tables;”.  So I thought at that point I should restart MySQL, in hindsight there are so many other things I should have done first, but I will document those below.  A restart of MySQL fixed the issue.

So what will I do next time (and yes…there will be a next time):

1) run top on the server: See if MySQL is hogging the CPU.  This could lead you to a number of options.

2) check the mysql-slow-queries.log: I set up the mysql option log-slow-queries.  this in conjunction with the long-query-time option will log any query that takes over a certain amount of time (default is 10s).

3) show mysql processlist: If you haven’t set up the log slow queries option this might also be helpful in determining what mysql is doing.  Here is a brief explaination of show processlist and its companion of kill <processid>.  I also found this handy script that you could set up as part of a cron to kill any runaway processes.

4) query is poorly written: I’ve found a number of reports that MySQL queries are poorly written in Moodle and that these can cause the CPU usage to top out at %100 which causes Moodle to be unusable.  Below are a few Moodle Tracker reports of this:

• http://tracker.moodle.org/browse/MDL-14911
• http://tracker.moodle.org/browse/MDL-19584
• http://tracker.moodle.org/browse/MDL-17051
• http://tracker.moodle.org/browse/MDL-10848

To see if this is the case with your Moodle use the slow query logging (or just the plain old query logging) with a backup of your database to see if you can replicate the problem somewhere else.  If you can the problem is probably the query and maybe you can figure out a way to rewrite the query (or get it rewritten) to see if that fixes the problem.

5) check your persistent db connection: Check to see if you have persistent database connections set up in the Moodle config.php file and in the php.ini file.  If you do, turn it off and see if this solves the problem.  it may be that your connections aren’t closing and they are killing your database regularly.

6) check your max connections: Are the maximum number of Apache connections greater than your maximum number of MySQL connections?  If so that might be a problem.  Basically users could get to the website but then when they go to request something in the database they can’t because the # of connections are taken up.  I have a feeling though that this may be a completely different error than what I was experiencing (people…including myself…really need to take screenshots or document errors better).

7) check your MySQL performance: Perhaps you haven’t configured Moodle right.  Take a look at these performance tuning tips to see if you can get it working better.

8) check your database tables: Stop mysqld and run a check on your Moodle tables.  See if some of the tables are corrupted, if they are fix them.

So if you’re wondering what I’m doing at 4am tomorrow, there’s the list.  Wish me luck.

Currently we are using the LMB Plugin for Moodle to enroll students into our courses.  This works swimmingly, until you have to enroll a large group into a class that really isn’t a class.  Let me explain…no let me sum up.

We have a bunch of placement tests and advising courses and that sort of thing we want our first year students to be enrolled in.  Moodle happens to have a flat file enrollment feature.  Below are the instructions from that enrollment feature:

This method will repeatedly check for and process a specially-formatted text file in the location that you specify. The file is a comma separated file assumed to have four or six fields per line:

*  operation, role, idnumber(user), idnumber(course) [, starttime, endtime]
where:
*  operation        = add | del
*  role             = student | teacher | teacheredit
*  idnumber(user)   = idnumber in the user table NB not id
*  idnumber(course) = idnumber in the course table NB not id
*  starttime        = start time (in seconds since epoch) - optional
*  endtime          = end time (in seconds since epoch) - optional

It could look something like this:

   add, student, 5, CF101
   add, teacher, 6, CF101
   add, teacheredit, 7, CF101
   del, student, 8, CF101
   del, student, 17, CF101
   add, student, 21, CF101, 1091115000, 1091215000

So great, I need to pull a randomly generated number rather than a username in order to enroll students.  So now I need to figure out a way to take a list of usernames in banner and get their corresponding idnumbers in Moodle.

Now if this were a one time thing I would take the lazy man’s way out and I would create a table in the database and do a simple

select table.username, user.idnumber
from table, user
where table.username = user.username;

But I’m going to have to do this every year until we either get rid of Moodle or I leave Wheaton.  Since plans are not in the work for either, I’ll actually do something more substantive.

First I made the decision to use the MySQL select in clause.  For those of you who don’t know, you can do:

select username, idnumber
from user
where username in ('value1', 'value2', 'valueN');

Its pretty useful really.  I can’t really find a MySQL page that talks about the in() clause but I did find a good post in the MySQL lists.  The next thing I had to figure out was how to push a file into a php array.  Again I found a great post on using the file() command to do this.  The only drawback is that when i go to print it’ll just show:

value1 value2 valueN

When that’s not what I want.  I need quotes and commas and crap around it.  So I found another great post that talks about using the implode function to put a php array into an in() clause.  The implode function will allow me to put quotes and what not around each one of the values in the array so it will look like this:

'value1', 'value2', 'valueN'

So now that I have the component parts, all I need to do is create a php page that will let me upload a file, run the query, and retrieve the outfile.  Of course the idea of writing this code makes me tired and since its 5pm I shall just publish this post and write the code tomorrow (hopefully).

UPDATE

I was wrong.  The value is not a randomly generated number, well it is, but its randomly generated by Banner.  In the Banner XML the value is the <sourcedid> for a <person>.

Luckily this randomly generated value is something that we can pull out of Banner using our reporting system.  The value (in our Banner) is gobsrid_sourced_id.  Not sure if it will be the same in your Banner, but hopefully this will put someone on the right track.

My next step is to document how to do all of this in our Moodle Documentation Wiki.  I might do a Jing Video.

I’ve been scouring academia for cloud computing projects and came across one that I myself am looking to get involved in (and not because its cloud computing).

NITLE hosts a Moodle Exchange (NME).

For faculty, technologists, and librarians at participating campuses that use or are interested in using Moodle. Participants in this collaborative user community focus on the use of Moodle at smaller, independent colleges and universities, sharing ideas and best practices and helping each other troubleshoot technical or use issues specific to liberal education and undergraduate colleges and universities. In the NME, Moodle-using or Moodle-interested campuses share strategies and ask and answer questions about implementation, evaluation, migration, customization, and other Moodle-related topics.

NME also works together to develop a Liberal Arts Edition of Moodle as well as plugins specifically focused on the needs of Liberal Arts Colleges.  So how does this get cloudy?  Well they use Google Code to distribute their developments.  This allows everyone involved in the project to access or share code that has been developed for the community.

If you’re doing something similar let me know about it.  I’m very interested in how different academic institutions are using the cloud to develop systems and applications.