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

admin/cli/check_database_schema.php does not check indexes

XMLWordPrintable

    • 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'. -------------------------------------------------------------------------------

      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.

        1. Screenshot_1.png
          Screenshot_1.png
          127 kB
        2. Screenshot_2.png
          Screenshot_2.png
          118 kB
        3. Screenshot_2020-02-06 dev Administration Development XMLDB editor.png
          Screenshot_2020-02-06 dev Administration Development XMLDB editor.png
          87 kB

            markn Mark Nelson
            kristianr Kristian Ringer
            Brendan Heywood Brendan Heywood
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Janelle Barcega Janelle Barcega
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved:

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

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.