Working with dates before epoch

If you use a custom user profile field with a date and want to store a date that falls before epoch, 01/01/1970 (e.g. a date of birth), the data will be stored as a negative number
e.g. 20/11/1959 is stored as -319284000 which means 319, 284, 000 seconds BEFORE 01/01/1970.

With MySQL you can convert these with the following SQL which works for both scenarios (before and after epoch, 01/01/1970):

select date_add(from_unixtime(0), interval data second) from mdl_user_info_field

Where in the above “data” is the column that returns the stored user profile field value e.g. date of birth from the mdl_user_info_field record.

If you just want the date (no timestamp):

select date(date_add(from_unixtime(0), interval data second)) from mdl_user_info_field

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).

select 
    f.contextid,
    x.instanceid, 
    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
from
    mdl_files f inner join mdl_context x
    on f.contextid = x.id
    and x.contextlevel = 50
    inner join mdl_course c
    on c.id = x.instanceid
group by 
    f.contextid, x.instanceid
order by 
    sum(filesize) desc
;

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

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 bi.id = bp.blockinstanceid
inner join mdl_context x
on x.id = 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.

MySQL query to get a list of Site Admin users

The following SQL will help you see which users are site admins in a MySQL (or MariaDB) database using the find_in_set function to query the values listed in the mdl_config siteadmins key-value.

select
    u.* 
from 
    mdl_user u
where 
    find_in_set(u.id, (select value from mdl_config where name = 'siteadmins'))

Note you need to use find_in_set and NOT in as it doesn’t handle the commas returned to separate each siteadmin’s user ID.