Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-10327

Use "ALTER TABLE ... ADD INDEX" instead of "CREATE INDEX"

    Details

    • Type: Improvement
    • Status: Unverified Fix
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.9
    • Fix Version/s: 3.0.10-RC1
    • Component/s: Update system
    • Labels:
      None
    • Environment:
      Several Linux distributions

      Description

      Time and again I have problems with updating the board because on some servers the default DB user doesn't have the permission for the CREATE INDEX Command (don't know why).

      Current statement:
      CREATE INDEX att_ip ON phpbb_login_attempts(attempt_ip, attempt_time)

      Better:
      ALTER TABLE `phpbb_login_attempts` ADD INDEX `att_ip` ( `attempt_ip` , `attempt_time` )

        Issue Links

          Activity

          Hide
          bantu Andreas Fischer added a comment -

          This is a known issue with confixx and not a bug in phpBB per se. If you're using confixx, please complain to the authors of confixx and get your hosting provider to fix MySQL permissions.

          Show
          bantu Andreas Fischer added a comment - This is a known issue with confixx and not a bug in phpBB per se. If you're using confixx, please complain to the authors of confixx and get your hosting provider to fix MySQL permissions.
          Hide
          bantu Andreas Fischer added a comment -

          The first statement probably requires the INDEX permission while the second statement requires the ALTER permission. Since the ALTER permission is a lot more powerful, I'm generally in favour of using the less powerful INDEX.

          We could however try to use INDEX and if it fails ALTER, but that seems a little much for a simple configuration error caused by third party software.

          Show
          bantu Andreas Fischer added a comment - The first statement probably requires the INDEX permission while the second statement requires the ALTER permission. Since the ALTER permission is a lot more powerful, I'm generally in favour of using the less powerful INDEX. We could however try to use INDEX and if it fails ALTER, but that seems a little much for a simple configuration error caused by third party software.
          Hide
          D@ve Dave Remmel added a comment -

          Thanks for the reply, I didn't know that Confixx is the reason. I'm just a phpBB service provider and I have no influence on my customers servers.
          In Germany Confixx is one of the most used server administration solutions. I see no reason to persist on the CREATE INDEX statement, since the mentioned suggestion imo has no disadvantage.

          regards, Dave

          Show
          D@ve Dave Remmel added a comment - Thanks for the reply, I didn't know that Confixx is the reason. I'm just a phpBB service provider and I have no influence on my customers servers. In Germany Confixx is one of the most used server administration solutions. I see no reason to persist on the CREATE INDEX statement, since the mentioned suggestion imo has no disadvantage. regards, Dave
          Hide
          D@ve Dave Remmel added a comment -

          > We could however try to use INDEX and if it fails ALTER
          Why? Normaly you need the ALTER statement anyway for most most updates...

          However... To "alter" the sql_create_index() function isn't THAT complicated

          Show
          D@ve Dave Remmel added a comment - > We could however try to use INDEX and if it fails ALTER Why? Normaly you need the ALTER statement anyway for most most updates... However... To "alter" the sql_create_index() function isn't THAT complicated
          Hide
          Oleg Oleg [X] (Inactive) added a comment -

          If we are already using alter table elsewhere we could convert create index to alter table to appease our users on these hosts, which would be a nice thing to do.

          Keep in mind that the hosts probably don't care one bit (especially if indeed create index is a proper subset of alter table in terms of what it can do), it is our users that are getting frustrated by this situation. Call this a usability bug.

          Show
          Oleg Oleg [X] (Inactive) added a comment - If we are already using alter table elsewhere we could convert create index to alter table to appease our users on these hosts, which would be a nice thing to do. Keep in mind that the hosts probably don't care one bit (especially if indeed create index is a proper subset of alter table in terms of what it can do), it is our users that are getting frustrated by this situation. Call this a usability bug.
          Hide
          bantu Andreas Fischer added a comment -

          Same for CREATE UNIQUE INDEX.

          Show
          bantu Andreas Fischer added a comment - Same for CREATE UNIQUE INDEX.

            People

            • Assignee:
              bantu Andreas Fischer
              Reporter:
              D@ve Dave Remmel
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development