MySQL compare strings by case

Here’s one method of comparing strings by case in MySQL. You can use the md5 function to hash the string and compare to its lower case value.

This example is for checking for usernames that are not lower case in moodle.

select username, md5(username), md5(lower(username))
from mdl_user
where deleted = 0 and suspended = 0
and md5(username) != md5(lower(username));

If the md5 hash when the string is lower cased is different to the current md5 hash you will get a result.

Moodle Unexpected Output while processing AJAX request

I had this one come up when expanding the Site Administration menu:

Coding error detected, it must be fixed by a programmer:  
Unexpected output whilst processing AJAX request.  

This could be caused by trailing whitespace. Output received: ' '

What causes it is there is a new function in later versions of Moodle in lib/ajax/ajaxlib.php called “ajax_check_captured_output”. This is a useful check for developers, and therefore when the site is in DEVELOPER debug mode it throws an exception. You can change the debug mode to something less than DEVELOPER and you shouldn’t get an exception (but note that there is still an underlying problem).

One catch is that if the site administration navigation is failing, you can’t actually get to Site administration > Development > Debugging. Easy work around, visit and adjust your debugging settings there.

MNET Public Key for your Moodle Site

All MNET host keys are stored in the table:

The id of the local MNET host key (your moodle) is stored in the config table in the mnet_localhost_id value.

So you can use the following to get the public key and MNET information about your local moodle:

select * from mdl_mnet_host where id = (
    select value from mdl_config 
    where name = 'mnet_localhost_id'

Moodle Git and Finding Versions

The key to working with the Moodle git repository is the version.php file. This file is changed on every major commit, so you can use it to track down a specific moodle version in the repository (use this in conjunction with branches and version tags).

To see what’s changed on this file including the file changes themselves use:

git whatchanged -p version.php

This is handy, but chances are you are searching for a specific version. Eg. say I want version 2012062503.02. How do I track down the commit(s) that relate to that version? Use the command above but add the -S search parameter:

git whatchanged -p -S2012062503.02 version.php

This gives you the commit details (including the hash) you need. If you get multiple results, you probably want the latest commit.

Once you have the correct commit, use git checkout with the branch option to make/set your branch. E.g.

git checkout 5f1d8f2 -B moodle

Now my moodle branch points to the commit I was looking for.

Moodle XMLDB constants

Moodle uses XMLDB¬†as an abstraction layer to support multiple database platforms and schema changes. You’ll need to know how this works to work with the database. In particular you’ll need to know the relevant constants.

They are all defined under and prefixed with XMLDB

For example, you can get the database field types like so:

cat lib/xmldb/xmldb_constants.php | grep XMLDB_TYPE

If you need some examples of the syntax, check out the core moodle install and upgrade files under lib/db as well.

Moodle login page instructions

The instructions that appear on the Moodle login page are stored in the Moodle configuration property auth_instructions. You can edit them from Site Administration > Plugins > Authentication > Manage Authentication (instructions).

In the database, look for auth_instructions in the mdl_config table (e.g select * from mdl_config where name = 'auth_instructions') and change the value (which can be HTML).

You may need to purge your Moodle cache to see changes.

Moodle Front Page Course

In case you didn’t know, the moodle front page is actually a course and usually course id 1. So you can update things like the site full name, shortname, and summary by updating this course (id=1) in the mdl_course table in the database.

Moodle force password change

If you need to know where the force password change is set for a given user in the Moodle database, look in the mdl_user_preferences table for the value auth_forcepasswordchange. A value of 0 means a password change is not required while a 1 means the user will be forced to change their password.

This might be useful for example if you want to do a bulk force password change on a number of users.

Moodle Debug Numeric Values

The following is a list of the numeric values to set the value of the debug field (either through $CFG->debug in config.php or the debug value in the in the mdl_config table). Note this is for Moodle 2.x.

  • 0 = NONE: Do not show any errors or warnings
  • 5 = MINIMAL: Show only fatal errors
  • 15 = NORMAL: Show errors, warnings and notices
  • 6143 = ALL: Show all reasonable PHP debug messages
  • 38911 = DEVELOPER: extra debug messages for developers

To change in the database, run the following SQL statement:

update mdl_config set value = {appropriate value} where name = 'debug';