I am having a problem converting a 3.0.12 board to 3.1.3 because of duplicate rows on table phpbb3_search_wordmatch. The converter errors when running the following migration file:

Database type :: mysql4

Previous version :: 3.1.0-RC2

Installing Schema: \phpbb\db\migration\data\v310\rename_too_long_indexes.

It produces the following error (I have omitted the backtrace):

SQL ERROR [ mysql4 ]

Duplicate entry '25-34-0' for key 'un_mtch' [1062]

SQL

ALTER TABLE phpbb3_search_wordmatch ADD UNIQUE INDEX un_mtch(word_id, post_id, title_match)

A few years back I had a couple of crashes on this db and had to repair the tables. But after those crashes I deleted and re-created the fulltext native search index from ACP (if that plays any role in this situation). I had no crash after the rebuild.

The queries I used to determine (1) the number of duplicate rows and the (2) the total number of rows, are the following:

mysql> SELECT COUNT(*) FROM (SELECT DISTINCT * FROM phpbb3_search_wordmatch) psw;

+----------+

| COUNT(*) |

+----------+

| 30532173 |

+----------+

1 row in set (7 min 43.27 sec)

mysql> SELECT COUNT(word_id) FROM phpbb3_search_wordmatch;

+----------------+

| COUNT(word_id) |

+----------------+

|       30625943 |

+----------------+

1 row in set (0.03 sec)

A total of 93770 duplicates exist.