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

Migrator fails to remove columns on MSSQL when they have/had an index

    Details

      Description

      The index must be removed before the column can be dropped.
      The best would be if the migrator/db_tools would check this, so you don't have to specify them in your migration.

        Issue Links

          Activity

          Hide
          DavidIQ David Colón added a comment -

          The code for sql_column_change used specifically for mssql in db/tools.php deals with looking for any constraints that reference a column and dropping them. Either this function needs to be used or the code being used needs to be re-used when dropping columns, at least for MSSQL. May need some massaging in the case of indexes.

          Show
          DavidIQ David Colón added a comment - The code for sql_column_change used specifically for mssql in db/tools.php deals with looking for any constraints that reference a column and dropping them. Either this function needs to be used or the code being used needs to be re-used when dropping columns, at least for MSSQL. May need some massaging in the case of indexes.
          Hide
          DavidIQ David Colón added a comment -

          Here is a sample query that will get you all of the indexes in the users table that use the username column:

          SELECT DISTINCT ix.name
          FROM sys.indexes ix
          INNER JOIN sys.index_columns ixc ON ixc.object_id = ix.object_id AND ixc.index_id = ix.index_id
          INNER JOIN sys.columns cols ON cols.column_id = ixc.column_id AND cols.object_id = ix.object_id
          WHERE ix.object_id = OBJECT_ID('users') AND cols.name = 'username'

          It would be a matter of taking each returned index and dropping it before dropping the column. It could be done database side, like it was done in tools.php, but would probably work better looping through the results PHP side.

          Show
          DavidIQ David Colón added a comment - Here is a sample query that will get you all of the indexes in the users table that use the username column: SELECT DISTINCT ix.name FROM sys.indexes ix INNER JOIN sys.index_columns ixc ON ixc.object_id = ix.object_id AND ixc.index_id = ix.index_id INNER JOIN sys.columns cols ON cols.column_id = ixc.column_id AND cols.object_id = ix.object_id WHERE ix.object_id = OBJECT_ID('users') AND cols.name = 'username' It would be a matter of taking each returned index and dropping it before dropping the column. It could be done database side, like it was done in tools.php, but would probably work better looping through the results PHP side.
          Hide
          bantu Andreas Fischer added a comment -

          The MSSQL behaviour kind of makes sense because indexes may be over multiple columns. A user may a) want to recreate the index without the dropped column or b) drop the index completely.

          Show
          bantu Andreas Fischer added a comment - The MSSQL behaviour kind of makes sense because indexes may be over multiple columns. A user may a) want to recreate the index without the dropped column or b) drop the index completely.
          Hide
          nickvergessen Joas Schilling added a comment -

          I made a patch which drops the index for us now.
          We either added a new index anyway or the column was the only one in the index, so in both cases retaining it without that one column does not make sense

          Show
          nickvergessen Joas Schilling added a comment - I made a patch which drops the index for us now. We either added a new index anyway or the column was the only one in the index, so in both cases retaining it without that one column does not make sense
          Hide
          nickvergessen Joas Schilling added a comment -

          Reopening in order to implement the systematic solution

          Show
          nickvergessen Joas Schilling added a comment - Reopening in order to implement the systematic solution

            People

            • Assignee:
              nickvergessen Joas Schilling
              Reporter:
              nickvergessen Joas Schilling
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development