[PHPBB3-9551] Mysql fulltext index creation fails due to partial collation change Created: 20/Apr/10  Updated: 22/Jan/17  Resolved: 19/Jun/12

Status: Closed
Project: phpBB3
Component/s: Search
Affects Version/s: 3.0.7-PL1
Fix Version/s: 3.0.12-RC1

Type: Bug Priority: Major
Reporter: Oleg [X] (Inactive) Assignee: Nils Adermann
Resolution: Fixed Votes: 0
Labels: None

Mysql with fulltext search

Issue Links:
was caused by solution of PHPBB3-4574 Database charset! Closed
GitHub Pull Request URL: https://github.com/phpbb/phpbb3/pull/849
Fix URL: https://github.com/phpbb/phpbb3/pull/849


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.

Comment by Nils Adermann [ 19/Jun/12 ]

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.

Generated at Sat Feb 16 07:29:26 UTC 2019 using Jira 7.12.3#712004-sha1:5ef91d760d7124da5ebec5c16a948a4a807698df.