Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-13653

Error creating index un_mtch on table search_wordmatch

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Cannot Reproduce
    • Icon: Major Major
    • 3.1.4-RC1
    • 3.1.3
    • Installation system
    • None
    • Debian Stable.
      MySQL version: 5.5.41-0+wheezy1-log (Debian)

      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.

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

              Created:
              Updated:
              Resolved: