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

admin/cli/check_database_schema.php does not check indexes

    XMLWordPrintable

    Details

    • Testing Instructions:
      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'. -------------------------------------------------------------------------------
    • Affected Branches:
      MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_37_STABLE, MOODLE_38_STABLE
    • Pull 3.7 Branch:
    • Pull 3.8 Branch:
    • Pull Master Branch:
      MDL-67886_master

      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

              Assignee:
              markn Mark Nelson
              Reporter:
              kristianr Kristian Ringer
              Peer reviewer:
              Brendan Heywood
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Janelle Barcega
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                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