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

Error creating index un_mtch on table search_wordmatch

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 3.1.3
    • Fix Version/s: 3.1.4-RC1
    • Component/s: Installation system
    • Labels:
      None
    • Environment:
      Debian Stable.
      MySQL version: 5.5.41-0+wheezy1-log (Debian)

      Description

      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.

        Attachments

          Activity

            People

            • Assignee:
              nickvergessen Joas Schilling [X] (Inactive)
              Reporter:
              aleha aleha [X] (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: