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

Mysql fulltext index creation fails due to partial collation change

    Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.7-PL1
    • Fix Version/s: 3.0.12-RC1
    • Component/s: Search
    • Labels:
      None
    • Environment:
      Mysql with fulltext search

      Description

      Support topic: http://www.phpbb.com/community/viewtopic.php?f=46&t=2035165

      Code: http://tps.projects.bsdpower.com/browser/branches/phpBB-3_0_0/phpBB/includes/search/fulltext_mysql.php?annotate=blame&rev=10399#L744

      In mysql if a fulltext index is created over multiple columns, collations of all of those columns must be the same. In the linked code above, the procedure for creating a fulltext index changes collation on post text but not on post subject.

      If the affected columns already use the target collation, changing collation on one column is a no-op. If affected columns use different collation, after the code runs collation on columns differs and index creation fails.

      A fix would be changing collation on both columns.

      Note that the commented out line is a result of http://tps.projects.bsdpower.com/changeset/7675 and http://www.phpbb.com/bugs/phpbb3/11637.

      Also note that the commented out line unconditionally changes subject column length to 100 chars. In support topic linked above the user's subject column was 255 chars long. Depending on mysql version changing length to 100 chars (or any fixed number, for that matter) may result in silent data loss.

        Issue Links

          Activity

          Hide
          naderman Nils Adermann added a comment -

          I have no idea why/who commented out this line which broke this:

          //$alter[] = 'MODIFY post_subject varchar(100) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL';
          

          Someone should find out where this was done. The code should simply use the varchar length that phpBB uses. Supporting unknown random schema changes within phpBB is not something we have ever done elsewhere and not something I plan on supporting. If you want to go and modify your schema you will have to adapt the code as well.

          Show
          naderman Nils Adermann added a comment - I have no idea why/who commented out this line which broke this: //$alter[] = 'MODIFY post_subject varchar(100) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL'; Someone should find out where this was done. The code should simply use the varchar length that phpBB uses. Supporting unknown random schema changes within phpBB is not something we have ever done elsewhere and not something I plan on supporting. If you want to go and modify your schema you will have to adapt the code as well.

            People

            • Assignee:
              naderman Nils Adermann
              Reporter:
              Oleg Oleg [X] (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development