Can’t create a new XMLDB file

The Moodle XMLDB plugin does some checks of the db/ folder in your plugin to ensure it has read/write access before allowing certain permissions (e.g. create and load). If it doesn’t find this, the text will appear but it won’t be a hyperlink.

Have a look in admin/tool/xmldb/actions/main_view/main_view.class.php

For example for the create button it will have:

 // The create button
                if ($dbdir->path_exists &&
                    !file_exists($key . '/install.xml') &&
                    is_writeable($key)) {                                       

So the db directory needs to exist and be writable by the user running the Moodle instance (e.g. if using apache the user that apache runs as).

Scheduled Tasks: A lock was created but not released

Schedule tasks can use two types of locks:
* Database locks — lock_db table
* File locks — dirroot/lock

The follow error can occur when running a cron through the CLI for example:

!!! Coding error detected, it must be fixed by a programmer: A lock was created but not released at:
[dirroot]/lib/classes/task/manager.php on line 483

 Code should look like:

 $factory = \core\lock\lock_config::get_lock_factory('type');
 $lock = $factory->get_lock(Resource id #585);
 $lock->release();  // Locks must ALWAYS be released like this.

As a starting point, trying disabling the schedule task causing the error and see if you can get the cron to run successfully.

You can try clearing out the locks as well from the database/filestore (whichever is relevant) and running the cron again.

Replacing strings in the database through the CLI

There is a tool provided with Moodle to replace strings in your database with another value. This is very useful for things such as site address or domain name changes, and changes from non-SSL (http) to SSL (https).

The tool can be accessed via the following relative URL as a site administrator.

While you can certainly run it from the browser / site, it can timeout and cause problems to do so. Therefore the CLI version is a far better option and available in the moodle code tree under:


php admin/tool/replace/cli/replace.php

Search and replace text throughout the whole database.

--search=STRING       String to search for.
--replace=STRING      String to replace with.
--shorten             Shorten result if necessary.
--non-interactive     Perform the replacement without confirming.
-h, --help            Print out this help.

Note you might need sudo above. So lets say you change your site form to you could run it like so:

php admin/tool/replace/cli/replace.php 
--search= --replace=  --non-interactive

This will scan through tables and give you results in the console as it is going. The non-interactive flag prevents prompting (assuming you are comfortable with the change). If you aren’t you probably shouldn’t use the tool or at least use it on a test site first. The shorten result flag shouldn’t be necessary in most cases.

Resetting a user’s password through the CLI

If you have access to the Moodle server, you can use the admin/cli/reset_password.php script to reset passwords for users with the manual authentication method.

To run this script (from the moodle root code directory), note you may need to sudo as a user with write privileges to the $CFG->dataroot directory.

php admin/cli/reset_password.php

== Password reset ==
Enter username (manual authentication only)
: moodletips
Enter new password
: moodletips

Note the password you specified has to match the password rules configured in Moodle for example the above password moodletips by default would throw:

Passwords must have at least 1 digit(s).
Passwords must have at least 1 upper case letter(s).
Passwords must have at least 1 non-alphanumeric character(s) such as as *, -, or #.

Very handy if you are unable to get into the Moodle site for any reason, or need a quick way to reset a user’s password.

Course file system space usage query

The following query provides a summary of the file space usage (in bytes) and number of files used by each course in the system from the mdl_files meta table for the data directory. This is just for the space used in the data directory (not the database).

    c.fullname as course_full_name,
    c.shortname as course_short_name,
    sum(f.filesize) as size_in_bytes,
    sum(case when (f.filesize > 0) then 1 else 0 end) as number_of_files
    mdl_files f inner join mdl_context x
    on f.contextid =
    and x.contextlevel = 50
    inner join mdl_course c
    on = x.instanceid
group by 
    f.contextid, x.instanceid
order by 
    sum(filesize) desc

It may need some refinement. Context level 50 refers to courses.

Overriding PHP Settings

There are a few ways to override global PHP (php.ini) settings with local settings. For example you can use a .htaccess file or local php.ini file depending on what options exist for your site hosting.

Another way to do it, is to override the settings in config.php. This has the benefit of being part of the standard moodle code configuration setup. All you need to do is use the ini_set() function. For example, to increase the max_execution_time from 30s to 60s you can do this in config.php:

ini_set('max_execution_time', 60);

The best place for a change like this is towards the end of the config.php, and perhaps wrapped in comments indicating custom configuration.

Getting PHP Info for your Moodle Site

There is a handy link under Site adminstration > Server > PHP Info ( or browse to ) that will return the standard PHP Info phpinfo() results for your server.

Very handy for checking things like installed modules and core parameters if you are overriding them ( e.g. error_log, max_execution_time, post_max_size, upload_max_size etc )

Query blocks instances and positions through the database

The following query gives you an idea of all the moodle blocks that have been added to various pages on your site and where they are positioned:

select bi.*, bp.*, x.*
from mdl_block_instances bi inner join mdl_block_positions bp
on = bp.blockinstanceid
inner join mdl_context x
on = bp.contextid
order by bi.blockname;

Very handy if you have a block that is causing you problems e.g. not fully uninstalled or visible to users.

The pagetypepattern e.g. *, site_index, my-index, course-view-* tells you which page types the block will be shown on, while the pagetype gives you the specific instances where it appears.

The context information at the end can also tell you the contextlevel (e.g. 50 = course, 70 = module) and the instance of that context (e.g. x.instanceid = 2 means course id 2 if the contextlevel is 50 (course context).

Verify your Moodle Database Schema against XMLDB

There is a very handy CLI tool admin/cli/check_database_schema.php which compares the structure of your Moodle database against the XMLDB metadata and looks for any issues.

These might includes issues such as:

  • Missing tables – they exist in XMLDB but not in your DB (not good!)
  • Unexpected tables – tables in your database not defined in XMLDB (e.g. created outside of Moodle)
  • Mismatches between table column definitions in the database and XMLDB

Some things may be acceptable to ignore, but it pays to do a check, particularly after an upgrade to make sure the upgrade process worked correctly and in particular you aren’t missing any core or plugin database tables that are defined in XMLDB, as these will pop up as database errors in your application sooner or later (e.g. “Error reading from database”).

If you need to build any missing core tables, remember they are defined under Developer > XMLDB Editor. From here find lib/db (core database tables) and choose Load, followed by Edit. Find the table you need and choose Edit. You can then use View SQL code to get the relevant SQL commands to create the table if it is missing from your database.

Also as a developer, make sure you are correctly utilising XMLDB for defining your plugin’s database tables. Taking shortcuts such as directly creating tables in the database without defining them in XMLDB will mean that they will pop up as unexpected when checking the database schema.

CLI Maintenance Mode

Newer versions of Moodle now provide the ability to put a site into maintenance mode through the CLI:

This can be found under the standard admin CLI location

php admin/cli/maintenance.php

Options include

php admin/cli/maintenance.php --enable
php admin/cli/maintenance.php --disable

NOTE this disables all web access to the site and can only be restored by disabling maintenance mode through the CLI again. This is exactly what you want if you need to take a proper site backup. The message displayed will be the maintenance mode message set up under Site Administration > Server > Maintenance Mode > Optional maintenance message (maintenance_message).

There is also a cool feature to enable it later e.g.

php admin/cli/maintenance.php --enablelater=60

Which would enable it after 60 minutes. This will tell exactly when maintenance mode will be enabled too. Good for making sure the site is in maintenance mode when you are ready to start an upgrade for example.

Finally there is an option called “enableold”

php admin/cli/maintenance.php --enableold

This is meant to do the same thing as the user interface Site Administration > Server > Maintenance Mode > Maintenance mode setting. That is site admins still have access to the site but the site is otherwise in maintenance mode for everyone else. This works in Moodle 3.2 but I’ve had problems with it in prior versions of moodle. It could possibly be around caching and might be important to also clear the moodle cache at the same time.

Table formats from Antelope to Barracuda

On your environments page, as of around Moodle 2.6 you might see a message like this:

unsupported_db_table_row_format if this test fails, it indicates a potential problem

Your database has tables using Antelope as the file format. You are recommended to convert the tables to the Barracuda file format. See the documentation Administration via command line for details of a tool for converting InnoDB tables to Barracuda.

If you are running MySQL/MariaDB, this message is advising you should move to the better InnoDB Barracuda format.

To perform the table upgrades, you use the Moodle cli (i.e. admin/cli) and run the following commands (prefix with sudo if relevant):

php admin/cli/mysql_compressed_rows.php -l

This lists tables that need compacting/compressing e.g.

mdl_data                        Compact     (needs fixing)
mdl_data_fields                 Compact     (needs fixing)
mdl_enrol_paypal                Compact     (needs fixing)
mdl_lti                         Compact     (needs fixing)
mdl_user                        Compact     (needs fixing)
mdl_user_info_field             Compact     (needs fixing)

To fix, run the following. Note this can be database intensive so avoid running during high load periods or when there are a lot of active users on the site.

php admin/cli/mysql_compressed_rows.php -f

mdl_data                         ... Compressed
mdl_data_fields                  ... Compressed
mdl_enrol_paypal                 ... Compressed
mdl_lti                          ... Compressed
mdl_user                         ... Compressed
mdl_user_info_field              ... Compressed

To confirm all is well, run the first info command again or check the admin/environment.php page again.

Moodle User Management URLs

Some handy user management and search URLs, particularly if the site admin block is hidden.

User search

Note a %20=means a blank space, encoded to work in a URL.


Create a new user


Edit a user if you know their moodle internal id


Delete an existing user

This requires a session key (which is a godo thing!) so you need to do this once in the user edit area.


If you don’t see a delete action for a user it usually means they are a site admin so you need to take them out of site admin first.

Assigning site administrators


Assign site admins through the database

The following steps can be used to add your user as a siteadmin in the database if required.

Step 1: Find your moodle user ID

select id from mdl_user where username = '{username}';

Step 2: Get the list of current siteadmin IDs

select value from mdl_config where name = 'siteadmins';

This gives you a list of Moodle user ids in a list that are siteadmins. What you want to do is append your Moodle user ID to the end of the list (assuming it isn’t there already!).

Step 3: Add your moodle user id to the list

You can do this manually, simple add ‘,{your_moodle_userid}’ to the text or you can use a query like this:

select concat(value, ',', (select id from mdl_user where username = '{username}'))
from mdl_config
where name = 'siteadmins';

Step 4: Update the siteadmin list

The following SQL does the deed of updating the list of siteadmins so be very careful. Ues the results from step 3 to perform this update. If anything goes wrong use the results from step 2 to revert the changes.

update mdl_config
set value = '{list_of_siteadmins_with_your_moodle_userid}'
where name = 'siteadmins';