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

{mdl}_user table needs an index on the username column

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.9.5
    • Fix Version/s: 2.0
    • Component/s: Authentication
    • Labels:
      None
    • Environment:
      Gentoo
      Apache
      MySQL
    • Database:
      MySQL
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      I am a newbie to Moodle and hope informing this issue in the correct location. Here is the case:

      Our Active Directory (AD) contains around 27,000 users and as I run the auth/ldap/auth_ldap_sync_users.php from the command line, the script inserts all the users in the AD into

      {mdl}_user table initially. However, in the subsequent executions the script prints "No updates to be done" and stalls. Besides the MySQL server hangs and the web site becomes unresponsive. When examining the codes I realized that the {mdl}

      _user table is left joined with the

      {mdl}_extuser table with the username column and there is no index for username in the {mdl}

      _user table. I created that index and the ldap sync operation started to work seamlessly. I think an index should be added to the username column.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              chuang Wen Hao Chuang added a comment -

              I just checked with our 1.9.5 installation, and there is a "username" index created (by default) for mdl_user already? Did I miss something here?

              Show
              chuang Wen Hao Chuang added a comment - I just checked with our 1.9.5 installation, and there is a "username" index created (by default) for mdl_user already? Did I miss something here?
              Hide
              iarenaza Iñaki Arenaza added a comment -

              Mr Wen,

              I've had a look at lib/db/install.xml and there is no index defined for "username". In addition to it, I've just installed a fresh stock install of 1.9.5+ (current as of yesterday) on a MySQL database, and I get no index on mdl_user.username at all.

              So I wonder where yours came from

              Anyway, the patch proposed in MDL-13458 should fix this issue.

              Saludos,

              Show
              iarenaza Iñaki Arenaza added a comment - Mr Wen, I've had a look at lib/db/install.xml and there is no index defined for "username". In addition to it, I've just installed a fresh stock install of 1.9.5+ (current as of yesterday) on a MySQL database, and I get no index on mdl_user.username at all. So I wonder where yours came from Anyway, the patch proposed in MDL-13458 should fix this issue. Saludos,
              Hide
              skodak Petr Skoda added a comment -

              Hello, there is a unique index on mnethostid+username which was introduced during the mnet introduction.

              There were some places with missing mnethostid, those should be all fixed now, if not please file a seprate issue.

              Thanks for the report.

              Petr Škoda

              Show
              skodak Petr Skoda added a comment - Hello, there is a unique index on mnethostid+username which was introduced during the mnet introduction. There were some places with missing mnethostid, those should be all fixed now, if not please file a seprate issue. Thanks for the report. Petr Škoda

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    24/Nov/10