0

I have been testing our site using MySQL 8.0.36, and all seems great other than the search index seems to be not working as expected as any entry search with a string over 10 characters in length returns 0 results.

I resaved entries with --update-search-index and the problem still persist.

I was wondering if it could be something to do with the collation, so I tried converting all tables to utf8mb4_0900_ai_ci, but this fails with the following error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs The SQL being executed was: ALTER TABLE craft_content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci.

Any pointers to what my problem may be are gladly received. I'm sure it's something I'm not thinking about somewhere. Thanks!

3
  • what happens if you use utf8mb4_unicode_ci?
    – funkysoul
    Apr 13 at 14:14
  • I had to change some fields to text to convert the content database to utf8mb4_unicode_ci, but I'm having the same issues with the search.
    – Watsonian
    Apr 15 at 16:27
  • I'm at a loss as to what I need to change. The following returns results SELECT elementId FROM craft_searchindex WHERE (((siteId = '1')) AND MATCH(keywords) AGAINST('experience*' IN BOOLEAN MODE)) If I change it to experienced i get 0 results: SELECT elementId FROM craft_searchindex WHERE (((siteId = '1')) AND MATCH(keywords) AGAINST('experienced*' IN BOOLEAN MODE))
    – Watsonian
    Apr 16 at 14:42

1 Answer 1

0

The problem was that innodb_ft_max_token_size was set to 10 in the my.ini.

Updating this and re-saving the index solved the issue. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.