Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-67886

admin/cli/check_database_schema.php does not check indexes

    XMLWordPrintable

Details

    • MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • MOODLE_37_STABLE, MOODLE_38_STABLE
    • MDL-67886_master
    • Hide
      Test 1
      1. Create a fresh Moodle site.
      2. Run php admin/cli/check_database_schema.php
      3. Confirm it returns "Database structure is ok".
      4. Visit the database using your favourite DB editor.
      5. Edit the "mdl_messages" table and delete the indexes "conversationid, timecreated" and "conversationid".
      6. Run php admin/cli/check_database_schema.php
      7. Confirm you get the following -

        -------------------------------------------------------------------------------
        messages
        * Missing index 'conversationid' (not unique (conversationid)).
        CREATE INDEX mdl_mess_con_ix ON mdl_messages (conversationid);
         * Missing index 'conversationid_timecreated' (not unique (conversationid, timecreated)).
        CREATE INDEX mdl_mess_contim_ix ON mdl_messages (conversationid, timecreated);
        -------------------------------------------------------------------------------
        

      Test 2
      1. Create a fresh Moodle site.
      2. Visit the database using your favourite DB editor.
      3. Edit the "mdl_messages" table and delete the indexes "conversationid, timecreated" and "conversationid".
      4. Create another database called anything you want.
      5. Visit 'Site administration' > 'Development' > 'Experimental' > 'Database migration'.
      6. Enter in the details for the new database you created.
      7. Click 'Transfer data'.
      8. Confirm it transferred correctly.
      Test 3 (master only)
      1. Create a fresh Moodle site.
      2. Visit 'Site administration' > 'Development' > 'XMLDB editor'.
      3. Click on 'Check indexes'.
      4. Click 'Yes'.
      5. Confirm it says "No missing or extra indexes have been found, your DB doesn't need further actions.".
      6. Visit the database using your favourite DB editor.
      7. Edit the "mdl_messages" table and add a new index on the "customdata" field.
      8. Visit 'Site administration' > 'Development' > 'XMLDB editor'.
      9. Click on 'Check indexes'.
      10. Click on 'Yes'.
      11. Confirm it says that you have 1 additional one.
      Test 4 (master only)
      1. Create a fresh Moodle site.
      2. Run php admin/cli/check_database_schema.php
      3. Confirm it returns "Database structure is ok".
      4. Visit the database using your favourite DB editor.
      5. Edit the "mdl_messages" table and add a new index on the "customdata" field.
      6. Run php admin/cli/check_database_schema.php
      7. Confirm you get the following -

        -------------------------------------------------------------------------------
        messages
         * Unexpected index 'mdl_messages_customdata'.
        -------------------------------------------------------------------------------
        

      Show
      Test 1 Create a fresh Moodle site. Run php admin/cli/check_database_schema.php Confirm it returns "Database structure is ok". Visit the database using your favourite DB editor. Edit the "mdl_messages" table and delete the indexes "conversationid, timecreated" and "conversationid". Run php admin/cli/check_database_schema.php Confirm you get the following - ------------------------------------------------------------------------------- messages * Missing index 'conversationid' (not unique (conversationid)). CREATE INDEX mdl_mess_con_ix ON mdl_messages (conversationid); * Missing index 'conversationid_timecreated' (not unique (conversationid, timecreated)). CREATE INDEX mdl_mess_contim_ix ON mdl_messages (conversationid, timecreated); ------------------------------------------------------------------------------- Test 2 Create a fresh Moodle site. Visit the database using your favourite DB editor. Edit the "mdl_messages" table and delete the indexes "conversationid, timecreated" and "conversationid". Create another database called anything you want. Visit 'Site administration' > 'Development' > 'Experimental' > 'Database migration'. Enter in the details for the new database you created. Click 'Transfer data'. Confirm it transferred correctly. Test 3 (master only) Create a fresh Moodle site. Visit 'Site administration' > 'Development' > 'XMLDB editor'. Click on 'Check indexes'. Click 'Yes'. Confirm it says "No missing or extra indexes have been found, your DB doesn't need further actions.". Visit the database using your favourite DB editor. Edit the "mdl_messages" table and add a new index on the "customdata" field. Visit 'Site administration' > 'Development' > 'XMLDB editor'. Click on 'Check indexes'. Click on 'Yes'. Confirm it says that you have 1 additional one. Test 4 (master only) Create a fresh Moodle site. Run php admin/cli/check_database_schema.php Confirm it returns "Database structure is ok". Visit the database using your favourite DB editor. Edit the "mdl_messages" table and add a new index on the "customdata" field. Run php admin/cli/check_database_schema.php Confirm you get the following - ------------------------------------------------------------------------------- messages * Unexpected index 'mdl_messages_customdata'. -------------------------------------------------------------------------------

    Description

      When installing a fresh moodle, the database is created from the install.xml file. In this file indexes are created implicitly from the key names, for example https://github.com/moodle/moodle/blob/MOODLE_37_STABLE/lib/db/install.xml#L643  this `mdl_message_conversation_members` table is created with 2 indexes, mdl_messconvmemb_con_ix and mdl_messconvmemb_use_ix that are based on the key names.

      If one of these indexes is dropped, the index check at admin/tool/xmldb/index.php will tell us to re-add it, but the output from check_database_schema.php will not change or notify us.

      I think this is because the check_database_schema script is not checking for indexes created from key names. The check indexes xmldb code does make this check for all indexes: https://github.com/moodle/moodle/blob/master/admin/tool/xmldb/actions/check_indexes/check_indexes.class.php#L56

      Shouldn't a schema checking script check these indexes as well?

      In a fresh moodle 3.7, I can run `drop index mdl_messconvmemb_use_ix;` and get the screenshot below. But the ouput from check_database_schema is

      root@f5bb632af830:/siteroot# php admin/cli/check_database_schema.php 
      Database structure is ok.

      Attachments

        Issue Links

          Activity

            People

              markn Mark Nelson
              kristianr Kristian Ringer
              Brendan Heywood Brendan Heywood
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Janelle Barcega Janelle Barcega
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/May/20

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 4 hours, 16 minutes
                  4h 16m