When a migration drops an index and adds a new one with the same name in one perform_schema_changes() call, the new index is never created on phpBB 4.0. The old index stays as it was, and no error is shown.

This is common in extension migrations that widen an index: drop_keys plus add_unique_index with the same key name in one update_schema(). It works on phpBB 3.3.

Steps to reproduce

1. Create a table with 'KEYS' => ['un_mtch' => ['UNIQUE', ['article_id', 'word_id', 'title_match']]].
2. Call perform_schema_changes() once with:

   [

   'drop_keys'        => ['phpbb_kb_src_wrdmtch' => ['un_mtch']],

   'add_unique_index' => ['phpbb_kb_src_wrdmtch' => ['un_mtch' => ['article_id', 'word_id', 'title_match', 'descr_match']]],

   ]

3. List the table's indexes.

Expected: the un_mtch index has the columns article_id, word_id, title_match, descr_match.

Actual: it still has article_id, word_id, title_match. Running the drop and the add as two separate perform_schema_changes() calls gives the right result. Tested on master d144a38 with SQLite. The comparator code involved is the same for every database.

Cause

Doctrine's Comparator::compareTables() reports a same-named index whose definition changed as a modified index (TableDiff::getModifiedIndexes()), not as dropped plus added. \phpbb\db\doctrine\comparator::compareTables() rebuilds the diff from getAddedIndexes() and getDroppedIndexes() only, so modified indexes are lost.

Proposed fix

• In \phpbb\db\doctrine\comparator::compareTables(), add each modified index to the dropped list (its old definition) and the added list (its new definition) before the diff is rebuilt.
• In \phpbb\db\tools\doctrine::schema_index_drop(), when a migration passes the unprefixed legacy key name and the table has both that name and its generated prefixed name (kbsrcwrdmt_un_mtch), drop the prefixed one, since that's the one phpBB 4.0 created.

A patch with a unit test is on https://github.com/bonelifer/phpbb/tree/fix/doctrine-modified-indexes. It isn't yet based on a ticket branch, and phpBB's database test suite hasn't been run on it.

Seen in: the phpbbmodders/knowledgebase extension, whose migration version_2_0_15 widens its search index this way. On phpBB 4.0 the index kept three columns and rejected valid rows. The extension now works around it with a second migration.

Investigated and written by Claude on behalf of William Jacoby (bonelifer).