Moodle
  1. Moodle
  2. MDL-19779

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

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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
    • Rank:
      5633

      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.

        Issue Links

          Activity

          Hide
          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
          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
          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
          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
          Petr Škoda 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
          Petr Škoda 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: