Moodle

Review all the indexes, creating the missing ones...

Details

  • Type: Task Task
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.8
  • Fix Version/s: 1.8.3, 1.9
  • Component/s: Database SQL/XMLDB
  • Labels:
    None
  • Environment:
    Any
  • Database:
    MySQL, PostgreSQL
  • Affected Branches:
    MOODLE_18_STABLE
  • Fixed Branches:
    MOODLE_18_STABLE, MOODLE_19_STABLE

Description

With the introduction of XMLDB a lot of "foreign key" obvious missing indexes have been added to Moodle DB Schema.

So, new sites created under 1.7 will have some more indexes than old sites migrated from previous versions (non XMLDB stuff).

At some moment in HEAD we must add some utility to look for indexes, compare them with the needed by the XMLDB schema and build them as necessary.

This can be achieved by the upgrade script or by some special script somewhere (Health Center) in the Admin Interface.

The script should iterate over all the Moodle XMLDB structures, getting their indexes and looking in DB if they are present (not matter of their name), creating them if necessary.

Only MySQL and PostgreSQL will need this.

Issue Links

This issue has a non-specific relationship to:
MDL-6855 Compare one 1.7 clean installation DB with one 1.7 installation migrated from previous version Minor Reopened

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

This was addressed in MDL-6855 and the list of new indexes has been copied from there.

Show
Eloy Lafuente (stronk7) added a comment - This was addressed in MDL-6855 and the list of new indexes has been copied from there.
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

List of new indexes added. It's only one reference. The script must search for differences itself.

Show
Eloy Lafuente (stronk7) added a comment - List of new indexes added. It's only one reference. The script must search for differences itself.
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

One discussion where the issue is commented:

http://moodle.org/mod/forum/discuss.php?d=57028

Should go to 1.8.1....uhm...

Show
Eloy Lafuente (stronk7) added a comment - One discussion where the issue is commented: http://moodle.org/mod/forum/discuss.php?d=57028 Should go to 1.8.1....uhm...
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Done. There is a new option "check indexes" in the XMLDB Editor main page that compares all the XMLDB definitions (install.xml files) with real DB objects. It produces one report with the missing indexes found and with the neeeded SQL statements to execute in order to keep the Moodle server updated.

Tested against MySQL, PostgreSQL, MSSQL and Oracle (test servers). Please, test it under your servers and feedback results.

Show
Eloy Lafuente (stronk7) added a comment - Done. There is a new option "check indexes" in the XMLDB Editor main page that compares all the XMLDB definitions (install.xml files) with real DB objects. It produces one report with the missing indexes found and with the neeeded SQL statements to execute in order to keep the Moodle server updated. Tested against MySQL, PostgreSQL, MSSQL and Oracle (test servers). Please, test it under your servers and feedback results.
Hide
Permalink
Martin Dougiamas added a comment -

I've done it on moodle.org ... worked great (and found about 30 missing indexes!)

Show
Martin Dougiamas added a comment - I've done it on moodle.org ... worked great (and found about 30 missing indexes!)
Hide
Permalink
Martin Dougiamas added a comment -

I had to insert all the semicolons after each command though ... I've just patched this slightly so that the many statements can more easily be copied and pasted straight into a SQL window

Show
Martin Dougiamas added a comment - I had to insert all the semicolons after each command though ... I've just patched this slightly so that the many statements can more easily be copied and pasted straight into a SQL window
Hide
Permalink
Martin Dougiamas added a comment -

Would it be a bad idea to add a "Add these now" button at the bottom so that Moodle can add the indexes itself?

Show
Martin Dougiamas added a comment - Would it be a bad idea to add a "Add these now" button at the bottom so that Moodle can add the indexes itself?
Hide
Permalink
Eloy Lafuente (stronk7) added a comment -

Not at all, it's a good idea but, for big sites, if a lot of indexes are missing, it can be a problematic option because of timeouts, potential errors in the middle of the process, and so on.

With latest modifications it's really easy to copy & paste all those SQL code all together , for any RDBMS, in the user preferred interface.

So I would vote -1 for this, keeping things as they are now. Anyway, you decide, of course.

Show
Eloy Lafuente (stronk7) added a comment - Not at all, it's a good idea but, for big sites, if a lot of indexes are missing, it can be a problematic option because of timeouts, potential errors in the middle of the process, and so on. With latest modifications it's really easy to copy & paste all those SQL code all together , for any RDBMS, in the user preferred interface. So I would vote -1 for this, keeping things as they are now. Anyway, you decide, of course.

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.5#665-sha1:422aead) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.